Results 1 to 14 of 14
  1. #1
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402

    Update VBA

    Hi Guys

    i can't see where i am going wrong with this code

    Dim str As String
    str = "UPDATE SOIDETAILS SET ORDERQTY = forms!SOIAddDetails!QtyTXTBox WHERE WORKORDERID =" & Forms!SOIAddDetails!WorkOrderTXTBox
    DoCmd.RunSQL str

    what i am trying to do is update the "ORDERQTY" feild in the "SOIDetails" table with the value held on the AddSOIDetails forms "QtyTXTBox" text box where the WORKORDERID is equal to the value held in the WorkOrderTXTBox



    if i remove this part "WHERE WORKORDERID =" & Forms!SOIAddDetails!WorkOrderTXTBox" all the OrderQTY in the SOIDetails table are updated.

    if i leave the code in i get this error

    Error 3464 (Data Type Mismatch in Criteria Expresion)

    any help would be brill as this is my first attempt at coding sql in VBA and im getting abit lost

    many thanks

    Steve

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Is the value in Forms!SOIAddDetails!WorkOrderTXTBox numeric
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi Bob

    yes the value will always be numbers such as

    65541

    Steve

  4. #4
    cyanidem's Avatar
    cyanidem is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2015
    Location
    Consett, UK
    Posts
    270
    What's WORKORDERID field data type?

  5. #5
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi cyanidem

    the WORKORDERIF field is a number for example 65441

    Steve

  6. #6
    cyanidem's Avatar
    cyanidem is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2015
    Location
    Consett, UK
    Posts
    270
    Try debug.print(str) before running SQL and see if it returns proper SQL string.

  7. #7
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi cyanidem

    i looked at this in the intermediate window "if this is the correct place" and i got this
    UPDATE SOIDETAILS SET ORDERQTY = forms!SOIAddDetails!QtyTXTBox WHERE WORKORDERID =65533

    65533 is correct as the workorderid entered in the form

    but i still get the error

  8. #8
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    You're absolutely sure that WORKORDERID is defined as a Number Datatype in the underlying Table? Fields that only contain digits, but are never used for math (as in ID numbers, telephone numbers, etc.) are frequently defined as Text, even though they look like 'numbers,' such as 65541.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi missinglinq

    well i have just checked, and your right the datatype for the feild is TEXT

    my mistake i thought it was held as a number

    steve

  10. #10
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi Guys

    just changed my code to
    WHERE WORKORDERID ='" & Forms!SOIAddDetails!WorkOrderTXTBox & "'"

    and it appears to work

    many thanks to everyone who helped me

    Steve

  11. #11
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Try:
    str = "UPDATE SOIDETAILS SET ORDERQTY = forms!SOIAddDetails!QtyTXTBox WHERE WORKORDERID ='" & Forms!SOIAddDetails!WorkOrderTXTBox & "'"
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  12. #12
    cyanidem's Avatar
    cyanidem is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2015
    Location
    Consett, UK
    Posts
    270
    If it always gonna be a number then you could just change its data type in table to number, less hassle in future with quotes etc.

  13. #13
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by cyanidem View Post
    If it always gonna be a number then you could just change its data type in table to number, less hassle in future with quotes etc.
    Keep in mind that you can't store numbers with leading zeros (eg 01245, 000124) as numbers though.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  14. #14
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Glad we could help!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

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

Similar Threads

  1. Replies: 6
    Last Post: 11-05-2014, 05:27 PM
  2. Replies: 7
    Last Post: 08-26-2013, 06:05 AM
  3. Replies: 2
    Last Post: 08-30-2012, 07:59 AM
  4. Replies: 4
    Last Post: 11-10-2011, 03:41 PM
  5. Replies: 1
    Last Post: 01-22-2011, 12:23 PM

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