Form text box value as table name in query

brickelldb

Registered User.
Local time
Today, 12:42
Joined
Mar 9, 2009
Messages
70
Hello. I'm trying to use the value in a text box on a form as the table name in a query.

Example

Form Name: Frm_Module1
TextBox Name: text500

I want to use the value in text500 as the tablename in this query:

SELECT [tablename].Field1, [tablename].Field2, [tablename].Field3
FROM [tablename];

Any help is GREATLY APPRECIATED. I have been googling for three hours. :banghead:
 
Here's some example code. This would select all fields from whatever table name you enter in the text box and return the record count. You would need to add error handling in the event that someone enters an invalid table name in the text box. Replace the .Recordcount section with code for whatever it is you want to do with the returned record set.

Code:
Dim strSQL As String

strSQL = "Select * From " & Me.Text500 & ";"

With CurrentDb.OpenRecordset(strSQL)
    If .RecordCount <> 0 Then
        .MoveLast
        MsgBox .RecordCount
    Else
        MsgBox "Selected table has no records"
    End If
End With
 

Users who are viewing this thread

Back
Top Bottom