Passing information from one form to another

[John S. Ford, MD]

I have a table, tblAdmissions for which each record corresponds to a single
hospital admission. It has primary key AdmissionIDNum and fields such as
foreign key PatientIDNum as well as DateOfAdmission, DateOfDischarge, etc.
Each admission must have exactly one patient.

A second table is tblPatients with primary key PatientIDNum and fields such
as PatientNameFirst, PatientNameLast, etc. Each patient can have zero or
several admissions.

I then joined these tables to form qryAdmissions_Patients (using
PatientIDNum as the join field) in such a way that ALL records in
tblAdmissions are shown but only records in tblPatients where PatientIDNum
matches an Admission record--in other words ONLY patients who've been
admitted at least once.

I then created a form, frmAdmissionDataEntryForm using
qryAdmissions_Patients as its datasource. It allows users to use controls
to enter data on admissions each of which has a unique patient.

I have another form, frmPatientSearch using tblPatients as its datasource.
It allows users to search for a specific patient. What I want is to be able
to pass the searched for patient's PatientIDNum to frmAdmissionDataEntry
(possibly with the OpenArgs property). My user should then be able to enter
a brand new admission with that patient's information already populated on
the form.

How can I do this?

Thanks in advance!
John

 

[fredg]

I have a table, tblAdmissions for which each record corresponds to a single
hospital admission. It has primary key AdmissionIDNum and fields such as
foreign key PatientIDNum as well as DateOfAdmission, DateOfDischarge, etc.
Each admission must have exactly one patient.

A second table is tblPatients with primary key PatientIDNum and fields such
as PatientNameFirst, PatientNameLast, etc. Each patient can have zero or
several admissions.

I then joined these tables to form qryAdmissions_Patients (using
PatientIDNum as the join field) in such a way that ALL records in
tblAdmissions are shown but only records in tblPatients where PatientIDNum
matches an Admission record--in other words ONLY patients who've been
admitted at least once.

I then created a form, frmAdmissionDataEntryForm using
qryAdmissions_Patients as its datasource. It allows users to use controls
to enter data on admissions each of which has a unique patient.

I have another form, frmPatientSearch using tblPatients as its datasource.
It allows users to search for a specific patient. What I want is to be able
to pass the searched for patient's PatientIDNum to frmAdmissionDataEntry
(possibly with the OpenArgs property). My user should then be able to enter
a brand new admission with that patient's information already populated on
the form.

How can I do this?

Thanks in advance!
John

To pass the [PatientIDNum] to the new form:

DoCmd.OpenForm "FormName", , , , , acDialog, Me.Controlname

Note: ControlName is the name of the control on the search form which
displays the searched for PatientIDNum.
Note also that the above opens the form in Dialog. You must enter the
new data on the form and then close the form before processing on the
first form will continue. If that is not what you wish to do, then
simply omit acDialog but retain the comma (there should be a total of
6 commas).

To then read the passed PatientIDNum in the second form, code the
form's Load event:

If Not IsNull(Me.OpenArgs) Then
Me.PatiendIDNum = Me.OpenArgs
End If

To display the patient's name, address, etc., which should be stored
in the Patient's Table, you can use DLookUp in unbound text controls.

=DLookUp("[PatientName]","PatientTable","[PatientIDNum] = " &
Me.PatientIDNum)

in the Patient's Table, you can use DLookUp in unbound text controls.
=DLookUp("[PatientAddress]","PatientTable","[PatientIDNum] = " &
Me.PatientIDNum)

etc. for Phone and whatever other data you wish to display.
Note: this patient information is only displayed on this new
admittance form. It does not become part of the new admittance record.
If the patient information needs to be changed, i.e. a new Phone, new
Address, etc., you will need to change it via a form bound to the
Patient Table.

 

[John S. Ford, MD]

Dear Fred,

If I understand your code, you're populating the "patient"-oriented
textboxes in my admission entry form with the DLookUp function. Right now
though, I have those textboxes BOUND to the appropriate fields of my
underlying query (qryAdmissions_Patients) which was made by joining
tblAdmissions and tblPatients.

Is there a way of maintaining this data and form structure and still getting
the same results?

John

I have a table, tblAdmissions for which each record corresponds to a
single
hospital admission. It has primary key AdmissionIDNum and fields such as
foreign key PatientIDNum as well as DateOfAdmission, DateOfDischarge,
etc.
Each admission must have exactly one patient.

A second table is tblPatients with primary key PatientIDNum and fields
such
as PatientNameFirst, PatientNameLast, etc. Each patient can have zero or
several admissions.

I then joined these tables to form qryAdmissions_Patients (using
PatientIDNum as the join field) in such a way that ALL records in
tblAdmissions are shown but only records in tblPatients where
PatientIDNum
matches an Admission record--in other words ONLY patients who've been
admitted at least once.

I then created a form, frmAdmissionDataEntryForm using
qryAdmissions_Patients as its datasource. It allows users to use
controls
to enter data on admissions each of which has a unique patient.

I have another form, frmPatientSearch using tblPatients as its
datasource.
It allows users to search for a specific patient. What I want is to be
able
to pass the searched for patient's PatientIDNum to frmAdmissionDataEntry
(possibly with the OpenArgs property). My user should then be able to
enter
a brand new admission with that patient's information already populated
on
the form.

