Updating 2 Tables From 1 (unbound) Form Field (1 Viewer)

[FONT=&quot]I'm creating a database to track products, European directives where compliance is required, and standards used to demonstrate compliance. I've hit a wall where, because of the structure of my tables, I need to create a record in 2 tables simultaneously from one form.
[/FONT]
[FONT=&quot]Long story short, i have 3 tables all joined together through relationships, so when I wish to have a new record created, a new record must be created simultaneously in all 3 tables. [/FONT]


[FONT=&quot]I have DirectiveName sorted out with a bound form so the user can create a new Directive name first. A name must first be created (if it does not exist) because any name can be used on multiple directives.[/FONT]


[FONT=&quot]After the user creates a name, they must add the Directive number from the tblDirectives:[/FONT]


[FONT=&quot]If a directive doesn't exist, a new one must be created in both tblDirectives and tblDirectivesTracker because they are joined with a one to one relationship. There shall only ever be 1 matching record in both tables at any given time. If I try to add a new Directive to tblDirectives, I receive the following error:[/FONT]


[FONT=&quot]So I've created an unbound form where my goal is to have Directives control above create a new record in both of the following tables/fields:[/FONT]

• [FONT=&quot]tblDirectives | Field: Directives[/FONT]
• [FONT=&quot]tblDirectivesTracker | Field: Directives[/FONT]

[FONT=&quot] I've tried dabbling with an append query but haven't had much success, I'm not sure this is the right tool for the job. I envisioned running an append query to append a new record into both fields/tables using the value from the control, but I haven't found a solid example of how to interface the control's value into the append query.
[/FONT]
[FONT=&quot]Does anybody any example databases using an unbound form to create a new record into 2 tables/fields based on the value of a single control? Either through append queries, VBA, or expressions?[/FONT]

[FONT=&quot]If not, does anybody possibly have any guidance for the simplest method to achieve this?[/FONT]

Why an unbound form? This sounds like a simple main form, subform. No code needed.

Main form list of directive names (I assume this is like a group of directives). Subform with related directives since many directives share the same Name (group). Then sub subform for the directive details. However, I do not understand this.

If a directive doesn't exist, a new one must be created in both tblDirectives and tblDirectivesTracker because they are joined with a one to one relationship. There shall only ever be 1 matching record in both tables at any given time. If I try to add a new Directive to tblDirectives, I receive the following error

Click to expand...

That does not make any sense to me. If the directive tracker is really one to one why is it not just part of the directive table? What are your primary keys and what are your foriegn keys?

Hi

Can you upload a zipped copy of your Db??

If you do not combine the tables then you can still do this is Main form subform.
MainForm based on Directive Name table. Subform is based on a left join of Directive to directiveName.
This assumes the subform is linked to the main form by the DirectiveName primary key to directive foreign key.
If you pick a name in the main form and start typing into the subform the subform control will enter the foreign key without code. Also because of the left outer join if you type in any of the fields from DirectiveTracker the query creates the foreign key in the directiveTrackers

One more thing. If you are simply assigning a name to a directive, then I would not even have a subform, but the main form is directives. The name would be assigned from a combobox. Only reason for the main form subform set up would be if you want to see that parent child grouping.

Hi guys, thanks for the quick replies. Here's the zip file. Its a work in progress, so forgive me if its still sloppy.


I'll read through everybody's responses and post up a reply. Thank you guys for the guidance.

You have duplication that should not be in the tables. Also you may want some better names. I would name my ids: Name_ID, Directive_ID, and Tracker_ID for clarification.

In the Directive Table I would:
call the primary key Directive_ID
Call the foreign key Name_ID
get rid of trackerID

In the tracker table
call the primary key Tracker_ID
call the foreign key Directive_ID
get rid of Directive 'from join
get rid of directive name 'from join

You are doubling up your foreign key. You have a foreign key in directives to tracker and a foreign key from tracker to directive.

Still do not under stand why tracker is a separate table. Can you explain?

