By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,661 Members | 781 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,661 IT Pros & Developers. It's quick & easy.

Join Table in VBA form

P: 4
I'm trying to create a form that imports a text file and join zip code fields to our "Master" database x2. I import the txt file and name it based on a "strTableName" which includes the date, a title, and information I put in a textbox on the form. Up to this point it works well, but beyond that I cannot JOIN the fields as I don't know what to put in the table name.

A second thing that I would like to do is add criteria to the queries if Email is <>"" (Blank) or not.

Here is the current code being used that will work up to

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command21_Click()
  2.  
  3.     Dim strTableName As String
  4.     Dim strSQL As String
  5.     Dim strSQLDirty As String
  6.     Dim strSQLGolden As String
  7.     Dim strQueryNameDirty As String
  8.     Dim strQueryNameGolden As String
  9.     Dim qdfNew As DAO.QueryDef
  10.  
  11.     strTableName = "tbl" & IIf(Len(Trim(Forms![MapPoint]!QueryName)) > 0, _
  12.                                Trim(Forms![MapPoint]!QueryName), _
  13.                                "Unnamed")
  14.  
  15.     DoCmd.SetWarnings False
  16.     DoCmd.TransferSpreadsheet _
  17.               acImport, acSpreadsheetTypeExcel9, strTableName, _
  18.               "C:\Documents and Settings\roxie\My Documents\Book1.xls", _
  19.               True
  20.  
  21.     strSQLDirty = "SELECT [Master Dirty Panel].RecordID, " & _
  22.                          "[Master Dirty Panel].FirstName, " & _
  23.                          "[Master Dirty Panel].LastName, " & _
  24.                          "[Master Dirty Panel].StreetAddress, " & _
  25.                          "[Master Dirty Panel].City, " & _
  26.                          "[Master Dirty Panel].State, " & _
  27.                          "[Master Dirty Panel].Zip, " & _
  28.                          "[Master Dirty Panel].Country, " & _
  29.                          "[Master Dirty Panel].[Email 1], " & _
  30.                          "[Master Dirty Panel].PrimaryPhone, " & _
  31.                          "[Master Dirty Panel].SecondaryPhone "
  32.     strSQLDirty = strSQLDirty & _
  33.                   "FROM [Master Dirty Panel] INNER JOIN [strTableName] " & _
  34.                     "ON [Master Dirty Panel].Zip = [strTableName].[Zip Code]"
  35.  
  36.     strSQLGolden = "SELECT [Master Golden Panel].RecordID, " & _
  37.                           "[Master Golden Panel].FirstName, " & _
  38.                           "[Master Golden Panel].LastName, " & _
  39.                           "[Master Golden Panel].StreetAddress, " & _
  40.                           "[Master Golden Panel].City, " & _
  41.                           "[Master Golden Panel].State, " & _
  42.                           "[Master Golden Panel].Zip, " & _
  43.                           "[Master Golden Panel].Country, " & _
  44.                           "[Master Golden Panel].[Email 1], " & _
  45.                           "[Master Golden Panel].PrimaryPhone, " & _
  46.                           "[Master Golden Panel].SecondaryPhone "
  47.     strSQLGolden = strSQLGolden & _
  48.                    "FROM [Master Golden Panel] INNER JOIN [strTableName] " & _
  49.                      "ON [Master Golden Panel].Zip = [strTableName].[Zip Code]"
  50.  
  51.     strQueryNameDirty = "Project Blast - " & _
  52.                         Format(Date, "yyyy") & "/" & _
  53.                         Format(Date, "mm") & " - " & _
  54.                         IIf(Len(Trim(Forms![MapPoint]!QueryName)) > 0, _
  55.                             Trim(Forms![MapPoint]!QueryName), _
  56.                             "Unnamed") & " - Dirty"
  57.     strQueryNameGolden = "Project Blast - " & _
  58.                          Format(Date, "yyyy") & "/" & _
  59.                          Format(Date, "mm") & " - " & _
  60.                          IIf(Len(Trim(Forms![MapPoint]!QueryName)) > 0, _
  61.                              Trim(Forms![MapPoint]!QueryName), _
  62.                              "Unnamed") & " - Golden"
  63.  
  64. With CurrentDb
  65.     Set qdfNew = .CreateQueryDef("" & strQueryNameDirty, strSQLDirty)
  66.     Set qdfNew = .CreateQueryDef("" & strQueryNameGolden, strSQLGolden)
  67.     .Close
  68. End With
  69.  
  70.  
  71.     Exit Sub
  72.  
  73. End Sub
