Post Info to 4 tables from one form?

[DWEvans]

There should be a way that ACCESS 2010 can post info to multiple tables from one form! I just don't seem to be getting the Relationships set up right! When I think I have it, yet another issu pops up! The latest one is everything looks good in Design View but Nothing shows up in a usable Form View. I am designing a mini DB to possibly share for comments! ASny constructive advice will be appreciated!

[Missinglinq]

The basic rule of thumb is one Form/one Table. The vast majority of multiple-table Forms are Read-Only. When a Form is Read-Only, and it contains no Records, basically nothing shows in Form View, not even the Controls (Textboxes, Comboboxes, Labels, etc.) which I suspect is what you're experiencing,here.

You've really given us nothing, in plain language, about the purpose of your database, and so advice is hard to give, but usually a Main Form, based on one Table, with Subforms, each based on a single, related Table, is the correct approach for this sort of thing.

Linq ;0)>

[ssanfu]

There should be a way that ACCESS 2010 can post info to multiple tables from one form!

The only way I know to do what you want, other than what Linq posted, is to use an unbound form (which means unbound controls) and write (a lot of) VBA code to do the inserts into the different tables.

[orange]

Further to the comments from Linq and Steve, get a good description of what your database is about. Keep it in simple, plain English. Then add some detail to flesh it out.

The real secret is to get your tables and relationships designed to support your requirements.
Here is a tutorial that will help you with table design and relationships. You have to work through it (~30 minutes) but what you learn can be applied to any database.
Good luck.

[DWEvans]

I will try to explain it in a simple reduced scale! I have say 6 buildings that will be inspected several times a day. So I have six tables, one for each building, with the Primary Key being an auto number Record ID. This way we have a DB of all the inspections for each building. Each of these tables have the same say 12 fields of inspection data, such as Start Time, # of Inspectors, room temp, Ending Time, & Next Inspection Due Time. I am trying to build one form that the info can be entered on for all 6 inspections but have the data posted to the six individual tables for good record keeping. But another twist is the fact that I have a main table with the Primary Key called Building, and there are six records, one for each Building. Does this explain the concept well enough to help me make it work? I already found that Creating a Form with 6 tabs with a building subform for each tab may be the answer? But I need the sub form to post to the Main Table and the Individual Building table too. The reason for this is there will be someone monitoring the Main Table and seeing the Inspections as they occur! Hopefully this will explain the concept well enough to get advice? Also, on another issue, How can I have a drop box pop up to have an inspector choose his name to begin his entries and have his name auto populate into a field "Submitted BY: on each form? i assume I can use a hidden field on each form so the user is unaware, but his name gets included with each record as the inspector

[Micron]

Sorry to say your issue is one of poor design in the beginning, and your issues will only become more numerous as time goes on. There is no way you should have 6 tables for inspections - one for each building. I strongly suggest you start over and avoid more trouble later. Not what you want to hear, I'm sure. You might want to research database normalization, then post back with details on your intended design, asking for comments. We could come up with something off the cuff, but I think you'd be better served by learning the concepts rather than being spoon fed.

[orange]

I agree with micron. Work through the tutorial I suggested in post #4. You will see how design can be done consistently and learn the concepts of design.
6 tables - 1 for each building suggests a design issue from the start.

Your description tells us what you have done and that it isn't working the way you want. And your effort to proceed by using some form will lead to some frustration. Too much Access and database jargon.

Good luck.

[Xarkath]

Hello,

I am far from being an expert so please take this with a grain of salt and really consider what everyone has said so far.

That being said, I do have something similar going on in that I have 1 data entry form that supplies 4 separate tables. How I did this was to make a table that only ever contains 1 record but has every field that might be needed for the 4 main tables. I built the from around that 1 complete table. Then I set up separate queries to pull the applicable data from the complete table and append the data to each of the 4 main tables as appropriate. I used VBA to build rules around what query to run and when and attached it to a submit button. I set it up so that when the form closes it clears the data from the complete table so that it only maintains the current entry. This probably broke every Access rule there is but it does work.

[Micron]

Xarkath, there are many ways to skin the proverbial cat. If what you have works, then you can consider it a success even if it's not pretty and I get that sometimes the destination is more important to some than the journey. However, there is a correct way to do things, and not learning the correct way usually means more work in the long run. It also does not provide a solid foundation to build on.

[Xarkath]

Xarkath, there are many ways to skin the proverbial cat. If what you have works, then you can consider it a success even if it's not pretty and I get that sometimes the destination is more important to some than the journey. However, there is a correct way to do things, and not learning the correct way usually means more work in the long run. It also does not provide a solid foundation to build on.

Agreed (and I can't say that firmly enough), that is why I clarified that he should really listen to the rest of you. But if he is like me then he has no propper training and a deadline that does not allow time for propper training. I was just suggesting something that might get him through should time be an issue.

[ssanfu]

Also, on another issue, How can I have a drop box pop up to have an inspector choose his name to begin his entries and have his name auto populate into a field "Submitted BY: on each form?

That depends on the table structure.
If you had tables:
tblInspectors
tblBuildings
tblInspections

then you could have a combo box of Inspectors, use a little VBA in the form before update event to insert the inspector PK field or the inspector name.


Something like this:
Attachment 22691

[orange]

I agree with Steve -- as I suggested here.

[Micron]

If inspectors are not the only type of user, why not a user table with a Level (for lack of a better word) with values such as User, Suprvsr, Admin, Inspctr, etc. instead? Easy to get his name into a form control based on his computer/network login id as well. You can also prevent, to a degree, anyone from accessing inspection data if they're not supposed to, based on the Level. I apologize if I missed something that indicates the db is only for inspectors.