Combobox in subform depending on combo in main form

[Sean Clayton]

Hi all,

I'd be really appreciative of a little advice, if someone would be
willing.

I'm attempting to design a database for one of our doctors here based
on a screenprint of an Access form he gave me. For the most part, it's
been straightforward, but I'm having trouble with one part, and it's
getting me pretty frustrated.

The form, as I've designed it, consists of 3 nested forms. The Main
form has a patient search combo box which populates a set of text and
date boxes, and also is supposed to populate the fields of the Surgery
subform with the surgeries for that patient. The Surgery subform has
its own combo box, which is supposed to search among the returned
surgery dates and populate the fields based on the date chosen.
Surgery also has its own subform, Procedures, that lists the
procedures performed during a particular surgery.

As I have it working now, I can pick a patient using the Main form
search box just fine, and it populates the relevant information in the
Main form text boxes. It also populates the fields on the Surgery and
Procedures subforms for the first listed surgery date for that
patient. The Surgery subform's combo box lists all the surgery dates
in the underlying table, but will only display the info for the dates
associated with the patient you choose. Choosing any other date does
nothing.

I want to set it up so that for the patient selected in the Main form
combo, it only displays the surgery dates associated with him in the
Surgery subform combo. Can someone give me a hand?

 

[Jeanette Cunningham]

Sean,
the row source for the combo for dates can be a saved query.
You can create the query in the query designer and use criteria to limit the
dates to just the ones matching the selected patient.
In the query select the column for PatientID
In the criteria row for PatientID type the criteria like this:
Forms!MainFormName!PatientIDControlName
Save the query and use it as the row source for the date combo.

Note: replace the name for the main form and the control for the PatientID
with the actual names of your main form and the control.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[Sean Clayton]

Sean,
the row source for the combo for dates can be a saved query.
You can create the query in the query designer and use criteria to limit the
dates to just the ones matching the selected patient.
In the query select the column for PatientID
In the criteria row for PatientID type the criteria like this:
Forms!MainFormName!PatientIDControlName
Save the query and use it as the row source for the date combo.

Note: replace the name for the main form and the control for the PatientID
with the actual names of your main form and the control.

Jeanette Cunningham -- Melbourne Victoria Australia

Thanks for the reply!

When I went back to implement your suggestion, I noticed that the
combo box on the subform only selected the surgery date for finding
records. The query within was:

SELECT Surgery.SurgDate
FROM Surgery;

In order to implement your suggestion I had to change it to this:

SELECT Surgery.PtID, Surgery.SurgDate
FROM Surgery;

...and now it's stopped working properly. When you select the patient
in the main form combo box, it does populate the first listed surgery
date and the associated information with it, but won't allow you to
select any other date, either for the same patient or for another
patient. I suspect it has something to do with there being a non-
unique identifier in the query now, but I can't think of a way around
that.

 

[Jeanette Cunningham]

Sean,
let's get a bit more info about the setup.
Could you confirm the following details:
What is the field that is the master link and child link between the main
form and subform?

Is the combo for selecting the surgery date an unbound combo - does it have
an empty control source?

When you select a patient in the main form, does the combo in the subform
shows all the previous surgery dates for that patient?
You select a date from the combo and the subform shows you the details for
the visit by that patient on the selected date?

When you go back to the combo to select a different date from the data
combo - you try to select a date from the combo but the subform will not
show the details for the newly chosen date? or the combo won't let you
select a date at all?


Jeanette Cunningham -- Melbourne Victoria Australia

 

[Sean Clayton]

Sean,
let's get a bit more info about the setup.
Could you confirm the following details:
What is the field that is the master link and child link between the main
form and subform?

The link is a field called PtID. It's an autonumber in the Patients
table (Main form), and a standard Number field in the Surgery table
(Surgery subform).

Is the combo for selecting the surgery date an unbound combo - does it have
an empty control source?

Yes, it is unbound with no Control Source.

When you select a patient in the main form, does the combo in the subform
shows all the previous surgery dates for that patient?
You select a date from the combo and the subform shows you the details for
the visit by that patient on the selected date?

When you go back to the combo to select a different date from the data
combo - you try to select a date from the combo but the subform will not
show the details for the newly chosen date? or the combo won't let you
select a date at all?

When you open the form, the first patient in the Patients table is
loaded in the form, not just with his Patient table details, but with
the details from his first surgery date in the subform as well. If you
try to choose a different surgery date, the combo will let you see the
surgery dates for just that patient, so that part is working
correctly, but doesn't load the details when you select it. Plus, if
you try to go back to the main form combo and select another patient,
selecting another patient won't load the details in either the main
form or the subform.

 

[Jeanette Cunningham]

Sean,
here is some code from an app that has a main form and a subform with
linking master/child field of TrID
The combo called cboSelTrack has 2 columns, the TrID and the Track name.
Selecting a track in the unbound combo on the main form shows the details of
individual records in the subform for the selected TrID
The code goes on the after update event of cboSelTrack.
The main form has allow filters and edits and deletes all set to true.
I suggest that firstly you get the main form correctly giving you the
matching records in the subform.
When this is working, then work on the combo on the subform.

There is also the possibility that your form is showing the first signs of
corruption.
A way to check this is to create a quick and dirty new form and subform -
you can use the forms wizard to do this quickly.
Avoid any work to pretty up the form - just use the bare minimum fields
controls to check that the form does work.
If the new form works and the current one doesn't - it suggests some
corruption with the current form.


Dim bolNoMatch As Boolean

If Not IsNull(Me.cboSelTrack) Then
' Find the record that matches the control.
With Me.Recordset.Clone
.FindFirst "[TrID] = " & Me.cboSelTrack
If .NoMatch Then
bolNoMatch = True
Else
Me.Bookmark = .Bookmark
End If
If bolNoMatch Then
Me.cboSelTrack = Me.cboSelTrack.ItemData(0)
End If
End With
Me.subform.SetFocus
End If


Jeanette Cunningham -- Melbourne Victoria Australia