Extracting Foreign Keys

[QuantifyRisk]

I have a form that inputs Agency Information, their Producer Information, and their States Licenses.
The initial form is Agency Info, then you can save it and add a Producer (there may be more than one producer for any agency). They are linked by a foreign key. On the Producer Info form, you can add their State Licenses (there may be more than one state license for each producer, all unique). This is also joined by a foreign key. (Original post: https://www.accessforums.net/access/...nfo-45392.html)

My goal is to get the ProducerInfo table to also hold a field for the AgencyNumber which their is already a relationship with (not a junction table, even though the pic on page 2 of my original post shows that it has a junction. I removed that). I thought it would automatically fill in the box, but it doesn't. How can I get this info in there? I was thinking DLookup but wasn't 100% sure that's what I wanted.

[ItsMe]

I use DAO or SQL to Update, Append, and Edit records.

An easy way to start may be to create an UPDATE query using the Query Builder. Use literal text for the criteria and UPDATE clause. Afterwards, you can decide if you want to bring the resulting SQL into VBA or call the Query Object by name.

[QuantifyRisk]

There's no way to edit info on the users end within Access?

[QuantifyRisk]

Follow-up Q: How do I link one form to another via foreign key?

[ItsMe]

I do not understand the questions in posts 3 and 4. Perhaps you should consider the fact that data is stored in tables, not forms. Queries can retrieve data and display said data in a form via the form's Recordset. Any updates, appends, and record deletions directly affects the table. You can Re-Query the table/data after updates, appends, and record deletions and, once again, display the retrieved data in a form.

JOINS and Relationships are created at the table layer.

[June7]

Form/subform arrangement will synchronize records of related tables. The subform container control has Master/Child Links properties. Set them to the PK/FK fields. The master PK will automatically save into the subform record as FK when any data is entered into subform record.

[QuantifyRisk]

Form/subform arrangement will synchronize records of related tables. The subform container control has Master/Child Links properties. Set them to the PK/FK fields. The master PK will automatically save into the subform record as FK when any data is entered into subform record.

Well that work nicely. Not exactly what I wanted in terms of layout but oh well. It's not going int for a beauty contest.

Since my 3 forms are now merged together (Parent-Child-Child of Child), I need to find a way to save Agency once, Producer as many times as needed and License as many times as needed... I suppose I can do a save button that only saves all the data everytime but checks for duplicates so there's only one agency and no duplicates. I suppose I could do a button that creates additional fields so multiple producers can be entered but that sounds too complicated for what I need to do.

[June7]

Multiples fields for same data element is poor design.