Create Primary Key from Foreign Key?

[timmeh01]

Hello all,

I am pretty new to Access. Would appreciate help with this question!

I have two tables tblPatients (pkUMRN) and tblAdmissions (pkAdmissionID, fkUMRNAdmission). pkUMRN is not auto-number, but is an 8-digit alphanumeric code unique to each tblPatients record. I have set up a one-to-many relationship between tblPatients_pkUMRN and tblAdmissions_fkUMRNAdmissions (each patient can have multiple admissions).

Is it possible to allow the entry of a new tblAdmissions_fkUMRNAdmissions to create a new tblPatients_pkUMRN? IE can you create a new primary key from a new foreign key?

Along a similar line, if the primary key already exists, how would you autofill a form with data from tblPatients when the tblAdmissions_fkUMRNAdmissions is entered in to the table?

Many thanks for your help, and sorry if this has already been answered!

Tim

 

[kipcliff]

We should address your second question first, with a mind toward your first question.

Set fkUMRNAdmission in tblAdmissions to lookup pkUMRN in tblPatients. Make sure the bound column is pkUMRN, and set Limit to List = Yes.

Create an autolookup query by using fkUMRNAdmission from tblAdmissions, any desired fields from tblPatients, and the other desired fields from tblAdmissions.

Create a form from this query, and bind the fkUMRNAdmission field to a combo box.

When you enter a patient ID, Access will fill the tblPatient fields with the matching data.

To do what your first question asks would violate referential integrity; however, you can place code in the NotInList event from the patient ID box on your admissions form and use it to open a New Patient form.

Then you can add the new patient data, save it, and resume filling out the admission.

 

[timmeh01]

Thanks this was all awesome!

I even managed to figure out some macros to open a NewPatient form, save the data and close the form.

I still have one problem remaining: I can't figure out how to get the UMRNAdmission combo box entry requery after the NewPatient form closes. The tblPatients updates, but the option for the new UMRN doesn't exist in the combo box unless the form is closed and reopened.

I've tried a requery macro for form On Got Focus
I've tried a requery macro for combobox After Update.

Both with and without UMRNAdmission as the Contol name.

I'll keep hammering away at it... any further advice?

 

[kipcliff]

Ah. My apologies for providing an incomplete solution. I have never created macros, and the only thing I have done with the ones I found was to convert them to VBA.

If the NotInList event is handled in code, there are two variables that go with it:

NewData, which is the input that triggered the event, can be forwarded to the NewPatient form via the OpenArgs part of the Open command used to call the form.

Response, which tells Access how the event was handled. When the event is triggered, you provide a message that asks the user whether to save the new data. If the answer is 'No', you set the Response to acDataErrContinue. If the answer is 'Yes', you set the Response to acDataErrAdded.

This latter value is what tells Access to requery the combo box, thereby refreshing the combo box list.

I hope this information helps you finish the job.

 

[kipcliff]

Hi everyone, I am new for this forum.

Erm, wrong place for the introduction, but welcome.

 

[timmeh01]

Thanks or the further guidance, Kipcliff.

This helped a lot. Project sorted

 

[kipcliff]

Excellent. I am happy to hear that.