Unanswered: Cascading combo boxes to control subform
Pardon me if this is clear as mud; I apologize.
I am creating a form that will help me separate out specific patients and visit dates from a table with about 9K records. I was able to create cascading combo boxes in a form. The first combo box lets me select the patient. The second combo box lets me select a specific date of their visit.
What I would like to do is base a subform on the selection in the second combo box. Now, the table that the second combo box pulls from does not have a direct relationship with the table I would like to make the subform from; In otherwords, while they do have fiels that they "share", neither is a primary key so I can't create a one-to-many relationship between them. But both are successfully linked to the table that feeds the first combo box.
My goal is to make the second combo box filter the subform data by name and date. I have been working on this for sometime and did, at one point, have something working, although I wasn't using the cascading boxes. But then I had to make an adjustment and it all went to pot. But I like using the cascading combo boxes and would like to continue to do so, so if anyone has any idea what the heck I can do to get this working, I would so love to hear it.
I've been using access for a long time but don't know coding very well, so if you do replay, may have to "dumb" it down for me.
cascading combo boxes to control subform -- more info...
Okay, let me try this:
CB1, Patients, has a drop down list of patient names: Patient A, Patient B, Patient C. The key in this table is patient ID.
If I select Patient A in CB1, then CB2, Patient Visit Dates (the key being patient dates ID), will provide me with a list of when they saw the doctor. It is based on a query of two fields, Patient Name and Patient Visit dates. So the data in CB2 would look like this:
Patient A|Visit date 1; Patient A|Visit date 2, etc.
I would like the subform, Patient Data, to filter based on the selection in CB2. I am thinking that because both CB's are unbound, that might be why the subform is not relating to either of the boxes. The Patient Data table has a relationship key of Patient Data, and foreign keys are dates of visit and patient ID.
Basically, all I need is Patient Visit dates to control the subform Patient Data. But I am stumped as to how to make that happen, or to possibly change CB2 to bound without disrupting how it works with CB1. This is the coding I used to connect the two boxes, btw:
Private Sub PTNameCB_AfterUpdate()
Me.DatesCB = Null
Me.DatesCB = Me.DatesCB.ItemData(0)
Here is the way I would approach this. Create 2 fields, a Start Date and a End Date and place them on your Main form. (They can be Unbound)If it is not already, use a qry as the record source for the Main form and under a date field (DatesCB ?) use this criteria.
Between [Forms]![frmYourMainForm]![BeginDate] And [Forms]![frmYourMainForm]![EndDate]
Then simply requery your form data.