Does anyone know how to write a macro or queries that I can add to a button to simultaneously add new record to all tabbed forms in my navigation panel? I have 6 forms. I just want one button on first form that would accomplish this task.
Queries Macro (1 Viewer)
- Thread starter WES9659
- Start date
John Big Booty
AWF VIP
- Local time
- Tomorrow, 03:22
- Joined
- Aug 29, 2005
- Messages
- 8,262
What is the nature of the data you wish to add? How many underlying tables are we talking about?
- Local time
- Today, 13:22
- Joined
- Feb 19, 2002
- Messages
- 43,560
In theory, you wouldn't ever do this. Presumably you have a main table and 6 many-side tables. If you always add a row to each many-side table for every row in the main table, you should probably just have one table. So, there is something wrong with the table design. If you show us a picture of the relationships diagram (with everything visible), we can help.
Thank you for trying to help me. Ok let me see if I can explain my dilemma. I have attached a zip file of data base so hopefully someone can see what I am doing wrong. Under my forms tab I have created a Navigation Form which allows me to tab thru my other forms. The first tabbed form you will see is the Basic Demographics Tab on that specific tab I have added 3 buttons one is a Save Record button, Add Record button and a Delete Record Button. Both Save Record and Delete Record Saves and deletes records on each of the tabs simultaneously like I want. However the Add Record button only works on the Basic Demographics form that it resides on. How can I make it work on all the tabs at once that are included in the Navigation Form? FYI You must add something in Client ID like WR456 to save record. Do I have to do this with queries? Thanks everyone for all your help and consideration.
- Local time
- Today, 13:22
- Joined
- Feb 19, 2002
- Messages
- 43,560
There are a number of problems with the table design.
1. The numbered tables have an autonumber ID as the primary key (good) but you are not using it to define relationships (very bad). They also are all named ID (bad) rather than with unique names. Other relationships have the same problem.
2. Some tables are not normalized. Family for example. Each family member should have a separate row with a relationship field.
3. Table and column names include spaces.
4. Many of the tables are defined as 1-many when they should be 1-1. This application truly does have many descrete pieces of information that need to be captured. Dividing the information into themed tables will help you create appropriate queries and forms. Of the numbered tables, only relationships should actually have a 1-many relationship since you should have one row for each relation you want to track. Right now you have over 40 columns dedicated to this and you will actually only need three. You also won't need to pick some arbitrary limit of relations. You will be able to accomidate the largest/smallest families equaly well. You will be able to fix this by removing the autonumber ID's from the many-side tables. Make ClientID an autonumber and get rid of the ID field from tblBasicDemographics. They change the data type of ClientID in the related tables to Number (Long Integer).
5. The simplest solution is to bind the demographic data directly to the navigation form rather than a subform. Then all the other forms will technically be subforms of that. At that point, setting the master/child links would cause Access to correctly populate the foreign key fields in tbls 2-7 and you won't find it necessary to add rows until they are needed as long as you use left joins. The military table is a good example for this since not everyone will have military service.
Spend some time reading about normalization. I didn't look very hard at all the other tables but I did see that some of them were also incorrectly defined and the lookups are on the wrong fields.
1. The numbered tables have an autonumber ID as the primary key (good) but you are not using it to define relationships (very bad). They also are all named ID (bad) rather than with unique names. Other relationships have the same problem.
2. Some tables are not normalized. Family for example. Each family member should have a separate row with a relationship field.
3. Table and column names include spaces.
4. Many of the tables are defined as 1-many when they should be 1-1. This application truly does have many descrete pieces of information that need to be captured. Dividing the information into themed tables will help you create appropriate queries and forms. Of the numbered tables, only relationships should actually have a 1-many relationship since you should have one row for each relation you want to track. Right now you have over 40 columns dedicated to this and you will actually only need three. You also won't need to pick some arbitrary limit of relations. You will be able to accomidate the largest/smallest families equaly well. You will be able to fix this by removing the autonumber ID's from the many-side tables. Make ClientID an autonumber and get rid of the ID field from tblBasicDemographics. They change the data type of ClientID in the related tables to Number (Long Integer).
5. The simplest solution is to bind the demographic data directly to the navigation form rather than a subform. Then all the other forms will technically be subforms of that. At that point, setting the master/child links would cause Access to correctly populate the foreign key fields in tbls 2-7 and you won't find it necessary to add rows until they are needed as long as you use left joins. The military table is a good example for this since not everyone will have military service.
Spend some time reading about normalization. I didn't look very hard at all the other tables but I did see that some of them were also incorrectly defined and the lookups are on the wrong fields.
