Update Query Doesn't work from my Form

[Wagon]

Good Morning,



I have a form that I'm creating to update volume delivered by suppliers.

I want to be able to pick a date from a drop down, Pick the order number and insert then the volume into a text box and it up date my table at the click of a button

The query I have is as follows:

Private Sub Command21_Click()Dim volume As Variant
Dim Deliverydate As Variant
Dim setdelivery As String
Dim OrderIDNo As Variant


OrderIDNo = Text22.Value
volume = Text19.Value
Deliverydate = Combo24.Value


setdelivery = "Update DeliveryTemplate " & _
"Set " & Deliverydate & " = '" & volume & "' " & _
"where [OrderID] = '" & OrderIDNo & "' "
DoCmd.RunSQL "setdelivery"
End Sub

When I click the button I get the following error - Run Time error 3129 Invalid SQL statement

So I went into Debug and got the query from the Immediate window which returned the following:

Update DeliveryTemplate Set June13 = '999' where [OrderID] = '1'

I copied and pasted this into a standard access query and ran it as normal to try and find an error but it ran fine and updated as requested

Does anyone know why it will work in a standard query but not through VBA on my form?

Any help would be much appreciated

Thank you

[ranman256]

Instead of using SQL, use a query. The query will not get the SQL wrong. Then just rrun
docmd.openquery "quUpdMyQuery".

but you put the SQL var in quotes...don't...
docmd.runsql setdelivery

[Wagon]

Apologies, but I don't understand how that would work,

Are there not too many variables for a standard query to work?

The volume for each OrderID will change for deliverydate

I need to put the figures into the form to populate the table

thank you

[orange]

Wagon,

Step back and tell us about your set up in plain English. Forget the sql and query for the moment--- just simple terms.
You don't really have a field called June13, do you? What is the DeliveryTemplate (again simple terms).

Something along the lines of...

We are a small company that does..... Each day we receive...... To account for our.... we have to update the......
We get Orders from.... ???

Good luck.