Form to Multiple tables (1 Viewer)

[KindleFire]

I have a form in which I would like to update multiple tables by the response of one field. I would like to show that same field in the fields of multiple tables. No matter how crazy this may or may not sound IS it possible and if so...Please Help....

 

[D-Fresh]

You could just use SQL statements in the After Update event or on the click of a button. USe the following code:

Dim MyDB as database
Dim MySQL as string

'If it's a new record, do the following:
MySQL = "INSERT INTO [Table_Name] (FieldName) VALUES ('" & me![Form_Field] & "')"

'or an existing record do this:
MySQL = "UPDATE [Table_Name] Set FieldName='" & me![Form_Field] & "' WHERE Criteria"

Set MyDB = currentdb
MyDB.execute(MySQL)

All you have to do is Change the name of the table, field name, and form field name for each table and re-execute the MySQL command. Note: If it is a numeric value, you do not need the apostrophes(Single Tick Marks) around the Form Field Value.

 

[KindleFire]

The insert works great HOWEVER,

When I do the UPDATE one it tells me that it cannot find the table, in which I know exists, any help?

 

[D-Fresh]

Make sure you spelled the name correctly. A little tip, click on the table name in the database window, and then hit F2. You can then press ctrl-C to copy the name. Hit Esc, then go to your code and paste the name into the Update statement. That way, you'll be sure to know you used the correct name.

Doug