Results 1 to 9 of 9
  1. #1
    Authomas is offline Novice
    Windows 10 Access 2007
    Join Date
    Nov 2022
    Posts
    15

    Question Update query with macro

    I need to update a query with a macro. The reason I want to update with a macro instead of an update query is because there are values that the macro gets which are the basis for the updating. Example scenario: The user selects a vendor, the vendors email is found and then the macro gets the Quantity, and PartNumber and input it all in outlook. That part is good but I run into a problem where the macro wont update the query and throws and error (missing operator). Code below:



    Dim strSql As String
    Dim searchFor As String
    Dim dbsTooling As DAO.Database
    Dim rstToolingQuote As DAO.Recordset
    Dim PN(20) As Variant 'gets part numbers which go in to email

    Set rstToolingQuote = dbsTooling.OpenRecordset("qToolingQuote")

    searchFor = InputBox("Would you like RFQ to be checked (y/n)", "Check RFQ?")
    If searchFor = "y" Or searchFor = "Y" Or searchFor = "yes" Or searchFor = "YES" Then
    For I = 1 To rstToolingQuote.RecordCount Step 1
    strSql = "UPDATE qToolingQuote SET RFQ = False WHERE PartNumber = " & PN(I)
    dbsTooling.Execute strSql
    Next
    End If

    Below is the error I get:

    Run-time error '3075':
    Syntax error (missing operator) in query expression 'PartNumber = SHDYTS.9-GDYU'.


  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If part number is text you have to concatenate and use text delimiters (single or double quotes). I used singles below - might be hard to spot.
    Code:
    WHERE PartNumber = '" & PN(I)"'"
    I'm surprised you get that far because you didn't set your db variable dbsTooling
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    The part number is text(string) so you need to enclose it in single -quotes or double double quotes:
    Code:
    strSql = "UPDATE qToolingQuote SET RFQ = False WHERE PartNumber = '" & PN(I) & "'"
    strSql = "UPDATE qToolingQuote SET RFQ = False WHERE PartNumber = """ & PN(I) & """"
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    Authomas is offline Novice
    Windows 10 Access 2007
    Join Date
    Nov 2022
    Posts
    15
    Thanks for the quick reply. No error is being thrown now but it now isn't changing the value of the records in the RFQ column. Also about setting the db, I forgot to include the line of code "Set dbsTooling = CurrentDb".

    here is the code now:
    strSql = "UPDATE qToolingQuote SET RFQ = '-1' WHERE PartNumber = ' & PN(I)'"

  5. #5
    Authomas is offline Novice
    Windows 10 Access 2007
    Join Date
    Nov 2022
    Posts
    15
    Hey just got it. I needed to change FALSE to -1. Thanks for your guys help!

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Look at the examples in post #3....
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    That's not what you were given? And now you've added quotes around a boolean data type? '-1'

    EDIT - I have to go out so I'll let Vlad take over.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Authomas is offline Novice
    Windows 10 Access 2007
    Join Date
    Nov 2022
    Posts
    15
    Yes I removed them after sending that post, my bad lol and now it works.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Hey just got it. I needed to change FALSE to -1. Thanks for your guys help!
    In Access (-1 True, 0 False)

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

Similar Threads

  1. Replies: 4
    Last Post: 08-05-2017, 12:33 AM
  2. Replies: 7
    Last Post: 01-20-2017, 02:21 PM
  3. cant click before update macro
    By justlearning123 in forum Access
    Replies: 1
    Last Post: 06-27-2015, 07:10 PM
  4. Replies: 2
    Last Post: 05-05-2012, 02:34 AM
  5. Running an Update Query from a Macro
    By michaelb in forum Queries
    Replies: 7
    Last Post: 06-02-2011, 09:46 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