How can I do this?

Thanks in advance!
John

To pass the [PatientIDNum] to the new form:

DoCmd.OpenForm "FormName", , , , , acDialog, Me.Controlname

Note: ControlName is the name of the control on the search form which
displays the searched for PatientIDNum.
Note also that the above opens the form in Dialog. You must enter the
new data on the form and then close the form before processing on the
first form will continue. If that is not what you wish to do, then
simply omit acDialog but retain the comma (there should be a total of
6 commas).

To then read the passed PatientIDNum in the second form, code the
form's Load event:

If Not IsNull(Me.OpenArgs) Then
Me.PatiendIDNum = Me.OpenArgs
End If

To display the patient's name, address, etc., which should be stored
in the Patient's Table, you can use DLookUp in unbound text controls.

=DLookUp("[PatientName]","PatientTable","[PatientIDNum] = " &
Me.PatientIDNum)

in the Patient's Table, you can use DLookUp in unbound text controls.
=DLookUp("[PatientAddress]","PatientTable","[PatientIDNum] = " &
Me.PatientIDNum)

etc. for Phone and whatever other data you wish to display.
Note: this patient information is only displayed on this new
admittance form. It does not become part of the new admittance record.
If the patient information needs to be changed, i.e. a new Phone, new
Address, etc., you will need to change it via a form bound to the
Patient Table.

 

[fredg]

Dear Fred,

If I understand your code, you're populating the "patient"-oriented
textboxes in my admission entry form with the DLookUp function. Right now
though, I have those textboxes BOUND to the appropriate fields of my
underlying query (qryAdmissions_Patients) which was made by joining
tblAdmissions and tblPatients.

Is there a way of maintaining this data and form structure and still getting
the same results?

John

I have a table, tblAdmissions for which each record corresponds to a
single
hospital admission. It has primary key AdmissionIDNum and fields such as
foreign key PatientIDNum as well as DateOfAdmission, DateOfDischarge,
etc.
Each admission must have exactly one patient.

A second table is tblPatients with primary key PatientIDNum and fields
such
as PatientNameFirst, PatientNameLast, etc. Each patient can have zero or
several admissions.

I then joined these tables to form qryAdmissions_Patients (using
PatientIDNum as the join field) in such a way that ALL records in
tblAdmissions are shown but only records in tblPatients where
PatientIDNum
matches an Admission record--in other words ONLY patients who've been
admitted at least once.

I then created a form, frmAdmissionDataEntryForm using
qryAdmissions_Patients as its datasource. It allows users to use
controls
to enter data on admissions each of which has a unique patient.

I have another form, frmPatientSearch using tblPatients as its
datasource.
It allows users to search for a specific patient. What I want is to be
able
to pass the searched for patient's PatientIDNum to frmAdmissionDataEntry
(possibly with the OpenArgs property). My user should then be able to
enter
a brand new admission with that patient's information already populated
on
the form.

How can I do this?

Thanks in advance!
John

To pass the [PatientIDNum] to the new form:

DoCmd.OpenForm "FormName", , , , , acDialog, Me.Controlname

Note: ControlName is the name of the control on the search form which
displays the searched for PatientIDNum.
Note also that the above opens the form in Dialog. You must enter the
new data on the form and then close the form before processing on the
first form will continue. If that is not what you wish to do, then
simply omit acDialog but retain the comma (there should be a total of
6 commas).

To then read the passed PatientIDNum in the second form, code the
form's Load event:

If Not IsNull(Me.OpenArgs) Then
Me.PatiendIDNum = Me.OpenArgs
End If

To display the patient's name, address, etc., which should be stored
in the Patient's Table, you can use DLookUp in unbound text controls.

=DLookUp("[PatientName]","PatientTable","[PatientIDNum] = " &
Me.PatientIDNum)

in the Patient's Table, you can use DLookUp in unbound text controls.
=DLookUp("[PatientAddress]","PatientTable","[PatientIDNum] = " &
Me.PatientIDNum)

etc. for Phone and whatever other data you wish to display.
Note: this patient information is only displayed on this new
admittance form. It does not become part of the new admittance record.
If the patient information needs to be changed, i.e. a new Phone, new
Address, etc., you will need to change it via a form bound to the
Patient Table.

As long as the patient's personal information is already stored in the
Patient Table, you don't really even need to see it on the new
Admission form. The PatientIDNum is all you need to store in the new
admission record. If you are storing the existing Patient Name and
Address, etc. in the new admission record you are creating a
spreadsheet type of database, not a relational one, which Access is.

If you want to have the existing Patient Table information available
for editing while the user enters the new admission record, I believe,
as long as you are using an updateable query (some query's are not
updateable) as the form's record source, you would be able to do so.
Still, I think I would make it a bit harder for the user to edit the
personal information by using a separate form. It's your call.

 

[John S. Ford, MD]

Dear Fred,

You make good points. I agree with you. I DON'T want users to be able to
edit "patient" data from the admission entry form. You're also right that I
should demand that they do it ONLY from a separate patient information entry
form.

That being the case, I don't need to bind those patient information controls
to the underlying query. In fact I'll probably just SELECT the PatientIDNum
in that query and use DLookUp the way you described only so that users can
be provided with "feedback" as to who they're actually admitting.

Now I've got to change some things around!

John

 

[jamaul]

smsir