Update VBA Query

[HansBades]

I can't get my update to work in my vba code. The relevant part is posted below. I am trying to change the category that a selected SKU falls into. The user chooses the SKU from a list box (Skuchosen variable). Choosing it indicates they want to update the category it belongs to. The update is supposed to change its category id to 10. But when I execute the code, I keep getting a message to enter a "parameter value". Any thoughts?


strsql = "Update SKU " _
& "Set SKUCategoryIDFK = 10 " _
& "Where SKU = " & SKUCHOSEN _
& ";"

DoCmd.SetWarnings False
DoCmd.RunSQL strsql

[Micron]

And that parameter prompt is ?? Maybe "Where SKU = " & SKUCHOSEN should be
"Where SKU = '" & SKUCHOSEN & "'"
assuming sku field is text data type.

[ranman256]

does :

SKUCHOSEN have a value?
did you try running it as a query instead of code?

[HansBades]

does :

SKUCHOSEN have a value?
did you try running it as a query instead of code?

Yes, SKUCHOSEN=Me.SKULISTBOX.Value
One the form, I have a listbox with all the SKUS for a given category. The user chooses the applicable SKU in the listbox and then clicks a button to change its category. I am still getting a syntax error.

[Welshgasman]

Debug.print strSql and post back the output here.

[orange]

@HansBades

To confirm, you have a form with a Listbox and you want to execute an Update query to change a record in your SKU table.

What is the rowsource of your SKULISTBOX? (from where does it get its values)
Are you using a specific form event to invoke the query? If so, which event?


Using Debug.print strSQL will show you how Access has interpreted you sql. Great approach for debugging/confirming things before actually executing code.

Please post all related code.

[Gicu]

Please try:

strsql = "Update SKU Set SKUCategoryIDFK = 10 Where SKU = " & Me.SKUCHOSEN 'assumes your listbox name is SHUCHOSEN and it is bound to a numeric column
CurrentDb.Execute strsql,dbFailOnError

Cheers,

[orange]

??? What exactly is SKU?? Table name, field name, both?

[HansBades]

Everyone,
Thank you. I think I got it working. I used Micron's suggestion. I didn't realize when I first tried it that I copied it incorrectly. Also, thank you everyone for the other tips. It will be very helpful to me going forward. Thanks again.