How do I denote a table name based of a string, and add criteria to the query?
Mar 9 '07 #1
Share this Question
Share on Google+
9 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry, I can't actually follow your question. Can you break it down a bit more and use better descriptions for your problems. Read your original post again and try to imagine you've never seen your database before.

Mary
Mar 10 '07 #2

NeoPa
Expert Mod 15k+
P: 31,470
I can't understand your question clearly enough to concentrate on an answer.
Can you try to rephrase it ultra-clearly. It needs to be very precise if it's remotely complicated (Yours is that), otherwise it's a double-struggle of understanding what you're saying as well as the problem itself.

Another point to consider (I'll fix it for you this time) is to make sure that the code is readable without too much scrolling. Who wants to struggle to understand your question if you can't even try to make it a little easier (I'm sure you had no idea to even think of this, but I'm just trying to explain it from a reader's point of view. Code in a code window - which is good btw - never wraps as the other stuff does, so more care has to be taken to be readable).
Mar 11 '07 #3

NeoPa
Expert Mod 15k+
P: 31,470
Sorry Mary.
I didn't refresh this one before replying :o
As you can see - it was pending for some while ;)
Mar 11 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry Mary.
I didn't refresh this one before replying :o
As you can see - it was pending for some while ;)
A point well made is worth making ... twice? ;)
Mar 11 '07 #5

P: 4
Thanks for the reply, I'll try and clarify.

To start from the beginning, I have a form that imports an Excel file created from Microsoft Mappoint. This is simply a data transfer to a new table. The import always comes from the same Excel file that is simply resaved when I need to import a new file. The file contains a list of zip codes for an area defined by me. Upon import I create a table using the "strTableName"

Expand|Select|Wrap|Line Numbers
  1.  "strTableName = "tbl" & IIf(Len(Trim(Forms![MapPoint]!QueryName)) > 0, Trim(Forms![MapPoint]!QueryName), "Unnamed") 
This works fine. The problem I encounter is when I try and join the zip code fields in two separate Master Databases. I do not know how to JOIN to the table created above as the name changes based on information I input into the form.

Expand|Select|Wrap|Line Numbers
  1. "FROM [Master Golden Panel] INNER JOIN [strTableName]" & "ON [Master Golden Panel].Zip = [strTableName].[Zip Code]"
  2.  
As you can see from the above code I have put in [strTableName] which is the table I want to join to. This is not working.

Essentially, how can I join an existing table with a zip code field to a newly created table which is named by information inputed into a form.

I hope that clarifies and pinpoints my problem.
Mar 12 '07 #6

NeoPa
Expert Mod 15k+
P: 31,470
In that case you need to remember that the strTableName that you've just set up is a VBA variable and not anything the SQL engine can process. You need to add its value (Not a reference to the variable itself) into the SQL string.
Expand|Select|Wrap|Line Numbers
  1. strSQL = strSQL & _
  2.          "FROM [Master Golden Panel] " & _
  3.          "INNER JOIN [" & strTableName] & _
  4.          " ON [Master Golden Panel].Zip=[" & strTableName & "].[Zip Code]"
Mar 12 '07 #7

