combobox update sql query!

davieboy_xr
04-19-2004, 09:18 AM
need a bit of help with an update query.

i have a combobox that has a yes and no value.
In my database table i have a field that i want to update and is of yes/no value.And saves as either -1 or 0.

I want to be able to update this when a user clicks the combo box so far i have this:

Private Sub Form_Load()
Combo1.Visible = False
Combo1.AddItem "Yes"
Combo1.AddItem "No"
Combo1.ListIndex = 1
Combo1.ToolTipText = "Select Either Yes or No"
end sub

Private Sub MSFlexGrid1_Click()
Set con = New ADODB.Connection
Set rs = New Recordset
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\db1.mdb;" & _
"User Id=admin;" & _
"Password="
Dim column3SQL As String
With MSFlexGrid1
If .Col = 3 Then 'column selected
Combo1.Top = .Top + .CellTop
Combo1.Left = .Left + .cellLeft
Combo1.Width = .CellWidth
Combo1.Visible = True
Combo1.SetFocus
column3SQL = "UPDATE EXTRA_NOTES SET SCR1_CARERVERBAL_CONSENT = '" & Combo1.ListCount & "'" _
& " WHERE PATIENT_ID='" & MSFlexGrid1.Rowsel & "';"
rs.Open column3SQL, con, adOpenKeyset, adLockOptimistic
' rs.Update
MSFlexGrid1.Refresh

Else
Combo1.Visible = False
End If
End With
End Sub

i can get it to appear in my flexgrid and i can get the dropdown box to display the values but it wont update and save. Help much appriciated.

HardCode
04-19-2004, 09:58 AM
You don't use the RecordSet object to do UPDATE, INSERT, or DELETE SQL statements. RecordSets "only" return data. You can use the Connection object to do this.


con.Execute column3SQL

davieboy_xr
04-19-2004, 10:58 AM
ive tried this and i cant still cant get it to update;

With MSFlexGrid1
If .Col = 3 Then 'column selected
List1.Top = .Top + .CellTop
List1.Left = .Left + .cellLeft
List1.Width = .CellWidth
List1.Visible = True
List1.SetFocus#
column3SQL = "UPDATE EXTRA_NOTES SET SCR1_CARERVERBAL_CONSENT = '" & List1.ListIndex & "'" _
& " WHERE PATIENT_ID='" & .TextMatrix(.Rowsel, 0) & "';"
con.Execute column3SQL
MSFlexGrid1.Refresh

Else
List1.Visible = False
End If
End With

End Sub

any help appriciated

HardCode
04-19-2004, 11:01 AM
Does it error out or just does nothing? Can you put a breakpoint at the line "con.Execute column3SQL" and type ?column3SQL in the Immediate window. Then copy and paste the result here. This way we can see the SQL as the database will see it.

davieboy_xr
04-19-2004, 12:49 PM
Thanks for the reply..
update Extra_notes Set Scr1_carerverbal_consent = '1' Where Patient_id='161';
i have changes the dropdown box o a list
it doesnt throw up an error it just highlights the list and does nothing.

HardCode
04-19-2004, 12:52 PM
Are the fields Scr1_carerverbal_consent and Patient_id of numeric datatype? If not, remove the single-quotes around the values:


'
'
update Extra_notes Set Scr1_carerverbal_consent = 1 Where Patient_id=161;
'
'

davieboy_xr
04-19-2004, 01:11 PM
the fields are yes/no value, i have removed the qoutes around around the
& Combo1.ListIndex &

and it seems to be doing something. the field inside the database has a yes/no true/false .

its seens to be holding a 1 value in the value of the combo box, and does want to change upon clicking the value. should i set the value as:

combo1.additem true
combo1.additem false

thanks dave

HardCode
04-19-2004, 01:29 PM
For yes/no fields, you can pass to Access " .... WHERE field1 = YES ..." without single quotes around the YES.

What are the values available in List1? Just 1 and 0 or something else?

MKoslof
04-19-2004, 01:56 PM
I would try 0 and 1 (or it might be -1) for your yes no fields...in a query, it is good to use the bit-wise value.

davieboy_xr
04-19-2004, 02:13 PM
heres my code, im getting some strange results..I click the box to the value iwant say "Yes" and it stays at "No" then i reopen the form and i get the "yes"
value in the grid.

private sub form_load()
combo
combo1.AddItem "No",0
combo1.AddItem "Yes" ,1
end sub

Private Sub MSFlexGrid1_Click()
Set con = New ADODB.Connection
Set rs = New Recordset
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:db1.mdb;" & _
"User Id=admin;" & _
"Password="

Dim column3SQL As String
Combo1.ToolTipText = "Select Either Yes or No"
With MSFlexGrid1
If .Col = 3 Then 'column selected
Combo1.Top = .Top + .CellTop
Combo1.Left = .Left + .cellLeft
Combo1.Width = .CellWidth
Combo1.Visible = True
Combo1.SetFocus#
column3SQL = "UPDATE EXTRA_NOTES SET SCR1_CARERVERBAL_CONSENT = " & Combo1.ListIndex & "" _
& " WHERE PATIENT_ID='" & .TextMatrix(.Rowsel, 0) & "';"
con.Execute column3SQL
MSFlexGrid1.Refresh
Else
Combo1.Visible = False
End If

End With
con.Close
MSFlexGrid1.Redraw = True
End Sub

MKoslof
04-19-2004, 02:20 PM
So is this a bound control? I see where you send the update statement to your table. But, then you simply do a MSFlexGrid.refresh. If the MSFlexGrid is a bound control, you need to set it = nothing and then reassign the recordset. If it is not a bound control, you need to repopulate it however you are currently building the grid.

davieboy_xr
04-19-2004, 03:34 PM
it is not a bound control..so how would i go about refreshing the data?

MKoslof
04-19-2004, 03:35 PM
How do you currently populate the grid? I tend to write a function, then I call this function whenever I need to repopulate the grid (such as updating the data source).

davieboy_xr
04-19-2004, 06:04 PM
thanks got it sorted .. i used what you suggested - using a function to refresh;)

davieboy_xr
04-19-2004, 07:35 PM
I still have a slight problem when i click the cell sometimes it does not automatically update the cell.It will if i click away from it though..

MKoslof
04-19-2004, 07:52 PM
Well, it depends what event you are using to fire the code. Try a double click event or a lost focus/change event.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum