Results 1 to 4 of 4
  1. #1
    Wagon is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Dec 2015
    Posts
    11

    Update Query Doesn't work from my Form

    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

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,837
    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

  3. #3
    Wagon is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Dec 2015
    Posts
    11
    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

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,509
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Open Query doesn't work
    By jj1 in forum Access
    Replies: 3
    Last Post: 02-04-2015, 01:01 PM
  2. My query code doesn't work
    By blacksaibot in forum Programming
    Replies: 2
    Last Post: 03-08-2012, 02:59 PM
  3. Parameter Query doesn't work
    By Kimmie in forum Access
    Replies: 5
    Last Post: 10-07-2010, 01:30 PM
  4. Query doesn't work the day after
    By sithis876 in forum Queries
    Replies: 1
    Last Post: 07-13-2010, 07:11 AM
  5. Form doesn´t update with Query.
    By disturbedgod in forum Forms
    Replies: 10
    Last Post: 02-09-2010, 02:00 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums