Use same code to update more than 1 table

[Guest]

I was able to get the below code (Thanks to Andrea's response) working for a
table. But I would like to run this exact code for multiple tables. Do I
need to create multiple modules to just change the table name in the code
each time? Or is there a easier way by calling out the table name in a
function. If there is, can someone tell me how/
For example I would like to run this code on Table1, Table2, Table3, and
Table4. All the tables have the same structure, it's just different values.

Thanks!

 

[Guest]

You can pass a parameter to the sub, the table name, and then use it in the
reocrd set

Sub Populate_State(TableName as String)

Dim db As Database
Dim rec As Recordset
Dim strState As String

Set db = CurrentDb
Set rec = db.OpenRecordset("SELECT id, state, city FROM " & TableName & "
ORDER BY ID;", dbOpenDynaset)

 

[David C. Holley]

You should be able to add a parameter which you can then use to
dynamically build the SQL statement. See below.

*HOWEVER* if the information is the same in all of the tables, that is
an indicator that your database is nor normalized. What specific
information is contained in each table?

I was able to get the below code (Thanks to Andrea's response) working for a
table. But I would like to run this exact code for multiple tables. Do I
need to create multiple modules to just change the table name in the code
each time? Or is there a easier way by calling out the table name in a
function. If there is, can someone tell me how/
For example I would like to run this code on Table1, Table2, Table3, and
Table4. All the tables have the same structure, it's just different values.

Thanks!

if IsNull(strTargetTable) = True then
Msgbox("Unable to process records. Target table not indicated")
end if

 

[Guest]

Assuming all the tables you want to update have all the field names you
reference in your code. It will not matter what other fields there are in
the the affected table. Just pass the sub the name of the table you want to
update. Note changes to code below.

 

[Guest]

Thank you. If I want to pass the subname by entering it into a form. Where
do I put the code? Thanks!

 

[Guest]

If I understand your question correctly, where ever you want the updates to
occur (command button, maybe?):

Call Populate_State("MyTableName")

The sub itself can either be in the General section of the form module or in
a standard module.

 

[Guest]

I almost there...
Now I am getting a runtime error 3131 "Syntax error in FROM clause" and the
below line is highlighted:

Set rec = db.OpenRecordset("SELECT id, state, city FROM " & _
tablename & " ORDER BY ID;", dbOpenDynaset)

 

[Douglas J Steele]

Are there spaces in your tablename?

Try

Set rec = db.OpenRecordset("SELECT id, state, city FROM [" & _
tablename & "] ORDER BY ID;", dbOpenDynaset)

Most of us recommend strongly against embedded spaces in names in Access.

 

[Guest]

There was a hyphen, but it worked when using your below example. Thank you!

Are there spaces in your tablename?

Try

Set rec = db.OpenRecordset("SELECT id, state, city FROM [" & _
tablename & "] ORDER BY ID;", dbOpenDynaset)

Most of us recommend strongly against embedded spaces in names in Access.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I almost there...
Now I am getting a runtime error 3131 "Syntax error in FROM clause" and the
below line is highlighted:

Set rec = db.OpenRecordset("SELECT id, state, city FROM " & _
tablename & " ORDER BY ID;", dbOpenDynaset)