Adding a new record to a form with multiple tables

[Zan1818]

Hello everyone,

My problem is the follow: I have a form that is based on a query that has multiple tables. These tables contain various information about 12 different cars. The tables are related by the license no that appears in each table. I would like to know if there is an easier way to add a 13th car by just changing something on the form or do I just have to add the new license no in each and every table included on that form? I tried adding a new page on the form and trying to fill it in that way but when I tried to save it the form didn't let me. I'll appreciate the help

[aytee111]

A form for entry has a table (or a simple query) as its record source and record adding and changing occurs naturally. Is your table design normalized? If you described what each table contains/why there are multiple tables per car we could help further.

Otherwise you could create a subform for each of the tables.

[Zan1818]

A form for entry has a table (or a simple query) as its record source and record adding and changing occurs naturally. Is your table design normalized? If you described what each table contains/why there are multiple tables per car we could help further.

Otherwise you could create a subform for each of the tables.

So what the form does is tracking the maintenance of each car. The different tables are the different kinds of repairs that can be done like oil change or tire rotation and so on. I have 16 tables for 16 different types of "repair". All of them are contained in a query and the form feeds the query to make the changes in the tables. There is no problem if I'm just replacing a car since I can just change the information in the existing record but if I need to add an extra car it seems that I need to add the license no on each table so that it can appear on the form.

[aytee111]

Why don't you rather normalize your data structure? Then you won't get problems like these - and may more that are in your future.

A table for car identification
a table for type of repair
a table for carID/licence no, repairID, and repair details

[Zan1818]

I don't think that will work with the way I want to display it on the form. Basically what I have is a form that for each car shows when each repair date and miles when each repair was done and at how many miles it should be done next. The form shows it so the user can easily figure out if the car needs the repair done or not. If I do what you say the form will only record the information on the table and the user will have to go back to it so see it. I also thought about doing a report that I still don't think I'll be able to display it the way I would like it. If you think there's a good way to do this please let me know. I can also attach a pic of the form to you show you how it looks.

[aytee111]

Access is a database system and is designed to be used as such. Here is some good reading to do - see the bottom of post # 7 https://www.accessforums.net/showthread.php?t=65906

[ssanfu]

So what the form does is tracking the maintenance of each car. The different tables are the different kinds of repairs that can be done like oil change or tire rotation and so on. I have 16 tables for 16 different types of "repair". All of them are contained in a query and the form feeds the query to make the changes in the tables. There is no problem if I'm just replacing a car since I can just change the information in the existing record but if I need to add an extra car it seems that I need to add the license no on each table so that it can appear on the form.

If you had a properly designed/normalized dB, you will not have any problems adding 1 more car or 50 cars. It would just be a matter of entering in the info.
With 16 tables for "repairs", what would happen if you wanted to add 1 more "repair" in the current design? You would have to add another table, change queries, change code (if any), change form designs and change reports.

Given what you have posted, you should only have 3 to 5 tables.
Here is an example that might help http://databaseanswers.org/data_mode...nter/index.htm
Your design won't be exactly like this, but you see the relationships?

I don't think that will work with the way I want to display it on the form. Basically what I have is a form that for each car shows when each repair date and miles when each repair was done and at how many miles it should be done next. The form shows it so the user can easily figure out if the car needs the repair done or not. If I do what you say the form will only record the information on the table and the user will have to go back to it so see it. I also thought about doing a report that I still don't think I'll be able to display it the way I would like it. If you think there's a good way to do this please let me know. I can also attach a pic of the form to you show you how it looks.

Don't be confused on data storage vs data display. They are two very different subjects.


Would you post a picture of the relationship window?
Or maybe post your dB? Make a copy of the dB, delete all but a few records (change any sensitive data), do a "Compact & Repair", then Zip it.