Auto Record create across tables

[b123]

Hi guys I am new to the forum and new to access. I am attempting to create an estimating program for my company. The specific problem that i am faced with right now is not addressed in the Access manual that I am working in. I invision a multi tabbed form that will take you through all of the disciplines required to produce an estimate. The first tab form will be linked to a quote log table and each sequential tab to be linked to another table in the process. My idea is to have the quote number entered on the first form and have it automatically create like records across the many tables required. Then to switch tabs and continue populating data throughout the other forms on the multi tabbed form. This way there is no chance of entering the quote numbers differently on the subsequent forms and losing continuity. Thanks lot for the help

[RuralGuy]

I do not see the problem. The "QuoteNumber" will be a ForeignKey in probably all of the tables and each tab should have a SubForm on it. With what are you having trouble?

[b123]

You are correct the quote number is the foreign key. i dont want the user to have to input this number on every form. i was hoping to have it populate automatically when the initial record is created. i am concerned that there will be a human error that will cause a disconnect in the quote while reinputting the quote number each time.

[RuralGuy]

Frankly, I think it is a sin to ask a user to input the same data more than once. SubForms are displayed on other Forms by means of a SubFormControl. These SubFormControls have Master/ChildLinkFields properties and the SubFormControl will populate the ChildLinkField in the SubForm for you assuming that you select the right field.

[b123]

I just set up two tables "quotetable" and "vesseltable" with one quotenumber field in each and one input form called "quoteinputfrm". Then I right clicked on the quotenumber field in the quotenumber table and set the property subdatasheet name to "vesseltable" and set the link child fields property to quotenumber. Then i did the same in the vessel table but named the "quotetable" as the subdatasheet and the link master fields to the quotenumber. It is clearly linked when i open the quote table you see the vessel table but it does not populate the field. this is what I have been fighting with.

[RuralGuy]

Setting up a Relationship involves using the Relationship menu, not the SubDataSheet of a table. Have you done that yet? Did you enforce Referential Integrity (RI strongly recommended)?

[b123]

Yes I am working on it now. I went throught and made the primary key [quotetable].[quotenumber] and made the foriegn key [vesseltable].[quotenumber]. Once i did that i went to the relationship table and tie the two together and used reenforcement and cascade edits and deletes. It does respond to edits across table once I have manually keyed in the matching quotenumber in the vessel table but when i generate a new record in the quote table it does not create a new record of the same in the vessel table.

[RuralGuy]

It is the MainForm/SubForm arrangement that adds the ForeignKey to the ChildLinkField of the SubForm, not the tables. Is your MasterLinkField set to the PrimaryKey field of the MainForm?

[b123]

Rural guy I have tried to set up the sub form arrangement like you suggested. I have had some succes when I cycle through records it cylcles through on the subform. However when I create a new record in the main form it still does not create a record in the subform table. Am I on the right track here or is there a way to enter data into one field on one form and have it populate the data into two different tables.

[RuralGuy]

Are you suggesting that Access create a Child record when you create the Parent record? It will not do that without some assistance from either you (in code) or the user.

[b123]

Yes that is what I was hoping to get done. I guess to accomplish this I will have to write some code to make this function happen. Do you think this can be done through Macro or VBA. If so which is best suited for this type of app. Thanks a lot we didnt fix it but we did put and end to a aggravating chase and that is a relief.

[RuralGuy]

I guess I would have to ask why the desire for a Child record when you have no useful data for it yet?

[b123]

The thought is that i would have a multi page form and each of the pages would have all of the estimating data for each discipline in the project. The user would start on page one and fill out customer info and quote numbers. then they would progress through the next pages to complete the data entry for the estimate. When they arrive at the last page set up a button to generate a report. My concern is that if they enter the quote number on each page manually it would be possible to create a disconnect on one of the pages. I was hoping that by entering the info on the first page I could prep the following tables to recieve data from their respective pages. I am not sure if this makes since.

[RuralGuy]

The QuoteNumber should be on the mainForm and then don't even display this data in the SubForms. In fact, the "Quote" table can have an AutoNumber as the PrimaryKey field (PK) and you could use this PK as the ForeignKey (FK) in all of the other tables and the user would never need to see this value but could always see the QuoteNumber it references. I rarely use a NaturalKey as the PK field and never let the user see AutoNumbers. You can still force the QuoteNumber field to be unique. I'm just tossing out some ideas for you to think about. There is nothing wrong with using your QuoteNumber as the PK of the table either.

[b123]

Thanks Rural I just got my forms to auto populate on the additional table. I am setting up a multipage form and have got the quote number linked. First major hurdle complete thanks again