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?