Question re: 1 Form, 2 Tables

[wilderfan]

It's been a few months since I've used ACCESS and I've forgotten how to do something.

I have 2 tables:

tblClientInfo

ClientID (autonumber) (PK)
StreetAddress
City
StateProv
PostalCode
​

tblClientName

ClientNameID (autonumber) (PK)
ClientID
ClientName
EffectiveDate
​

There is a 1 to many relationship between tblClientInfo and tblClientName, allowing for the database to track client name changes which may occur over time.

I want to create a Form to "Enter New Client Info" with the fields:

ClientName
StreetAddress
City
StateProv
PostalCode
​

I don't really want to see sub-forms appearing on the form.

Problem: When I attempt to enter the ClientName (the first textbox), I get an error message: cannot add record(s); join key of table 'tblClientName' not in recordset.

Any suggestions?

Thx.

​

 

[boblarson]

You need a subform for this.

But you can format it so it doesn't look like there is a subform there.

 

[wilderfan]

Do I need to bring the autonumber fields into the Detail section of the form?

I don't really want them to be visible.

 

[boblarson]

Do I need to bring the autonumber fields into the Detail section of the form?

I don't really want them to be visible.

Nope, you should be fine just having them in the underlying recordsources.

 

[wilderfan]

Mmm ...

There must be another issue.

I've attached a zip copy. If you can see what I've done wrong, then I'd appreciate getting your feedback. Thanks, Bob.

 

[boblarson]

The problem lies in that you are trying to put two tables (one-to-many) on a single form without using a SUBFORM. I told you that already.

Also, you had it the wrong way around. The ClientNameID should be in the Info table as a FK and not the other way around. I made a few modifications to your table structure and also added your subform in there. You will want to have your own custom buttons to change the address (so you can retain history) and you'll want to change the sort on the subform to be by Effective Date DESCENDING so the latest address shows on the form.

See if you have questions about this or if this helps.