Option button help

Hi

I trying to write the value of a option to a database and needed some help. I have two option button inside of a frame control. The user can only choose one of them. But what I’m struggling with is if the first one is checked I need to write that to my database. And when my application starts I get that value out of my database and have the appropriate option button checked. I’ve seen the option buttons linked to a data control somehow.

Thanks for any help

Reston

i usually don't link or bind controls to RS, so what I do is like this:

in the database, field is a yes/no

in VB

getting from DB:
If RS("Field") = true then
opt1.value = true
else
opt2.value = true
end if

sending to db:
IF opt1.value = true then
RS("Field") = true
else
RS("Field") = false
end if

I guess you could shorten it too:


RS("Field") = opt1.value

This what I have in my form
And this doesn’t work. I’m not sure if this the best place for this code?


Private Sub cmdUpdate_Click()

datEdgeCoarse.Recordset.Update
datEdgeCoarse.UpdateRecord
sending to db:
If Option1.Value = True Then
datEdgeCoarse.Recordset.Fields("R1up").Value = True
Else
datEdgeCoarse.Recordset.Fields("R2up").Value = True
End If
datEdgeCoarse.Recordset.Fields("R1up").Value = Option1.Value

End Sub

The problem with this is that the option button is off compared to the database.

Private Sub datEdgeCoarse_Validate(Action As Integer, Save As Integer)

'getting from DB:
If datEdgeCoarse.Recordset.Fields("R1up").Value = False Then
Option1.Value = True
Else
Option2.Value = True
End If

End Sub

Thanks for the help

reston

______________________________
Private Sub cmdUpdate_Click()

datEdgeCoarse.Recordset.Update
datEdgeCoarse.UpdateRecord
sending to db:
If Option1.Value = True Then
datEdgeCoarse.Recordset.Fields("R1up").Value = True
Else
datEdgeCoarse.Recordset.Fields("R2up").Value = True
End If
datEdgeCoarse.Recordset.Fields("R1up").Value = Option1.Value

End Sub
________________________________

Several things look wrong with that code

First the Updates should be coming after data changes, not before right?

Second, Why do you have field R1up and R2Up?
If one has to be true and the other false, aren't they really the same thing?
If you only had one of them, the true or false value for it will determine both option buttons (since only one can be True at a time).

Third, you are setting the value of the field R1Up twice in a row.

...
You should delete field R2Up
Then the code would be:

Private Sub cmdUpdate_Click()
'sending to db:
datEdgeCoarse.Recordset.Fields("R1up").Value = Option1.Value
datEdgeCoarse.Recordset.Update
End Sub

And that's the whole sub

Bringing it back from the db would be:

If datEdgeCoarse.Recordset.Fields("R1up").Value = True then
option1.value = true
else
option2.value = true
end if

The reason I don't shorten that to
option1.value = datEdgeCoarse.Recordset.Fields("R1up").Value
is that sometimes I forget to set one or the other to True at runtime and the above line would only work for true, whereas if it were false, neither button would be true. Of course if you remember to set one of them to true at design time, then just use the one line above to set the buttons

Lastly, you don't really need the .value property for the object or the recordset, they are both default
the most concise code looks this way:

to the db:
Private Sub cmdUpdate_Click()
datEdgeCoarse.Recordset.Fields("R1up") = Option1
datEdgeCoarse.Recordset.Update
End Sub

and from the db:
option1 = datEdgeCoarse.Recordset.Fields("R1up")
'and for safety sake,
option2 = Not datEdgeCoarse.Recordset.Fields("R1up")

Hope that helps

Hi

This is the code that works in the Update sub. The only problem is the it only update the first recordset.
If I move to the second recordset it updates the first one.

Private Sub cmdUpdate_Click()
datEdgeCoarse.Recordset.Edit

If Option1 = True Then datEdgeCoarse.Recordset.Fields("R1up") = Option1
If Option1 = True Then datEdgeCoarse.Recordset.Fields("R2up") = False

If Option2 = True Then datEdgeCoarse.Recordset.Fields("R2up") = Option2
If Option2 = True Then datEdgeCoarse.Recordset.Fields("R1up") = False

datEdgeCoarse.Recordset.Update
ace = 69

End Sub





This what I’ve tried for scrolling through the data control.

Private Sub datEdgeCoarse_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
If datEdgeCoarse.Recordset.Fields("R1up").Value = True Then
Option1.Value = True
Else
Option2.Value = True
End If

End Sub

The only problem is that the first option botton is correct but as I scroll the data control the option button is off by one. For example if my recordset is R1up the R2up and this goes back and forth in my table. Record one is R1up the Record two is R2up. Then when I’m scrolling the data control and get to recordset two option1 one is clicked or I guess true.

Still, first I have to reiterate that not only is this code redundant:
If Option1 = True Then datEdgeCoarse.Recordset.Fields("R1up") = Option1
If Option1 = True Then datEdgeCoarse.Recordset.Fields("R2up") = False

If Option2 = True Then datEdgeCoarse.Recordset.Fields("R2up") = Option2
If Option2 = True Then datEdgeCoarse.Recordset.Fields("R1up") = False

but the fields R2up and R1up are redundant. You can delete field R2up and have the same amount of data.

Consider this:

I would have a column like this:
Male
and the values are true of false
If it is True, then it is male, if it is false, then it is female
When I get that value back in code, I can deal with either case by the one value.
I don't need
Male = True
Female = False
to know that it is a male.
That is duplicate information, unnecessarily taking up database space and database access time.


