Connecting option buttons to table fields

[Roselene]

Hi, everybody!

How can I connect Option buttons to a table field?
I have 2 option buttons and a table field. I’d like to store different data on this field for each button, eg. When the user clicks button 1 then the table field stores 1 and when the user clicks button 2 the table field stores 2, or something like that.
Do I need to use a Module? How should this module be?

I'm sure it must an easy thing to do, but, I have been so far uncessful.

Thanks in advance!
Roselene

i may be wrong, but i think you can only connect a option button to a field if the field is a Boolean one (True/false).

what i suggest is to have a label or something, connect the label to the field that you want, and when the user clicks the option button, the label changes between "1" and "2". (does that make sense?)

------------------

Wossname,
Email me: wossnamex@talk21.com :)

[netSurfer]

Can you be more clear on exactlyu what you are trying to do? I'm not sure what you mean by table field? I can try to help if you give me more info

[Roselene]

Hi, people!

The user will choose between two options:
Destination 1 (Option button 1) and
Destination 2 (Option button 2)

I have a table which contains a field called Destination, and I'd like to store 1 in this field when the user click the 1rst. Option button and to store 2 when the user click the 2. I'm sorry if I didn't make myself clear enough last time.

I've tried to connect the option to this field, but I couldn't find its data properties. So I'm wondering how this button works.

Should I use a VB module to send a value to a text box, based on which option button the user clicks, having, for example, the label changes between "1" and "2"? And after that connecting this label to my Destination table field?

I really appreciate your attention and any further help,

Thanks,
Roselene

[netSurfer]

ok, I'm assuming that by table you mean a grid? Say the destination field was in column 3 and we're working on row 2. The grid controls count the first rows and columns as 0.

I would make the option buttons an array called optDestination. In the _Click(Index as integer) sub, with the Index being the index of hte button clicked,

write this:

grdTable.col = 2 'remember, first col is 0
grdtable.row = row you are using
grdtable.text = index + 1

then, if click first option, enters 1 in the field, second option it would insert a 2. This is, of course, if you are using a grid for hte table. If not, what type of control are you using for the table?

[Roselene]

Net Surfer,

I'm connecting my table to the form using a Data Control. There's no grid.

But, as I couldn't find the Option buttons data properties, I don't know how to send the Destination Value (eg.1 - 1rst. option button and 2 - 2nd option button) to the Destination field, which is in my table.

I may have to use another control, like a list box with 2 options instead of option buttons.

What do you think?

Thanks for helping,
Roselene

[netSurfer]

Ok, now it's a little more clear. What are you using to display the data? I assume you use the Data Control (the one with the arrow buttons?) to allow the user to go through data or to simply connect but what do you use to display the fields etc?

[Roselene]

NetSurfer,

Yes, I'm using a data Control (the one with the arrow buttons) to connect my Table (Microsft Access) to my Form (Visual Basic) To display the fields I'm using objects such as data list, data combo and text box.
No problem with them, cause they have data properties and I can connect them to my Access Table fields. Problems only with the option buttons.

Thanks for your attention,
Roselene

[netSurfer]

ok, I haven't used Data controls really, at least not bound to a data base. I prefer to handle the database connections myself. Have you tried simply setting the value manually or can you do that while the control is bound to the database. The way I understand it, because a control is bound to the database, changing it would change the database. You could always unbind the control you are using to display that field and then just change the value. What control are you using that you need the option buttons to change?

Hi, Roselene maybe this is what your looking for...

Private Sub Data1_Reposition()
with data1.recordset
' here you have to make sure that the fields
' hez something like true = yes false = no or
' you can try 1 = yes -1 = no or something like that
if .fields(1).value = True then
' set you value to true
option1.value = True
else
option1.value = False
End if
End with
End Sub

[Roselene]

NetServer,

What do you mean when you say "I prefer to handle the database connections myself"? VB modules?
I'd like the user to choose between 2 destinations:

Primary Crusher
Primary Crusher Square

so I've made each destination a option button. I have a field in a table called Trips where I'd like to store the result of the user's choice.

I'm thinking about using a list box instead of option buttons, so, I could connect the list box to the destination field on the Trips Table, using the list box data source and data field properties.

Sorry if I didn't make myself clear enough.
Thanks very much for helping,

Roselene

[Roselene]

NetSurfer,

I'm sorry. I've written your nickname on the wrong way.
Please forgive me,
Roselene

[Roselene]

NetSurfer,

I'll try to use the info you sent me and will let you know how it works.
Thanks a lot.

Roselene

[netSurfer]

glad to help. I'm going to be at work for another 4 hours or so. My home PC is fried so if you have a problem after that, email me and I'll get it tommorow. Just in case I can't find this thread again. Good luck. :-)

email: matthewbryan@hotmail.com

[netSurfer]

I'm going home now. I hope that the code helped :-) If not, I'll be back tommorow sometime. Good luck.

matthew

[netSurfer]

Roselene, of course your forgiven, no harm done ;-)

I usually write all the code to handle database connections. I don't like using the Databound controls, not enough control over the data. For example:

Dim dbsData as database, rcdTable as recordset, strSearch as string

strSearch = "Search String"

