Form Update to Multiple Tables

[Def7]

I am currently working on updating a database and wanted to know if there is a way to have a field that is entered dictate which table the form updates to.

For example:

If I select Group 1, I want the table with Group 1's information to be updated. But if I select Group 2, I want their database to be updated.

Thanks in advance for any replies.

 

[Will_B]

If your form's recordsource is a table, the SIMPLEST approach is to simply to put your form into Design mode, open the Properties window for your form, and change the RecordSource. As long as a ALL fields on the form exist in all the tables you might want to update, this would work. It would also DISPLAY records that already existed in the selected table (although you could later specify the form as data-entry-only if you wish). However, if your form's recordsource is a query, this approach becomes more complex.

Assuming you want a more elegant solution, such as selecting the table from a combo box, read on. Even if your form's recordsource is a query, you can adapt the approach, below, to select a table-specific version of the query you need (that is, one query for Group1, one query for Group2, etc.). Once again, this all assumes that ALL fields on the form exist in all the tables you might want to update.

STEP 1: Before going further, make a copy of your database as a backup.

STEP 2: Create a combo box on your form and name it "cmbWhich_Table". Add the following SQL as the RowSource. This will list all tables in your database. You can always edit it later so it only lists the tables or queries that you might want to update, but for the timebeing it will list ALL tables in your database:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") And (Left$([Name],4)<>"Msys") And (MSysObjects.Type)=1 ORDER BY MSysObjects.Name

STEP 3: For the properties of this new combo box, select the "After Update" event and add the following code (where "Me" is VBA-short-hand for the current form and "cmbWhich_Table.Text" refers to the selected table name):

   Private Sub cmbWhich_Table_AfterUpdate()
   Me.RecordSource = Me!cmbWhich_Table.Text
   Me.Requery
   End Sub

... and that's it. Verify that the data you see after each table selection actually changes and actually refers to the table you selected.

You might decide to remove the form's default record source so NO table is selected when the table first opens (if so, see Properties/Data/RecordSource), but that isn't necessary for this to work. As I mentioned earlier, you can adapt this solution to work with queries. For more info, see http://mvps.org/access/queries/qry0002.htm

 

[SydneyGeek]

If you have a table for each Group, particularly if they have the same fields, then you need to rethink the structure.
Combine the data into one table but add a GroupNumber field to differentiate the groups. Then your forms won't need to do any gymnastics, and your data is more likely to be accurate.

Denis

 

[Def7]

Thanks for they replies. It worked perfectly.
Will_B:

Can you let me know how I can edit it so it only list the tables and queries that I want to update?

 

[Will_B]

That depends on how the tables are named.

METHOD 1: Ideally, the tables you would want to select would have a naming convention that would easily identify them by the start or end of their name. For example, UpdateTable_1, UpdateTable_2, etc. If possible, you should use such a convention. It will make filtering your lookup filed MUCH, MUCH easier.

METHOD 2: If you can't change the names, the alternative is to edit the SQL string I sent you so it queries for each table you might want. If this is a lot of tables, it will make the SQL ugly but workable. Also, everytime you create a new table for this form you'd have to add it to the SQL, something you wouldn't need to do in Method 1. This is another reason to using a naming convention.

For both solutions, below, paste them into Query Designer. Select "SQL View" to edit and test them before pasting into your form.

Here is the Method 1 solution. Change "UpdateTable*" to whatever your naming convention is, but leave the asterisk:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((MsysObjects.Name) Like "UpdateTable*") AND ((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=1))
ORDER BY MsysObjects.Name;

Here is the Method 2 solution. Replace "Table_1", "Table_2", etc., with each and every table name that you need. Keep the quotation marks around each.

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((MsysObjects.Name) In ("Table_1","Table_2","Table_3")) AND ((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=1))
ORDER BY MsysObjects.Name;

 

[Def7]

I am able to use method 1 but where do I enter the code?

 

[Will_B]

Where I have

"UpdateTable*"

... replace it with the naming convention you are using --- and be sure to keep the asterisk at the end and the quotation marks.

When you are done, test this SQL string in Query Design to see what tables it lists. When you are satisfied it works, do the following:

1) Make a backup copy of your form (just in case)
2) Open your form in Design Mode.
3) Click on your cmbWhich_Table combo box (lookup field).
4) If the properties Window is not already displayed for this box, right-click on the combobox and select "Properties".
5) Find the property of "RowSource". It will be on the "Data" tab of the Properties window.
6) For "RowSource", remove what is currently there (it should be a table name) and paste the SQL string you just edited ("SELECT MSys...").

And that is it. If you have not already created your cmbWhich_Table combobox on the form (where you will be selecting which table to update), see my March 9 posting below for instructions.

 

[Def7]

It worked perfectly. Thank you.