Too few parameters. 1 expected.

[Lhoj]

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:

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

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?

[Minty]

Stick the whole statement into a variable and then try running that.

Dim strSQL as String

strSQL = "Update tablename set ret_date=Now(), active=0 where serial_number = '" & Me.combobox.OldValue & "'"

debug.print strSQL
Currentdb.Execute strsql, dbfailonerror

There might be some conflict trying to use refer to old value and updating the record at the same time?

[Lhoj]

Ok, nevermind, I'm an idiot. I'd mispelled one of the fields (names are on spanish, but something like ret_data instead of ret_date) and for some reason, instead of telling me ret_data was not a field of the table it threw the error on the thread's title? I must have read the resulting query a hundred times and missed the little typo every single time, and I was starting to lose my mind. Access' misleading error didn't help.

Anyway, thanks for your answer, Minty.

[Minty]

Glad you got it spotted in the end.

A debugging tip, if you use the technique of sticking the SQL in a string variable, you can paste the result from the immediate window into a blank new query design and try and run it, the error then generated is often much more specific/obvious in it's meaning.

[Micron]

Access' misleading error

Not misleading at all - you just didn't understand the message, so perhaps just not as clear as it could be. What you have is a query with parameters. If you point to a table that doesn't have the named field, misspell a field/object name, write sql in code in such a way that vba cannot resolve references, in short, do anything such that any parameter cannot be resolved, you get that message. It's on you to understand the messages presented and eventually you will get to understand most of them without having to research them.

[Lhoj]

Glad you got it spotted in the end.

A debugging tip, if you use the technique of sticking the SQL in a string variable, you can paste the result from the immediate window into a blank new query design and try and run it, the error then generated is often much more specific/obvious in it's meaning.

Nice tip, thanks Minty!