Can't create new record in a related table

[acannon]

I am building an application for tracking equipment on engineering projects. The db contains a table EqBasic which contains basic information about every equipment item in the project. There are also related tables that contain details about the equipment items. There are different related tables depending upon the type of equipment item.



I use a command button on the form for displaying/entering the basic info to add a new item, and that works well. Currently I have to manually create the record in the related detail table, but am trying to tie that process into the process of creating the new record in the basic table. So far I have no success at all. I can get the form for entering the details of the related item to open, and navigate to a new record, but nothing I have been able to do will allow me to programmatically put the necessary entry into the 2 fields used as a compound unique index to relate the 2 tables. The 2 fields for the unique index are 2 of the 3 fields that are concatenated for a complete equipment number. I have no issue entering them into the new record in the basic table, but can’t get them into the new record in the related table using VBA.

Any help will be greatly appreciated! I’ve been pulling out what little hair I have left for several days now on this problem.

Thanks,
Alan

[ranman256]

You need a form with the single master record.
in the form add a subForm that links the master key. this will add the Master key automatically to the child table.
The form wizard will build this for you.

[acannon]

I'll try that, but I don't think it will work. I have 9 different related tables which have different data in them depending on the type of equipment item. Any given equipment item has 1 record in the basic info table and 1 record in only 1 of the 9 related detail tables. I think I've found a way to do what I'm attempting with SQL code and the DoCmd.RunSQL functionality.

I'll post back the results of both efforts.

[June7]

Then you can have 9 different subforms organized on a Tab control.

[acannon]

Thanks for the suggestions. I found the DoCmd.RunSQL approach worked very nicely. I used a Select Case structure to set the first part of the SQL string of "INSERT INTO {tablename}, then completed the common part of the SQL string outside the Select Case. It works just fine and I didn't have to revise or create any additional forms.
Thanks again to both of you! ... Alan

[June7]

Glad you found solution.

But I am wondering - isn't there other data that must be input to the detail tables?

[acannon]

Yes, there is other data for the detail tables, but this action simply creates the record in the detail tables. Typically we will enter the basic data at the start of the project, then as detailed info is developed we add the detailed information. I had encountered the problem that the record didn't exist in the detail table to enter the data and this way my users don't have to know how to create the record. It is automatically created when the basic equipment record is created.

[June7]

And how is more data entered into the 9 detail tables - a single form that has all 10 tables in the RecordSource?

Sounds like my db is similar - main table with multiple 1-to-1 related tables but not every detail table will have an associated record. I have a 'login' form for the main table and use VBA to create related record(s). Then a single form with all the tables in the RecordSource for detail data entry.

[acannon]

The primary form, which is the basic info form, has a command button that will open the appropriate detailed form to the record for the current record in the basic form. A command button closes the detail form and returns to the basic form. There is also an alternate path to the detail form, and the command button code is able to detect the correct calling form and return to the one from which it was called.