Autopopulate a form or use subform?

[vircalendar via AccessMonster.com]

This may be basic, but I've searched AccessMonster for answers and I can't
find the right one.

I have a patient scheduling database that stores patient identification in
one table and information about their procedures in another table. The two
are linked by a field called MRN (medical record number) with a one to many
relationship.

What want to do is pass the MRN to a new form (called requisition) that will
be used for scheduling a new procedure. The form would autopopulate patient
ID info (name, address, allergies, etc) and then have a bunch of blank fields
for the procedure being requested. This seemed to me like a perfect
situation for a subform (the patient data being on the main form and the
procedure data being on the subform), and it worked well that way.
Unfortunately, I ran into the problem of being unable to use the me.undo
feature to reverse changes in both main and subforms (this is covered in
great detail in other posts). Why would I need to do this? Well, the operator
may need to modify medication or allergy data stored on the main form at the
time of scheduling a procedure. I can't have the changes to either the main
or subform updated until the whole thing is finished.

So then I thought I would try making a single form using a query of both
tables as the source. The problem here is that I would have about 30 fields
that need to be populated from the patient data side. I found some ideas
about how to do this, but none that my little mind could apply. One of
these was presented, without any explanation, as:

With Me.RecordsetClone
.FidFirst "nameID=""" & Me.NameID & """"
If .NoMatch Then
' avoid exiting the scope while a With is in effect
Else

Me.Address = .Fields(Address)
Me.City = .Fields(City)

End If
End With

I don't know where to put this or how to use it. Would it be OnOpen of the
new form? If so, would the MRN be passed as an opening argument? How would
this work if the patient has never been seen before and his/her MRN is not on
file? Is there another technique that fit my situation better?

Thanks!

 

[Tim Ferguson]

What want to do is pass the MRN to a new form (called requisition)
that will be used for scheduling a new procedure. The form would
autopopulate patient ID info (name, address, allergies, etc) and then
have a bunch of blank fields for the procedure being requested.

You have a number of different ways to do this.

(a) You can base the form on a query that is a join between the two
tables; you should include the Procedures.MRN field in the query (which
will allow you to update the details of the procedure in question) but
_not_ the Patients.MRN field (which will make the patient fields
effectively read only).

(b) Alternatively, you could base the table on the Procedures table, and
have a bunch of locked and disabled text fields with their controlsources
set to DLookup() functions retrieving the appropriate values from the
Patients table. This is not very efficient, though

(c) Better than that would be a similar set up to (b) above but instead
capture the OnCurrent event and use it to open a recordset to retrieve
the one record from the Patients table and populate the text boxes like
that. It's roughly the same thing but accomplished using one database
read rather than one for each field.


Hope that helps


Tim F

 

[vircalendar via AccessMonster.com]

Option c is kind of what I had in mind. As I understand it, I would open a
new, blank form that would include all of the fields I want, then use the
recordset to populate those fields that come from the patients info table.
Right?

If that's what you're suggesting, can you maybe clarify for me how to
"capture the OnCurrent event and use it to open a recordset to retrieve the
one record from the Patients table and populate the text boxes like that?"
What would the oncurent code look like?

 

[vircalendar via AccessMonster.com]

Also, would this use a query of both tables as the data source or just the
data from the procedures table. If it's just the procedure table, how would
changes in the patient data fields be saved?

Sorry to ask so many questions, but this has been driving me nuts for a
couple of days.

 

[Tim Ferguson]

If that's what you're suggesting, can you maybe clarify for me how to
"capture the OnCurrent event and use it to open a recordset to
retrieve the one record from the Patients table and populate the text
boxes like that?" What would the oncurent code look like?

I don't really know enough about the process you are modelling. At a
guess, it might be something like this:

select a Patient record
check/ update the details against the person's current situation
select a new procedure to be logged against that person
Add the details of the particular Procedure

In that case you need:
1: a form that will search the Patients table
2: a form that will present the Patients fields and allow updating
3: a form that will allow the user to create a new Procedure record
and link it to the Patient in form (2) and select which procedure
the patient is to undergo.

None of this is neccessarily very hard:
(1) would be an unbound form with a list box or perhaps some text
so the user can find the correct patient. It would have an OK button
that opens form(2) for the chosen patient.

(2) is simply a form with patient details, bound to a recordset like
"SELECT * FROM Patients WHERE RecordNumber=29777". This recordset can
be created by the first form if you like.

(3) is a form bound to the Procedures table. It would have some kind
list box to choose the procedure, some text boxes for AdmissionDate or
checkboxes for Survived and so on. You can program something like this:

private sub Form_Current()
dim recordNumberTemp as long
dim rs as recordset

If IsNull(me!txtRecordNumber.Value) then
' it's a new record, retrieve the record number from
' the other form

' need loads of error trapping here...
recordNumberTemp = forms!PatientForm!RecordNumber.Value

Else
' use the current one
recordNumberTemp = me!txtRecordNumber

End If

set rs = curretndb().OPenRecordset( _
"SELECT * FROM patients " & _
"WHERE RecordNumber = " & recordNumberTemp, _
dbOpenSnapshot, dbForwardOnly)

' now set the text fields
me!txtRecordNumber = recordNumberTemp
me!txtFullName = rs!FirstName & " " & rs!LastName
me!txtAge = CalculateAge(rs!DateOfBirth)
' and so on

' clear up afterwards
rs.Close

End Sub


Hope that helps


Tim F

 

[Tim Ferguson]

Also, would this use a query of both tables as the data source or just
the data from the procedures table. If it's just the procedure table,
how would changes in the patient data fields be saved?

Ahem... it retreives data from both tables. The patient data live in the
Patients table, and is only changed and saved as part of the Update
Patient Info process, which probably has its own form. The Procedures
table only contains data about procedures (ProcedureType, DatePeformed,
DidPatientSurvive, LitresOfBloodTransfused and so on) and not about the
patient -- except of course, the MRN so that you can tell which patient
underwent the thing

Hope that helps


Tim F

 

[vircalendar via AccessMonster.com]

Thanks. I'll give this a try

I don't really know enough about the process you are modelling. At a
guess, it might be something like this:

select a Patient record
check/ update the details against the person's current situation
select a new procedure to be logged against that person
Add the details of the particular Procedure

In that case you need:
1: a form that will search the Patients table
2: a form that will present the Patients fields and allow updating
3: a form that will allow the user to create a new Procedure record
and link it to the Patient in form (2) and select which procedure
the patient is to undergo.

None of this is neccessarily very hard:
(1) would be an unbound form with a list box or perhaps some text
so the user can find the correct patient. It would have an OK button
that opens form(2) for the chosen patient.

(2) is simply a form with patient details, bound to a recordset like
"SELECT * FROM Patients WHERE RecordNumber=29777". This recordset can
be created by the first form if you like.

(3) is a form bound to the Procedures table. It would have some kind
list box to choose the procedure, some text boxes for AdmissionDate or
checkboxes for Survived and so on. You can program something like this:

private sub Form_Current()
dim recordNumberTemp as long
dim rs as recordset

If IsNull(me!txtRecordNumber.Value) then
' it's a new record, retrieve the record number from
' the other form

' need loads of error trapping here...
recordNumberTemp = forms!PatientForm!RecordNumber.Value

Else
' use the current one
recordNumberTemp = me!txtRecordNumber

End If

set rs = curretndb().OPenRecordset( _
"SELECT * FROM patients " & _
"WHERE RecordNumber = " & recordNumberTemp, _
dbOpenSnapshot, dbForwardOnly)

' now set the text fields
me!txtRecordNumber = recordNumberTemp
me!txtFullName = rs!FirstName & " " & rs!LastName
me!txtAge = CalculateAge(rs!DateOfBirth)
' and so on

' clear up afterwards
rs.Close

End Sub

Hope that helps

Tim F