SQL query with combo box

[spinkung]

Hi all,

ok.....

i have a list box on a form which when an item is double clicked executes a SQL query. Its the first time i've ever used it so i'm unsure of whats possible and whats not.

Below the list box is a combo box that i want the user to make a selection in and which is tied to the value in the list box. (hope that made sense).

So, user select a listbox item. Before they can save they have to select a combobox item. When both are done i want to add the date() to the listbox item (in the underlying table) AND the value from the combobox.

I have the date part working but cant seem to link the combobox.


here's what i've got so far.......(the red is where it doesn't work)

Select Case MsgBox("Save Completed?", vbYesNo, "Save")
Case Is = vbYes
Dim StrId As String
Dim StrSql As String
Dim qryD As QueryDef

StrId = List0.Value

StrSql = "UPDATE TBL_StockEntry SET TBL_StockEntry.[Date Booked In] = Date(), [COLOR="Red"]TBL_StockEntry.[State] =  me.combobox1.value [/COLOR]" & _
"WHERE (((TBL_StockEntry.StockID)=" & StrId & "));"

Set qryD = CurrentDb.CreateQueryDef("", StrSql)
qryD.Execute

List0.RowSource = List0.RowSource

ps. the error i'm geting is: runtime error '3061':too few parameters, expected 1.


can anyone help?

cheers
spinkung.

 

[robblack]

String conatenation will at least be part of your problem:

StrSql = "UPDATE TBL_StockEntry SET TBL_StockEntry.[Date Booked In] = Date(), TBL_StockEntry.[State] = me.combobox1.value " & _
"WHERE (((TBL_StockEntry.StockID)=" & StrId & "));"

Should read:

StrSql = "UPDATE TBL_StockEntry SET TBL_StockEntry.[Date Booked In] = Date(), TBL_StockEntry.[State] = " & me.combobox1.value & " " & _
" WHERE (((TBL_StockEntry.StockID)=" & StrId & "));"

Hope this is of some help.

 

[spinkung]

absolutley spot on, thanks.


Spinkung