Are you using an ADO control?

A while back I wrote these two subs and I've never looked back:

In a module, paste the following code:
'--------------------------------------------------------------------
Public RS As Recordset
Public cmd As Command
Public prm As Parameter
Public Conn As Connection
Public adoCommand As Command
Public strSQL As String

Public RS2 As Recordset
Public cmd2 As Command
Public prm2 As Parameter
Public Conn2 As Connection
Public adoCommand2 As Command
Public strSQL2 As String

Public connstring As String
Public DBPath

Public Sub BuildRS(SQLSTRING)
'modify these to suit your db info
DBPath = App.Path & "\whatever.mdb"
connstring = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath & ";Jet OLEDB:Database Password=whatever"

Set RS = Nothing
Set Conn = Nothing
Set adoCommand = Nothing
Set Conn = New Connection
Conn.Open connstring
Conn.CursorLocation = adUseClient
'break here
strSQL = SQLSTRING
'resume db
Set adoCommand = New Command
adoCommand.CommandType = adCmdText
adoCommand.CommandText = strSQL
adoCommand.Name = "adoCommand"
Set adoCommand.ActiveConnection = Conn
Set RS = New ADODB.Recordset
RS.Open adoCommand, , adOpenStatic, adLockOptimistic

End Sub
Public Sub BuildRS2(SQLSTRING)
'modify these to suit your db info
DBPath = App.Path & "\whatever.mdb"
connstring = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath & ";Jet OLEDB:Database Password=whatever"
Set RS2 = Nothing
Set Conn2 = Nothing
Set adoCommand2 = Nothing
Set Conn2 = New Connection
Conn2.Open connstring
Conn2.CursorLocation = adUseClient
'break here
strSQL2 = SQLSTRING
'resume db
Set adoCommand2 = New Command
adoCommand2.CommandType = adCmdText
adoCommand2.CommandText = strSQL2
adoCommand2.Name = "adoCommand2"
Set adoCommand2.ActiveConnection = Conn2
Set RS2 = New ADODB.Recordset
RS2.Open adoCommand2, , adOpenStatic, adLockOptimistic
End Sub
--------------------------------------------------------------------------------

Okay, once you make a module with these codes, you will never have data connection headaches again.
Just build all recordsets in this way:

BuildRS "(sql here)"
and you have an open, editable Recordset

if RS is already open and you don't want to close it, then use:
BuildRS2 "(sql here)"

These recordsets will stay open for updating until you call one of the two lines above again, closing the RS and opening a new one

Now that you have that out of the way, the question of updating RS or RS2 can be handled by a boolean
cmdUpdate_click()
If blnFirstSet = true then
'blah.....
RS.Update
else
'blah.....
RS2.Update
end if
End sub

That will settle the problem of which RS gets updated

Then....
the last part, your code looks fine to me
as for the problem, I have read this:
The only problem is that the first option botton is correct but as I scroll the data control the option button is off by one. For example if my recordset is R1up the R2up and this goes back and forth in my table. Record one is R1up the Record two is R2up. Then when I’m scrolling the data control and get to recordset two option1 one is clicked or I guess true.

several times and I can't quite get what you are saying

Let me know if you need any more help on this

Hi

And thanks for all the help by the way!

I am using the DAO control so you know.

And the whole thing about R1up or R2up is that I fabricate optics. R1 is surface one and R2 is surface 2.I am pulling those variables from my access database and bringing them into vb6. So my application looks at whether surface 1 is up or surface 2 is up. So if condition 1 (R1up) is true the do whatever. The main thing here is that the option button on my application matches the database value. So if I scroll the data control the option buttons update from the database. As I was saying before if my application is at the first record I’ve set the database R1up checked is access. If you have access I can e-mail you something to look at if you would like? I think I could throw something together as an example!

I hope this helps

sure ,

my email is [email protected]

Of course, the DAO is a bit outdated. I'd suggest following the method I outlined above.

Also, the Surface up is still and either/or situation, so it STILL only requires ONE field in the database. True means 1 is up, false means two is up.

Remove any other DB conn codes you have put on the project and just add a reference to the Microsoft ActiveX Data Objects Library 2.0

That is all you need for fast, easy, convenient connection.

So when you said scroll , you meant the DAO object?
You could manually set up a scroll bar and arrow buttons and when the value changes, go to that RS.AbsolutePosition

In other words, scrollbars maxvalue = RS.RecordCount and minvalue = 1
and in the scrollbar's value change sub, set the RS.Absolute position to the scrollbar's value. Just be sure to turn the scrollbars tab stop to false so it doesn't blink.

If you just want next and previous

Command1_click()
If Not RS.EOF then RS.MoveNext
'bind controls, option buttons
End sub
Command2_click()
If Not RS.BOF then RS.MovePrevious
'bind controls, option buttons
End sub

the bind controls is the code I already wrote out

Then you will always have the right value. Just don't reference the R2UP field at all. It'll work, I promise.

Better yet, give me Your email and I'll send you a model of my way.

actually since it is Chinese New Year here and I have nothing better to do, i've already built a model of what I was saying. It demonstrates basically the same functions as an ADO control over a recordset.

This one lets you scroll through, edit/update the record, and even add new ones. All without the messy codes and no object.

Hope that helps