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.
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
______________________________
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")
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 OLEDBatabase 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 OLEDBatabase 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
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!
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.
Option button help
Reply With Quote
atabase Password=whatever"