Page 2 of 2 FirstFirst 12
Results 16 to 22 of 22
  1. #16
    Netopia is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    16
    Continuing adventures!

    I tried just changing one thing to a variable and leaving the other one alone, just so I could troubleshoot better if there were any problems as I am learning. With the one variable, I continuously get Run-time Error 3141 "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect." I've tried a number of different syntaxes, but can't get it to work.


    I replaced the spot where I called the variable, and it makes the table as I would expect, EXCEPT... the table wouldn't show up until I closed and reopened Access. If I rerun the code (push the button) I get a run-time error 3010, which states "Table '11111' already exists", even though it doesn't show up in the list of tables until I close and reopen Access.
    [UPDATE] I was about to ask you guys how to fix this, but then thought I should certainly take the time to Google it myself. Turned out that it's a very common thing out there that a lot of newbs like me run into. I added "Application.RefreshDatabaseWindow" at the end of the sub and all was happy!

    So the static name works just fine, but I get an error on the syntax when I try to use a variable. I've tested that the variable is working by adding a Message box to display glMailList after updating the textbox, and it comes up with whatever text I enter. I removed the msgBox line after finding that it worked.



    Here are my declarations:

    Code:
    Option Compare DatabaseOption Explicit
    
    
    Global glMonth As String
    Global glMailList As String
    And here is the VBA
    Code:
    Sub MailListTxtBox_afterUdate()
               glMailList = MailListTxtBox
    End Sub
    
    
    
    
    Private Sub RunQ1_Click()
    
    
    Dim SQuery1 As String
    
    
    SQuery1 = "SELECT MONTHLYIMPORT.UID, MONTHLYIMPORT.Imprintcode, MONTHLYIMPORT.Postalcode, " _
    & "MONTHLYIMPORT.Subno, MONTHLYIMPORT.Field3, MONTHLYIMPORT.Field4, MONTHLYIMPORT.Field5, " _
    & "MONTHLYIMPORT.[End Date], MONTHLYIMPORT.Qty, MONTHLYIMPORT.[First Name], " _
    & "MONTHLYIMPORT.[Middle Name], MONTHLYIMPORT.[Last Name], MONTHLYIMPORT.Companyname, " _
    & "MONTHLYIMPORT.Address1, MONTHLYIMPORT.Address2, MONTHLYIMPORT.Address3, MONTHLYIMPORT.Address4, " _
    & "MONTHLYIMPORT.City, MONTHLYIMPORT.State, MONTHLYIMPORT.Country, MONTHLYIMPORT.[Country Code], " _
    & "MONTHLYIMPORT.[Email Address], MONTHLYIMPORT.[Phone Number], MONTHLYIMPORT.County, " _
    & "MONTHLYIMPORT.Ite, MONTHLYIMPORT.Number, MONTHLYIMPORT.[Person Status], MONTHLYIMPORT.[Order Date], " _
    & "MONTHLYIMPORT.[Order Amount], MONTHLYIMPORT.[Order Number], MONTHLYIMPORT.PriceList, " _
    & "MONTHLYIMPORT.PMApma_type, MONTHLYIMPORT.pma_org_name, MONTHLYIMPORT.pma_address1, " _
    & "MONTHLYIMPORT.pma_address2, MONTHLYIMPORT.pma_address3, MONTHLYIMPORT.pma_address4, " _
    & "MONTHLYIMPORT.pma_city, MONTHLYIMPORT.pma_state, MONTHLYIMPORT.pma_postal_code, " _
    & "MONTHLYIMPORT.pma_country, MONTHLYIMPORT.Field42, MONTHLYIMPORT.[Date List Pulled] INTO " & glMailList & " " _
    & "FROM CBJuly18mf AS MONTHLYIMPORT " _
    & "WHERE (((MONTHLYIMPORT.Imprintcode)='LF' Or (MONTHLYIMPORT.Imprintcode)='FI'));"
    
    
    CurrentDb.Execute SQuery1, dbFailOnError
    Application.RefreshDatabaseWindow
    
    
    End Sub
    Thoughts?

  2. #17
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You have to account for the string-within-a-string as below with the double quotes:

    & "WHERE (((MONTHLYIMPORT.Imprintcode)=""LF"" Or (MONTHLYIMPORT.Imprintcode)=""FI""));"

  3. #18
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This is a common technique used to debug this type of thing:

    http://www.baldyweb.com/ImmediateWindow.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #19
    Netopia is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    16
    Quote Originally Posted by davegri View Post
    You have to account for the string-within-a-string as below with the double quotes:

    & "WHERE (((MONTHLYIMPORT.Imprintcode)=""LF"" Or (MONTHLYIMPORT.Imprintcode)=""FI""));"
    That part runs fine. When I put in a static table name, the new table is produced and has only the LF and FI entries. It's the variable name that is causing the problem.

    Thanks,

    Joe

  5. #20
    Netopia is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    16
    Paul, I'll look that over and see what I can glean.

    Thanks,

    Joe

  6. #21
    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 Joe. If you don't see the problem, post the SQL here.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #22
    Netopia is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    16
    Figured it out. The line:

    Code:
    & "MONTHLYIMPORT.pma_country, MONTHLYIMPORT.Field42, MONTHLYIMPORT.[Date List Pulled] INTO " & glMailList & " " _
    Needed to be:

    Code:
    & "MONTHLYIMPORT.pma_country, MONTHLYIMPORT.Field42, MONTHLYIMPORT.[Date List Pulled] INTO glMailList " _
    Thanks much for all of this continuing help!

    Joe

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-05-2016, 02:36 PM
  2. Displaying a Public Variable on a form
    By swenger in forum Programming
    Replies: 1
    Last Post: 06-23-2016, 01:56 PM
  3. Replies: 5
    Last Post: 04-27-2015, 02:40 PM
  4. Replies: 1
    Last Post: 03-28-2013, 07:54 AM
  5. Replies: 5
    Last Post: 05-18-2012, 07:31 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