Variable Table Name in SQL From Statement

[lizzbizz]

How can I write an SQL statement where the From table name changes based on user's input into a form's textbox?

 

[scking]

In it simplest form you merely use the value from the textbox. The problem with using this method is that the user must input the information exactly or the SQL statement will error when run. We have used the MSysObjects table to return all the tables in the database to a combobox. The combobox then will always have a valid table name. Just remember to write the ControlSource property of the combobox to exclude those table you don't want to see, 'Not Like MSys*" and 'Not Like "USys*".

"SELECT * FROM " & Me.Textbox1 ";"

Steve King Growth follows a healthy professional curiosity

 

[lizzbizz]

I'm new to VB programming so I want to stick with the simplest method for now. I am the user so I'll trust that I can enter the table name correctly in the text box.

What code would precede the SQL statement "SELECT * FROM " & Me.Textbox1 ";" ?

Thanks.

 

[lizzbizz]

The following code gives me a Query Definition already exists error. How can I make it temporary (Name = "&quot and still run the OpenQuery command?

Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT * FROM " & Forms!Form1!MonthYear & ";"
Set qdf = dbs.CreateQueryDef("Exception Greater or Equal to $10", strSQL)
DoCmd.OpenQuery "Exceptions Greater or Equal to $10"

 

[PaulF]

try Deleting the Object before you "Create" it again

On Error Resume Next 'In case it doesn't exist
DoCmd.DeleteObject acQuery "Exceptions Greater or Equal to $10"


If you have Error Handling in place in your code, then add a line to send any additional errors to the Error Handling code.

PaulF