Software

Ask something
Question Locked

Access - form to update tables with referential integrity

0 helpful
5 replies

by informationfac

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

How can I create a form that will create a record in several tables that have a many to one relationship with a referential integrity? I am creating a cenus data base. There is a table that has the client id and basic info - with many related tables that are a one to many to the client ID table. This requires that a client id be setup first, then the census data added after. As the cenus data updates as often as every 6 months and they need the census history, this related census tables have the Client ID field as a foreign key with many record entries.

How can I make this happen in a single form when inputing the intial census record?

5 replies

by tony hopkinson

should be a BeforePost event somewhere.

check to see if client id is null,
if so create a client record, get the id and then force it into the census record.

The other way is to break up the UI so first you create a client on one form and then add the census data for that client on the next.

by informationfac

I tried to create an exit event, that would save the initial entry (client id, name,....), saves a another autofield numberfield from the sub client table and then would hold the focus to the client id and place the same client id within the client id field of the census data and allow data entry in that portion of the form. I did this in a macro - all works with the exception of the client ID in the Census form - it still throws the error that a related recored is required in another table

by tony hopkinson

is on the control and fires after loss of focus. Sometimes it doesn't fire at all as it relies on something else getting focus and then 'knowing' what used to. Seen that get very flaky on occasion.
You need to implement before post somehow. It's that method that is returning the error. The most robust solution is to break it up into create / select client and then add census data.

I'm not an access front end expert, may be posting a specific question might get you the answer you need. There are a few here

by johnsnow

edited by moderator

Message was edited by: beth.blakely@...

This discussion is closed to new replies.