PK not auto-adding

[bsnalex]

Ok the long and short is this, my main table is Patient Registration details, which includes a universal PK (field is called RGID) that links to all other tables in my db.

I'm setting up a query that will add appointments to my appointment table. The PK in tblAppointments is an autonumber, but within the db, tblAppointments is joined to tblRegistration via RGID on a one-to-many relationship (one in tblRegistration, many in tblAppointments). Before you ask, referential integrity is enforced. The query will eventually be the datasource to input appointments via a form.

I'm running into a brick wall, because despite the relationship being set up correctly, I find when I run the query and specify "2" as the criteria in tblAppointments.RGID (one i set up earlier) i can alter the existing appointment record, but the RGID that should automatically insert into the field is not and then the record won't exist correctly as there's no RGID.

Can anyone suggest a fix?

 

[spikepl]

This is a lot of complex info expressed in words .

Show screenshots (attach as zip due to your postcount) and explain the issue.

I have a suspicion that you expect Access to fill-in the value of a foreign key for you, just because you selected enforcing Referential Integrity (RI). RI does not DO anything: it just acts to prevent you contravening it, that's all.

If you use a form/subform construct, with Parent/Child fields set in the subform container's properties, then Access inserts the FK for you, so long as the PArent record exists.

On queries alone one can do stuff, provided that the key from the correct side is included in the query. But that requires specifics which is why you should show a screenshot or, much better, attach a db shaved down to the objects required to illustrate the issue, AND - do not forget - instruction what to do to see the problem - we do not read minds.

 

[bsnalex]

If you use a form/subform construct, with Parent/Child fields set in the subform container's properties, then Access inserts the FK for you, so long as the PArent record exists. /QUOTE]

Hi. I had a play around building some forms with Parent/Child link fields set and it's working now. Just needed to identify the field and voila it auto-adds now when I begin a new record.

Thanks very much for the help. If I continue to get stuck though, I'll keep posting.!