I'm sure it's gonna end up being the silliest of things, but I'm out of ideas. I have this one form with three comboboxes with the same procedure. Basically, they all have a beforeupdate and an afterupdate event. When the combobox selection changes, the before update triggers and asks the user if they want to retire the previously selected entry (basically it just unchecks a checkbox and adds a retire date to that record).
In two of the comboboxes the method works alright with the following code. Both of them locate the corresponding record through the autonumerated PK:
Code:
CurrentDb.Execute "Update tablename set ret_date = Now(), active=0 where id = " & Me.combobox.OldValue, dbFailOnError
In the other combobox, that updates a table without an autonumerated PK, the "Too few parameters" error gets thrown. Since the where clause field is an string the code is the following, which is basically the same but with single quotes for the string
Code:
CurrentDb.Execute "Update tablename set ret_date=Now(), active=0 where serial_number = '" & Me.combobox.OldValue & "'", dbFailOnError
The combobox.oldvalue gets displayed properly when printed to a confirmation msgbox before updating the table. Since its a variant, I tried converting it to string (with Cstr(combobox.OldValue) ) to no avail.
How can I make it work for a String (short text) value? Where am I going wrong?