Can a variable value from a form be used to query tables?

[Guest]

I would like to set up a utility that I could run the same code on different
tables by changing the value of an unbound text from a form to the code. In
the following code what could I put in for [any table] that would allow this?
Or is there another way?

Sub any_Table()
Dim db As Database
Dim rsProvider As DAO.Recordset

Dim strSQL As String
Dim iPos, iLen As Integer

Set db = CurrentDb

strSQL = "Select * from [Any table]"
Set rsProvider = db.OpenRecordset(strSQL, dbOpenDynaset)

blah blah
end sub

Thanks

 

[Douglas J. Steele]

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

 

[Douglas J. Steele]

I'm not quite sure what you're asking.

Are you saying that have a text box on your form, and you want to be able to
put the name of the table there?

If the text box is named, say, txtTableName, you'd use:

strSQL = "Select * from [" & Me.txtTableName & "]"

(The reason for leaving the square brackets there is just in case any of
your table names include blanks, which I wouldn't recommend by the way).

In fact, you could use a combo box that returned the names of the tables,
and save your users having to type. The following query will return all your
table names, so could be used as a Row Source for the combo box:

 

[Douglas J. Steele]

Apologies: these responses are getting sent before I'm finished!

The following query could provide the Row Source for your combo box:

SELECT [Name]
FROM MSysObjects
WHERE [Type] IN (1, 4, 6)
AND [Name] NOT LIKE "MSys*"
ORDER BY [Name]

If you're asking for something else, post back with more details (and an
example, if possible)