Using Code to Update a field in a table

[jlitondo]

I have a form with one combo box and one list box. User makes one selection in the combo box and one or more selections from the list box.Then clicking on a command button on the form updates a field on one or more records on the table where the combo selection and list box selection(s) holds true.I tried using rs.Update with an update sequel statement but I need help on this.I can paste or email my code if anyone would like to take a look at it and see what I'm doing wrong.

 

[cmmrfrds]

Code to loop through a listbox, assuming that you always have at least one item selected in the listbox.

Dim RSMT As DAO.Recordset
Dim combovariable as variant, listvariable as variant

'-- Open the your table
Set RSMT = CurrentDb.OpenRecordset("yourtable", dbOpenDynaset)

'-- save the combo box variable
combovariable = Comboboxname

'-- For each of the items in the ItemsSelected collection
For Each AnItemSelected In Me!yourlist.ItemsSelected()

listvariable = Me!Yourlist.ItemData(AnItemSelected)

' you do you sql update here, I assume you have an unique key to find the ' record you want to update.

Next AnItemSelected

RSMT.Close

 

[jlitondo]

I get an Access error msg that reads " Too few parameters. Expected 2". I set up msgboxes and determined that the combo and list box variables are assigned as selected.Not sure what the error means, but my table has 2 keys set as its primary key and these are picked from the list and combo boxes.

 

[cmmrfrds]

The error is caused by the SQL string not being set up properly, so you need to look at it. It is a syntax error.
Here is an example of an SQL update statement.

Dim SqlString as string
SqlString = "UPDATE Equipment " _
& "SET equipmentName = " & """Samuel Adams""" _
& " WHERE equipmentKey = 1 ;"
RSMT.Execute SqlString

' debug to see what is in the string
debug.print "the string = "; SqlString

'Do a "Control G" after this is run to see what your string looks like. This brings up the debug window so you can view your code.

 

[jlitondo]

You are right cmmrfrds. I had assigned my control values into variables and then referenced the variables in my sql string. But Access won't allow for this so I modified the slq ...WHERE clause from (= listvar) to (='" & listvar & "') and got it to work.Thanks.

 

[cmmrfrds]

In Access (each database vendor may have different syntax ie. Oracle, Sybase, ....) you need to surround the text datatype field with quotes. If the field is of datatype number or numeric then quotes are not used. This should be covered someplace in the Access documentation, although I can't tell you where without hunting for it.

 

[DMG2000]

db.excute("Select * From Table Where Field ='" & var &"'&quot
or
query = Select * From Table Where Field = " & var & "

 

[jlitondo]

I am learning alot from this site.Thanks alot guys for your tips.I greatly appreciate them.