Results 1 to 15 of 15
  1. #1
    MuskokaMad is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    17

    Syntax For After Update Event

    Thank you in advance for your help!



    I am Trying to create an update query that will change the value of a field in an OnUpdate Event.

    Here is what I am trying to do.

    I have a Form frm_NewOrderEntry that is bound to a Table tbl_Orders with a Primary Key of ID.

    I have a Subform sfrm_NewOrderDetails that is bound to a Table tbl_OrderDetails with a Primary Key of ID and a Foreign Key of Order_ID.

    In the Subform I have a Combo Box cbo_Product that does a lookup on a Table tbl_Products which is related to tbl_OrderDetails with a Foreign Key of Product_ID.

    In the AfterUpdate Event for the Combo Box I Have the following code already.

    Private Sub Product_ID_AfterUpdate()

    txtDescription = DLookup("ProductDescription", "tbl_Products", "ID=" & Product_ID)
    txtPrice = DLookup("RetailPrice", "tbl_Products", "ID=" & Product_ID)
    txtQuantity = 1
    txtDiscount = 0

    End Sub

    Table tbl_Products also has a Field ProductStatus_ID.

    I would like an update query that can be triggered in the ONChange Event that changes the ProductStatus_ID Value for the Selected Product.

    Here is what I have tried... but get an error.

    Private Sub Product_ID_AfterUpdate()

    Dim strSQL As String

    txtDescription = DLookup("ProductDescription", "tbl_Products", "ID=" & Product_ID)
    txtPrice = DLookup("RetailPrice", "tbl_Products", "ID=" & Product_ID)
    txtQuantity = 1
    txtDiscount = 0

    strSQL = "UPDATE tbl_Products " & "SET ProductStatus_ID = " & 5 & " WHERE Product_ID = " & Product_ID
    CurrentDb.Execute strSQL, dbFailOnError

    End Sub


    What have I done wrong?

    Jason

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What error do you get? Do the lines with the DLookup's run correctly? I would drop the unnecessary concatenations. All they do is slow down execution and make it confusing to look at:

    strSQL = "UPDATE tbl_Products SET ProductStatus_ID = 5 WHERE Product_ID = " & Product_ID
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    MuskokaMad is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    17
    I get a Runtime Error 3061 Too Few Parameter. Expected 1.

    Changed the SQL Statement... Same Error

  4. #4
    MuskokaMad is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    17
    The DLookups Run Fine

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Double check the spelling of all the field and table names. Do they actually contain spaces rather than the underscores? What is the data type of ProductStatus_ID?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    MuskokaMad is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    17
    None of the field or table names contain spaces and Product Status_ID is a Number field

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    And the spellings? I note that you use a different field name in the criteria of the DLookup than in the SQL. Is that the problem? That error is telling you that it can't find something.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    MuskokaMad is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    17
    Ok... so now the SQL works... you were absolutely right I used the wrong name so it works great but now the other fields have stopped updating... Sorry to keep pestering... this is driving me nuts!

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Really? Getting the SQL to work shouldn't have affected what came before. What's the code now?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    MuskokaMad is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    17
    Private Sub Product_ID_AfterUpdate()

    Dim strSQL As String

    txtDescription = DLookup("ProductDescription", "tbl_Products", "ID=" & Product_ID)
    txtPrice = DLookup("RetailPrice", "tbl_Products", "ID=" & Product_ID)
    txtQuantity = 1
    txtDiscount = 0

    strSQL = "UPDATE tbl_Products SET ProductStatus_ID = 5 WHERE ID = " & Product_ID
    CurrentDb.Execute strSQL, dbFailOnError

    End Sub

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What table is the form bound to (presumably not tbl_Products)? Executing the SQL shouldn't have stopped the other things from working. Do you get an error, or the textboxes just don't get populated? Given the 3 different trips to the table, I would actually use a recordset, but this should still work. Can you post the db?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    MuskokaMad is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    17
    Here it is. The form is frm_NewOrderEntry2

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In this sample, the textbox names do not have the "txt" at the beginning. If I change that, the code works as expected.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    MuskokaMad is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    17
    That's what I get for Version Control. Never would have seen that without a second set of eyes.

    Thanks so much!

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem, that's what we're here for. Welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Event Calendar Help
    By Nosaj08 in forum Forms
    Replies: 9
    Last Post: 06-11-2010, 11:19 AM
  2. Form does not update after event
    By pedro in forum Forms
    Replies: 10
    Last Post: 12-23-2009, 07:54 AM
  3. Replies: 21
    Last Post: 06-03-2009, 05:54 PM
  4. sql syntax error
    By SMAlvarez in forum Access
    Replies: 1
    Last Post: 03-12-2009, 09:43 PM
  5. Befor update event
    By wasim_sono in forum Forms
    Replies: 1
    Last Post: 03-24-2006, 07:21 AM

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