So if that is not clear you want to change the relatioship
Directives relate to Names by the Name_ID
The Tracker table relates to directives by the Directive_ID. You want the foreign key in the tracker table not in the directive table.
However, like I said withdrawn, withdrawn date, and superceded seem like properties of a directive and therefore no tracker table is needed.

Thanks for the advice!


I thought about it overnight and based on some of your comments, I'm going to combine the 2 directive tables. The original purpose was so that a user must select a directive from the tblDirectives. I realize now having tblDirectives and tblDirectivesTracker serves no purpose when all of the data from these 2 tables can be consolidated into 1.


I'm going to take a shot at pulling those 2 tables together and restructuring so I'm not doubling up on foreign keys.


Also will do some cleanup work on the field names. Thanks for the tips! I really appreciate it.

restructuring so I'm not doubling up on foreign keys

Click to expand...

Even if you did not consolidate (although I see no reason why you should not consolidate), the problem was that the foreign key was in the wrong table. You should have it in the Tracker table not in the directive table. The "parent" would be Directive and the "tracker" would be the "child". You would not enter a record in the tracker before entering a directive, that is why you could not create a record.
Another thing. Do you really have a relationship to the Name, or is the Name just a value. Since there are no other fields in the Name table, why not just store the Name value, instead of the Name ID. You gain nothing by storing an ID except making a lot of work. Store the value "Low Voltage Directive (LVD)" into a text name field instead of the value 3. Now you no longer have to make a query to see "Low Voltage Directive (LVD)". Now there may be a reason that you want a relationship instead. For example you want to add additional fields to that table in the future like a sort field to sort them in some specific business order such as most commonly used. The other would be cascade deletes so that if you deleted a name it would delete child records (would not make sense here).
Also depending on how you do your entry, I do not even think you need the Name table if you do not know the possible names ahead of time. I would think the names are groups that you determine when entering a directive. The name field would be based on a combobox that selects the 'distinct' names from the existing directive table. So I enter the first directive and give it a name. There are zero names in the combobox so I have to type one in. I enter the second directive and there is one name in the combobox, and I can select that or type in a new name. If I type in a new name then on the 3rd Directive to enter there will be two names in the combobox...

A couple more things.
1) Do not use lookups in your table. You can google about this and why it is bad, but it only causes headaches. Build your lookups on your forms and if you have to in a query.
2) Do not put any spaces in a field name. Name_ID, or NameID instead of "Name ID". Spaces cause problems and extra work.
3) Personally if a field is a foreign key or a PK I make that very clear.
Superceded_By_ID, or I like Superceded_By_FK instead of "Superceded_By".
4) I personally like natural keys when appropriate, instead of autonumbers. Autonumbers are always good choice, but if I have Unique, short, single word, no spaces, I would use them. Example would be like airport identifiers (ICAO) which are 3 letter indetifiers. Your Directive appears to be a good identifier. Unique, no spaces, short.
Directives
89/336/EEC
73/23/EEC
72/23/EEC
2004/108/EC
2014/30/EU

I would make your DirectiveS a PK ID. It has the benefit of allowing you to create a relationship, but it is readable and does not reqire an additional query.

So if I make Directives an ID field, and I am only storing a name value my table is much simpler.

Directive_Identifier 'Text 1999/5/EC Primary key
Directive_Name 'Text a value and not a key, unless you plan additional fields in the Name table
Withdrawn 'Boolean
Withdrawn_Effective_Date 'date Time
Superceded_By_Directive_ID 'Text 'this is a foeign key but has the benefit of being readable

Without any lookups

Thanks for the help!


I've got this working well using your advice. I consolidated the tables into 1 which simplified literally everything. This simplified the need for relationships and now there is no more need to use an unbound form to update 2 redundant fields in separate tables. A simple bound form does the trick.


I also renamed many of the fields to remove spaces per your suggestion. Once this is cleaned up I'll share the final version in case anybody is curious.