Make-Table Query Problem!!!!

[Kckronic21]

I have a problem with this query. I have the following in my sql statement:

SELECT [Employees].[Name], [Employees].[Position], [Employees].[City], [Employees].[State], [Employees].[Zip Code], [Employees].[Phone_Number] INTO [Forms]![City_Form]![City_txtbox]
FROM Employees
WHERE ((([Employees].[City])=[Forms]![City_Form]![City_txtbox]));

What I would like to do is to have the new table name change when the user chooses a different city. For example, If the user chooses "Charlotte" in the City_Form and runs the query, I want the new city to be named "Charlotte". Please tell me what I am doing wrong. Thanks!

 

[tlbroadbent]

You must create the SQL statement dynamically in VB code. Include the following code in a command button on the form. Yo can use the keyword ME to represent the form as long as the code is in a module of the form.

Dim strSQL As String

strSQL = "SELECT [Name], [Position], [City], [State], [Zip Code], [Phone_Number] INTO " & Me.City_txtbox & " FROM Employees WHERE [City]=" & Me.City_txtbox & ";"

DoCmd.RunSQL (strSQL)

Some cautions about your logic. If this will be a shared database, if two users select the same city, they will encounter conflicts. Another problem is the growth of the database. You'll want to compact the database regularly if you are adding and deleting tables frequently. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.