set dbsData = "database.mdb"
set rcdTable = dbsData.OpenRecordset("Select field1, field2 from tableName where field1 = '" & strSearch & "'"
if not rcdTable.eof then
'Have found the Search String
rcdTable.edit
else
'haven't found search string
rcdTable.addnew
rcdtable(1) = "New Search"
end if
rcdtable(2) = "Second field"
rcdtable.update
rcdtable.close
dbsData.close


This would open a database, grab fields 1 and 2 from tablename if field 1 = "Search String", if it doesn't find "Search String" it will add a record and put "New Search" in the field 1. In both cases, it will put "Second Field" in field 2. Then it will close both the database and the recordset (table)

Onto your question, so what you're really doing is if the pick option 1, it would set one value in the database, if the other option button it would set the other value. Doing it manually like above, it would be easy to write and to use the Option buttons. But with only DataBound controls, I don't think you can. I think that is a control set that has databound Option Buttons but I'm not sure. Sorry I can't be more help. If you decide that you really need to use option buttons, I can give you the code that would write and retrieve the values they choose.

[Roselene]

NetSurfer,

It's very, very kind of you helping me!
Please, give me the code, if it won't disturb you.

I'll try to learn from the code you've sent me. I'm only a beginner, there's so much I need to learn...

Thanks again,
Roselene

[netSurfer]

Roselene, that's what this place is for, helping each other. I'm glad to be able to help. Ok, I will write this to use 2 option buttons that are arrayed together - same name, different index number. They will tie to a field called Destination in a table called Table1. If the user has selected option 1, it will write 1 to the database, it will also retrieve the 1. The retrieve and save code I will place in seperate functions that you can paste into your form and then call them when you load or save the record. Because the record I assume changes as they click the data control, you will have to call the function there, sending in the Index or key field value. I need to know what value to search for so that we get the correct values from the Destination field. If you don't understand or want clarification on any code below just ask :-)

function GetData(strSearch as string)
Dim dbsData as database, rcdTable as recordset, strSearch as string, intTemp as integer

strSearch = "Search String"
'you have to replace the "Search String" with the value from the Index/Key in your datacontrol.

set dbsData = "database.mdb"
set rcdTable = dbsData.OpenRecordset("Select destination from table1 where INDEXFIELDNAME = '" & strSearch & "'"
'need to replace the INDEXFIELDNAME with the name of the index field, if it has a space in it --> [Field Name]

if not rcdTable.eof then
'Have found the Search String
else
'haven't found search string
msgbox "The value: " & strSearch & " wasn't found.", vbokonly, "Search Failed"
end sub
end if

intTemp = rcdTable(0) 'this gets the value of the destination field

optDestination(intTemp -1 ).value = 1
'optDestination(intTemp).value = 0
'only use the 2nd optDestination if you don't have them chained together. May have to play with this a bit to make it work, depends on how you set it up
rcdtable.close
dbsData.close

end function

that code should work to get the value of the destination they choose

now for hte code to write the option buttons value back into the database. You have to call this where ever you save changes. Again, include the key value and you need to call this function AFTER you have saved the other data.

function SaveDate(strSearch as string)
Dim dbsData as database, rcdTable as recordset, strSearch as string, intTemp as integer

strSearch = "Search String"
'you have to replace the "Search String" with the value from the Index/Key in your datacontrol.

set dbsData = "database.mdb"
set rcdTable = dbsData.OpenRecordset("Select destination from table1 where INDEXFIELDNAME = '" & strSearch & "'"
'need to replace the INDEXFIELDNAME with the name of the index field, if it has a space in it --> [Field Name]

if not rcdTable.eof then
'Have found the Search String
rcdTable.edit
else
'haven't found search string
msgbox "The value: " & strSearch & " wasn't found.", vbokonly, "Search Failed"
end sub
end if

if optDestination(0).value = 1 then
'they selected option1
rcdTable(0) = 1
else
rcdTable(0) = 2
end if
rcdtable.update
rcdtable.close
dbsData.close

end function

That should work. I can't test it but if you have problems, let me know. You can email me if you need to. Hope this helps.


SORRY! Replace the End Sub's with End Function or Exit Function, I can't remember which one.

[This message has been edited by netSurfer (edited 01-18-2000).]

[Roselene]

Matthew,

Your code is great! I'm really learning from it! Let me ask you a question:
we have a recordset called rcdtable, so, when you say:
intTemp = rcdTable(0) is it ecqual to
intTemp = rcdTable("Destination")?
cause Destination is the only field on the this recordset.

so, when I use a recordset and store a value in it, the Table which the recordset has come from is updated as well, isn't it?
I guess I am getting it.

Do you usually have a button on your forms, where the user has to click in order to save the record? Another button to cancel, to make changes and so on?
It sounds safety.

I have no other doubts so far, but if I have any in the future, should I send a message to the Forum (this topic) or to your e-mail?

Thanks again, Matthew, it's very kind of you helping me that way.

Roselene

[Maartin]

Yes Roselene.

That is the same. It is just a lot easier to read it when it is done as
rsMain("description") instead of
rsMain(0).



------------------
Have Fun.
Maartin.
dinamite@onwe.co.za
-----------------------

[Roselene]

Maartin,

Thanks for helping, this Forum is terrific.
Have fun you too!

Roselene

[Roselene]

Maartin,

Thanks for helping, this Forum is terrific.
Have fun you too!

Roselene

[Roselene]

Maartin,

Thanks for helping, this Forum is terrific.
Have fun you too!

Roselene

[Crazy D]

You can call the field also like
recordet!fieldname

[netSurfer]

Roselene, yes Martin is right, it is much easier to put the Field Name in instead of the index number:

rcdTable("Field Name")

vs:

rcdTable(0)

but I usually get too lasy to do it :-) By all means, if you have problems Post it to this forum. Someone is usually able to help and I check it several times a day. But if you post and don't get an answer, feel free to email me the question. Good luck on learning VB. Personally, since starting it 4 years ago, I've never stopped learning new things about it. It's a lot of fun.

Matthew