P: 4
That worked great NeoPa, thanks for the lesson. I'm not sure what type of etiquette is required here, but I thought I'd post my whole code for future reference of what worked "perfectly."

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command21_Click()
  2.  
  3.     Dim strTableName As String
  4.     Dim strSQL As String
  5.     Dim strSQLDirty As String
  6.     Dim strSQLGolden As String
  7.     Dim strQueryNameDirty As String
  8.     Dim strQueryNameGolden As String
  9.     Dim qdfNew As DAO.QueryDef
  10.     Dim strSQLDataEx As String
  11.  
  12. 'Import Table name
  13.     strTableName = "tbl" & IIf(Len(Trim(Forms![MapPoint]!QueryName)) > 0, Trim(Forms![MapPoint]!QueryName), "Unnamed")
  14.  
  15. 'Import
  16.     DoCmd.SetWarnings False
  17.     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTableName, "C:\Documents and Settings\roxie\My Documents\Book1.xls", True, "Zip Codes!"
  18.  
  19. 'Data Exchange
  20.     strSQLDataExchange = strTableName & "ALTER TABLE [" & strTableName & "] ALTER COLUMN [ZIPCode] TEXT(10)"
  21.  
  22. 'Dirty Panel Table Join
  23.     strSQLDirty = "SELECT [Master Dirty Panel].RecordID, [Master Dirty Panel].FirstName, [Master Dirty Panel].LastName, [Master Dirty Panel].StreetAddress, [Master Dirty Panel].City, [Master Dirty Panel].State, [Master Dirty Panel].Zip, [Master Dirty Panel].Country, [Master Dirty Panel].[Email 1], [Master Dirty Panel].PrimaryPhone, [Master Dirty Panel].SecondaryPhone "
  24.     strSQLDirty = strSQLDirty & "FROM [Master Dirty Panel] " & "INNER JOIN [" & strTableName & "] ON [Master Dirty Panel].Zip=[" & strTableName & "].[ZipCode]"
  25.  
  26. 'Golden Panel Blast Table Join
  27.     strSQLGolden = "SELECT [Master Golden Panel].RecordID, [Master Golden Panel].FirstName, [Master Golden Panel].LastName, [Master Golden Panel].StreetAddress, [Master Golden Panel].City, [Master Golden Panel].State, [Master Golden Panel].Zip, [Master Golden Panel].Country, [Master Golden Panel].[Email 1], [Master Golden Panel].PrimaryPhone, [Master Golden Panel].SecondaryPhone "
  28.     strSQLGolden = strSQLGolden & "FROM [Master Golden Panel] " & "INNER JOIN [" & strTableName & "] ON [Master Golden Panel].Zip=[" & strTableName & "].[ZipCode]"
  29.  
  30. 'Golden Panel Dial Table Join
  31.  
  32.  
  33. 'Query Names
  34.     strQueryNameDirty = "Project Blast - " & Format(Date, "yyyy") & "/" & Format(Date, "mm") & " - " & IIf(Len(Trim(Forms![MapPoint]!QueryName)) > 0, Trim(Forms![MapPoint]!QueryName), "Unnamed") & " - Dirty"
  35.     strQueryNameGolden = "Project Blast - " & Format(Date, "yyyy") & "/" & Format(Date, "mm") & " - " & IIf(Len(Trim(Forms![MapPoint]!QueryName)) > 0, Trim(Forms![MapPoint]!QueryName), "Unnamed") & " - Golden"
  36.  
  37. With CurrentDb
  38.     Set qdfNew = .CreateQueryDef("" & strQueryNameDirty, strSQLDirty)
  39.     Set qdfNew = .CreateQueryDef("" & strQueryNameGolden, strSQLGolden)
  40.     .Close
  41. End With
  42.  
  43. strSQLDataEx = "ALTER TABLE [" & strTableName & "] ALTER COLUMN [ZIPCode] TEXT(10)"
  44.  
  45. DBEngine(0)(0).Execute strSQLDataEx, dbFailOnError
  46.  
Thanks again!
Mar 13 '07 #8

NeoPa
Expert Mod 15k+
P: 31,470
You're welcome.
The 'etiquette' is simply to express your needs clearly and to try to ensure the code, when posted, is easily readable without the reader having to scroll sideways too much. There is a FAQ, but general courtesy will normally be just fine.
In your case it wasn't an etiquette issue, but more that you didn't provide enough information so anyone could help you. This is often tricky for first-time posters, but any site of a similar nature will always benefit from a question being put clearly and with all the relevant information.
Mar 13 '07 #9

NeoPa
Expert Mod 15k+
P: 31,470
Just reread you last post and realised maybe you were talking about etiquette for finishing off a thread. There is none specifically laid out, but we always appreciate a thank you, and posting the resulting code is a bonus - so full marks for that :)
Mar 13 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.