how to prompt user for filename in a Make Table Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with a yes/no field that allows the user to select records that they need. A select query allows them to view the results of their selections but I want them to be able to save those results in a new table. I could copy the select query and make it a MakeTable query but upon doing so I have to designate the name of the new table. I want the user to be able to designate that table name on the fly through a user prompt.
Is there a different approach to this? Is there a prompt I can insret into the name when creating the Make table query?
Thanks for your help.
 
Bruce said:
I have a table with a yes/no field that allows the user to select records that they
Click to expand...
need. A select query allows them to view the results of their selections but I want
them to be able to save those results in a new table. I could copy the select query
and make it a MakeTable query but upon doing so I have to designate the name of the
new table. I want the user to be able to designate that table name on the fly
through a user prompt.
Is there a different approach to this? Is there a prompt I can insret into the
Click to expand...
name when creating the Make table query?

Hi Bruce,

To my knowledge....
the only way I believe you could do
this is through code where you
-ask the user for a table name
-check that table does not exist already
-rewrite the make-table SQL
-execute new SQL

for example (code w/o checking):

strNewTableName = InputBox(strMsg, "Enter New Table Name", strNewTableName)
strSQL = " "SELECT Employees.* INTO " & strNewTableName _
& " FROM Employees WHERE YesField = -1;"
CurrentDb.Execute strSQL, dbFailOnError


Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
 
Here be a more specific example:

Private Sub cmdMakeNewTable_Click()
On Error GoTo Err_cmdMakeNewTable_Click
Dim strMsg As String
Dim boolNewTable As Boolean
Dim strTableName As String
Dim strSQL As String

strMsg = "Please enter a name for the new table."
boolNewTable = False
Do While boolNewTable = False
strTableName = InputBox(strMsg, "Enter New Table Name", strTableName)
If TableExists(strTableName) = True Then
strMsg = "Table '" & strTableName & "' already exists." & vbCrLf _
& "Please enter a different name for the new table."
Else
boolNewTable = True
End If
Loop
'*** Change this for your make table SQL ***
strSQL = "SELECT Employees.* INTO " & strTableName _
& " FROM Employees WHERE YesField = -1;"
CurrentDb.Execute strSQL, dbFailOnError

MsgBox "Have successfully made table " & strTableName & "."

Exit_cmdMakeNewTable_Click:
Exit Sub

Err_cmdMakeNewTable_Click:
MsgBox Err.Description
Resume Exit_cmdMakeNewTable_Click

End Sub
'********************************************
Public Function TableExists(strTableName As String) As Boolean
'from Joe Fallon
On Error Resume Next
TableExists = IsObject(CurrentDb.TableDefs(strTableName))
End Function
'********************************************
 
Back
Top