Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004

    Unanswered: Update record via textbox value and listbox selection

    Hi I should be able to do the following, but Im struggling..

    On my form I want to select a record in my listbox (list1) and then use a textbox (text1) to update the field orderQuantity of the selected record.

    So if I select record number 001 via the list box and that record has orderQuantitiy of 75, I want to use the textbox to change the orderQuantity to another value.

    Ive tried the following but I get the message "you are about to update 0 rows"..

    DoCmd****nSQL "UPDATE table1 SET table1.orderQuantity = orderQuantity WHERE (((table1.orderQuantity)=forms!form1!text1) AND ((table1.orderDetailsID)=[forms]![form1]![List1]));"
    Where am I going wrong?

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    In your SQL statement there is no reference to the form in the right part of the assignement, so you replace OrderQuantity by OrderQuantity, i.e. you do nothing. Moreover, the references to the controls are inside the quotes, meaning that they are literal while the first condition makes no sense (if I understand correctly what you try to achieve):
    "UPDATE table1 SET table1.orderQuantity = orderQuantity WHERE (((table1.orderQuantity)=forms!form1!text1) AND ((table1.orderDetailsID)=[forms]![form1]![List1]));"
    It sould be something like:
    "UPDATE table1 SET table1.orderQuantity = " & forms!form1!text1 &  " WHERE (((table1.orderDetailsID)= " & [forms]![form1]![List1]  & "));"
    Or more simply:
    "UPDATE table1 SET orderQuantity = " & Me.text1.Value &  "WHERE (orderDetailsID = " & Me.List1.Value & ");"
    If orderDetailsID is not numeric, you'll need to add single quotes around the value from the list:
    "WHERE (orderDetailsID = '" & Me.List1.Value & "');"
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts