Choosing different children per parent when adding a new sales record

[quovadio]

I have a database that is tracking stays in medical lodging. We enter the stays based on the patient receiving medical care. I have a patients table with a parent/child relationship to a guests table. When a patient's family returns, they don't necessarily come with the same guests. For example: Joe is in the hospital, and his kids, Sally and Jeff stay with us. They check out, and a few weeks later Joe is readmitted, but only sally comes with him this time. How can I enter the stay so that only the current guests are reflected without creating new guest records every time.

Is there anyway to have to dropdown lists where the choices available in the second dropdown are contingent on what is chosen in the first?

[Stewart Ross]

Hi. We have an insights article on Cascading Combo/List Boxes which takes you through the technical steps involved in setting up a second combo or list box whose contents depend on the selection made in the first box.

The article linked above is a useful introduction to the topic. I assume from what you say about having a parent/child structure defined that all guests are associated with particular patients in such a way that for any patient you will be able to select the guests correctly in all circumstances based on a unique ID for that patient and not something non-unique such as the patient's name.

-Stewart

[quovadio]

Two things:

1. The article seemed to help, when I choose a patient, the list box for guests is populated with only those guests related to the patient. But a strange thing happens, I choose a patient for stay 1 and then move on to stay 2, choose a different patient, everything is fine until I go back to the record for stay 1 and the guests from stay 2 are listed on that record even though the original patient from stay 1 remains.

2. I still don't understand how to choose multiple guests on one stay and then a different set of guests the next time the patient and their family stay with us.

[Stewart Ross]

re 1: this suggests that there is a problem with either the binding of the guests to particular stays or the way you have structured your patient-child relationships. I wonder if you are using unbound text boxes on your form? Anyway, without more information it is impossible for me to guess at this one.

Re 2. ...this is what I was referring to in making the assumption that you would be able to select all the guests in all circumstances.

In fact, to do this you need to query all of the stays for that patient to generate an all-guest list that lists all possible guests known for that specific patient. Of course there will be persons staying who are NOT on the all-guest list for that patient at present, which you must already be dealing with as new entries at some point.

-Stewart

[quovadio]

How can I tell whether I am using unbound text boxes? I am using whatever access automatically use when you quickly generate a form from a table. The parent child relationship is set so that the patient table is the parent and the primary key of the patient table (PatientID) is also included on each record in the guest table, so that each guest (who has their own autonumber ID ) is associated with a PatientID

[Stewart Ross]

In your case, as you have created new combos you should look to see if their controlsource properties are blank. If they are then these are unbound combos - they do not link or bind to fields in your underlying records, so they will not change with, store, or be modified in sync with the records in the table.

-Stewart