populating a combobox

[jshooty]

On my main form, I have a number of comboboxes that I use to list patient diagnoses. These diagnoses are pulled from a table that lists the diagnoses with an autonumber primary key. On a subform where I am inputting data about a patient's death, I want to populate a "cause of death" combobox with the diagnoses that I earlier entered using the diagnoses comboboxes. (I can list up to seven diagnoses per patient.) The closest I've been able to come winds up pulling the autonumber into the combobox list, when what I want are the diagnoses themselves. I figure there has to be a way to either send the diagnoses forward to the cause of death combobox as I select them on the main form, or to query backwards to the diagnoses to populate the cause of death combobox.

Kind of morbid, I know--but can anyone help anyway? Thanks.

 

[neileg]

On my main form, I have a number of comboboxes that I use to list patient diagnoses. These diagnoses are pulled from a table that lists the diagnoses with an autonumber primary key. On a subform where I am inputting data about a patient's death, I want to populate a "cause of death" combobox with the diagnoses that I earlier entered using the diagnoses comboboxes. (I can list up to seven diagnoses per patient.)

That suggests you have an un-normailised structure. You should have the diagnoses in a related table which would give you an unlimited number.

The closest I've been able to come winds up pulling the autonumber into the combobox list, when what I want are the diagnoses themselves. I figure there has to be a way to either send the diagnoses forward to the cause of death combobox as I select them on the main form, or to query backwards to the diagnoses to populate the cause of death combobox.

Kind of morbid, I know--but can anyone help anyway? Thanks.

You need to join the table that holds the diagnoses into the query that the combo box is based on. Then you can show the text instead of the ID.

 

[dayna]

I’m no Access expert, but I might be able to take a stab at your questions.

Neileg could very well be right. It sounds like you’ve got many patients, and each patient can be diagnosed with more than one illness. Conversely, each illness can correspond to more than one patient. Normalized databases don’t recognize many-to-many relationships; however, this problem is easily solved with a junction table, and perhaps you’ve already taken care of that. Assuming you have, let’s consider your first question.

If you’ve got a combo box that functions perfectly well except for the fact that it displays the autonumber IDs rather than the text fields associated with those IDs, here’s what you can do:

1. Set the combo box’s Column Count property to 2 instead of 1. (I am assuming here that your Diagnoses table has two fields: [ID] and [Diagnosis]. If, for example, the text you want to display is in the third field, you would change Column Count to 3.)

2. Change the Column Widths propery to 0”;1” – and here again, I’m assuming that your Diagnoses table consists of two fields. What you’re actually telling Access here is: make my first field (ID) invisible, and display my second field (text) in a drop-down list that is 1” wide. So, since some of your diagnoses may be long text fields, you may want to set your second column width to 2”, or perhaps even longer.

With regard to your second question, the first step would be to make sure that your database’s structure is normalized. If you’ve got a junction table, you can base a query on it, and then use that query as the row source for your combo box. Though I must admit that I’m a little confused as to why you would want to limit your Cause of Death field to past diagnoses.

As long as we’re being morbid, consider this example: Over the course of one year, let’s say you diagnose Patient A with three ailments: diabetes, Alzheimer’s, and tapeworm. Suppose Patient A later dies, not from any of the aforementioned conditions, but from severe head trauma imposed by a disgruntled spouse. Maybe the cause of death was undiagnosed syphilis, drug overdose, or a boating accident.

It seems to me that not only would you want to avoid limiting the possible causes of death to your previous diagnoses, you may also want to include even more possibilities than what is listed in your Diagnoses table. But then again, I don’t really know what you’re working on and what your logic may be. Like I said, I’m a bit of a noob myself, so I’ll shut up now. Good luck to you…

 

[neileg]

Normalized databases don’t recognize many-to-many relationships; however, this problem is easily solved with a junction table, and perhaps you’ve already taken care of that.

Not sure where you got this idea from. Relationships exist in the real world and are simply modelled in a database. If a many to many relationship exists, then it needs to be there in your database. Now it's true that Access can't handle a many to many without the use of a junction table, but that's not an issue of normalisation.

 

[jshooty]

Neil--Thanks for the input. I do have the diagnoses in a related table--the limit to seven diagnoses referred to the fact that I put seven comboboxes on the form into which the diagnoses from the related table are pulled. (I could add more--but that should hopefully be enough.) I am still somehow incorrectly writing the query, though, because the combobox for cause of death remains unpopulated. i thought maybe the problem was because I was trying to pull something off of the main form onto a subform, so I set it up using a tab instead--but I still can't get the combobox populated based on the picks made in the seven diagnoses comboboxes. Can you offer any other tips? Thanks.

 

[jshooty]

Dayna--I enjoyed your example, thanks for "out-morbidding" me. My database is designed to link the patient's current admission (with the current admission diagnoses) to the mortality event using a tab page. So even if a patient develops a new problem in the hospital from which they eventually succumb, I would still want that listed as a diagnosis on the main part of the form--and thus it would become one of the choices in the "cause of death" combobox--if I can figure out how to do that. Hope that makes sense.

 

[dayna]

Alas, this isn’t the first time Wikipedia has proven me wrong. (Damn you, Wikipedia!) It turns out the Neil is right and that I had a very foggy understanding of the difference between “normalized” and “relational.” I guess I’m too dumb for my Dummies book.

On that note, I probably shouldn't offer any further advice. As a master of the obvious, the only thing that comes to mind is that you may want to set the Limit to List property of your Cause of Death combo to No so that it will allow users to type in a value that doesn’t already appear on the list. As far as adding that new value to the Diagnoses lookup table, I’m stumped --but call me if you need someone to help you brainstorm possible causes of death!

 

[neileg]

Neil--Thanks for the input. I do have the diagnoses in a related table--the limit to seven diagnoses referred to the fact that I put seven comboboxes on the form into which the diagnoses from the related table are pulled. (I could add more--but that should hopefully be enough.) I am still somehow incorrectly writing the query, though, because the combobox for cause of death remains unpopulated. i thought maybe the problem was because I was trying to pull something off of the main form onto a subform, so I set it up using a tab instead--but I still can't get the combobox populated based on the picks made in the seven diagnoses comboboxes. Can you offer any other tips? Thanks.

You have misunderstood my point. I understand that you have a table that holds your standard diagnoses. What you should not have is seven fields in your patient table holding these diagnoses. You need a separate table that holds the patient ID and the diagnosis ID for each diagnosis. You have a many to many relationship between patients and diagnoses and in Access this needs to be handled using a junction table. Try a search in these forums on Junction tables and many to many.

 

[jshooty]

Neil--Good point, I see how that is going to get ugly. Going to use your suggestion to simplify the relationships. Thanks.