Hi, I have another problem. I have two synchronized combo boxes on one of my forms. For some reason, when I choose items from these boxes, all the other records on this form display the same items in their boxes. And if I change another record, it goes and changes all the records again. How do I get this field to be different for every record. I don't understand why this has happened when all my other fields work fine. I hope this makes sense, if you have any ideas please reply.
Help me again, please!
- Thread starter newberc
- Start date
Okay, I'll try to explain better. I have created a form with two combo boxes on it. These combo boxes are used to define a disgnoisis for a patient record. But, what is happening is when I choose a diagnosis for one patient record, when I go to the next record I find it has chosen the same diagnosis. This is now true for all the records. I don't understand why this has happened because all my other text and combo boxes do not do this. Is that clearer?
oh, I wish I could show you. each combo box gets its data from a query which queries two tables. The first combo box queries a main disease diagnosis list. Based on what you choose in this box, a query runs and displays in the second combo box the subdiseases. So, I start a new record at the top I fill in their last name, first name, etc. Then I fill in their diagnosis with the two combo boxes. Next I want to enter in another patient so I move to the next empty form. The problem is the Diagnosis on what is supposed to be a blank form is already filled in. If I change this information it will change the previous record. The diagnoisis is different for every patient, but right now it is the same. How do I get it to stop doing this?
Robert Dunstan
Mr Data
- Local time
- Today, 22:07
- Joined
- Jun 22, 2000
- Messages
- 291
The way I understand your problem is that in your first combo box you want to select the main category of disease and then based on this selection show the relevant sub diseases in the second combo box. I am assuming you have two tables 1 for the main disease and 1 for the subdisease linked by an ID field with a one to many relationship.
Try creating two unbound combo box controls and say call them comMainDisease and comSubdisease. In comMainDisease's RowSource property use the query builder to create your SQL statement that will query your table containing the main disease categories. Do the same for comSubdisease except this time the query will look to your table containg the sub diseases. Ensure that in both the rowsources you include the ID field.
You now need to place a criteria in the query for comSubdisease under the ID field -
=[Forms]![your FormName]![comMainDisease]
This should then only display the subdiseases related to the main disease.
Try it out and if it doesn't work to how you want it then you can e-mail me a sample of your .mdb (providing it's not too large) and I'll have a look for you - robert.dunstan@virgin.net
Good luck
Try creating two unbound combo box controls and say call them comMainDisease and comSubdisease. In comMainDisease's RowSource property use the query builder to create your SQL statement that will query your table containing the main disease categories. Do the same for comSubdisease except this time the query will look to your table containg the sub diseases. Ensure that in both the rowsources you include the ID field.
You now need to place a criteria in the query for comSubdisease under the ID field -
=[Forms]![your FormName]![comMainDisease]
This should then only display the subdiseases related to the main disease.
Try it out and if it doesn't work to how you want it then you can e-mail me a sample of your .mdb (providing it's not too large) and I'll have a look for you - robert.dunstan@virgin.net
Good luck
I feel awful, I think I am explaining this all wrong. I have got the combo boxes to synchronize properly. Now the only problem is that when I change a diagnosis for a patient, it changes all the diagnosis fields for each different patient, and each patient needs to have a different diagnosis.
Eg. Record for Jane Doe. I click on the first box choose the main disease, then the second combo box displays only those subdiseases that fall under the main disease heading. I choose one of those. Okay, Jane Doe is done. I click the record selector for the next record. John Doe. I go to choose a disease and subdisease but it is already filled in with the same the Jane Doe has. If I change this to John Doe's diagnosis, then when I go back to look at Jane's it has changed to what John's is. I need each person's diagnosis to be different. Please help if I have made any sense at all.
Eg. Record for Jane Doe. I click on the first box choose the main disease, then the second combo box displays only those subdiseases that fall under the main disease heading. I choose one of those. Okay, Jane Doe is done. I click the record selector for the next record. John Doe. I go to choose a disease and subdisease but it is already filled in with the same the Jane Doe has. If I change this to John Doe's diagnosis, then when I go back to look at Jane's it has changed to what John's is. I need each person's diagnosis to be different. Please help if I have made any sense at all.
Robert Dunstan
Mr Data
- Local time
- Today, 22:07
- Joined
- Jun 22, 2000
- Messages
- 291
Hi newberc,
Can you tell me if your combo boxes are bound controls. If they are bound to the ID fields for your main disease and sub disease this could be your problem. Have you tried re-creating these combo boxes as unbound controls?
Also what are your data relationships and exactly what tables do you have in your .mdb because the underlying table structure and relationships could be another problem. For example I have an ordering database that records purchase orders and all the orders are stored in two tables. When the user creates a new order, information such as the supplier are retrieved from the suppliers table and the SupplierID is stored in one of the orders tables. What the user then sees on screen is the supplier name and address. However if the user changes the address of that supplier then all existing orders to that supplier are changed as well so I understand what you are saying.
So it does sound as if the underlying structure may be not quite right.
Can you tell me if your combo boxes are bound controls. If they are bound to the ID fields for your main disease and sub disease this could be your problem. Have you tried re-creating these combo boxes as unbound controls?
Also what are your data relationships and exactly what tables do you have in your .mdb because the underlying table structure and relationships could be another problem. For example I have an ordering database that records purchase orders and all the orders are stored in two tables. When the user creates a new order, information such as the supplier are retrieved from the suppliers table and the SupplierID is stored in one of the orders tables. What the user then sees on screen is the supplier name and address. However if the user changes the address of that supplier then all existing orders to that supplier are changed as well so I understand what you are saying.
So it does sound as if the underlying structure may be not quite right.
I think you are right. But, I am new at access and I don't really know much about the relationship stuff. I have a form which has two different tabs. The one tab has the basic Patient Name, Birthdate, etc. The other tab is where the problem is. On this tab, Patient Diagnosis, I have the Diagnosis combo boxes. There are three. The first is the main disease which when clicked on displays the Diseases table. The Diseases table has two columns, DiseaseID and Disease. When you choose a disease a query runs and displays in the second combo box only the SubDiseases that come under the Main Disease that you chose. This is based on the SubDiseases table which has three columns, SubDisease, Code, and DiseaseID. The third field just displays the Code column based on which SubDisease you chose, it's row source is =cboSubDiseases.Column(1). What relationships should I be creating between these tables to be able to give each patient a different diagnosis?
I think you are right. But, I am new at access and I don't really know much about the relationship stuff. I have a form which has two different tabs. The one tab has the basic Patient Name, Birthdate, etc. The other tab is where the problem is. On this tab, Patient Diagnosis, I have the Diagnosis combo boxes. There are three. The first is the main disease which when clicked on displays the Diseases table. The Diseases table has two columns, DiseaseID and Disease. When you choose a disease a query runs and displays in the second combo box only the SubDiseases that come under the Main Disease that you chose. This is based on the SubDiseases table which has three columns, SubDisease, Code, and DiseaseID. The third field just displays the Code column based on which SubDisease you chose, it's row source is =cboSubDiseases.Column(1). What relationships should I be creating between these tables to be able to give each patient a different diagnosis?
Robert Dunstan
Mr Data
- Local time
- Today, 22:07
- Joined
- Jun 22, 2000
- Messages
- 291
Hi newberc,
As another thought, in your patients table do you have fields for DiseaseID and subDiseaseID? These fields would then be your link to the disease tables.
It's not easy to explain how relationships work but based on your probelm I see it as being one main disease has many sub diseases
and many patients.
I think the best thing do to, if you agree, is to e-mail me a copy of your .mdb (if it's not too large) and I'll look at the problem for you. My e-mail adress is robert.dunstan@virgin.net
As another thought, in your patients table do you have fields for DiseaseID and subDiseaseID? These fields would then be your link to the disease tables.
It's not easy to explain how relationships work but based on your probelm I see it as being one main disease has many sub diseases
and many patients.
I think the best thing do to, if you agree, is to e-mail me a copy of your .mdb (if it's not too large) and I'll look at the problem for you. My e-mail adress is robert.dunstan@virgin.net
Robert Dunstan
Mr Data
- Local time
- Today, 22:07
- Joined
- Jun 22, 2000
- Messages
- 291
Hi newberc
Oops I forgot about patient confidentiality!!
OK firstly what is the size of your .mdb?
Make a copy of your .mdb and in your copy delete all the records in your tables and then you can e-mail me the blank database and I can enter some dummy data to play around with the form you are having a problem with.
Oops I forgot about patient confidentiality!!
OK firstly what is the size of your .mdb?
Make a copy of your .mdb and in your copy delete all the records in your tables and then you can e-mail me the blank database and I can enter some dummy data to play around with the form you are having a problem with.
Robert Dunstan
Mr Data
- Local time
- Today, 22:07
- Joined
- Jun 22, 2000
- Messages
- 291
Hi newberc,
Thanks for the e-mail. I did receive it all.
I have a slight problem in that I'm using Access 2000. Can you tell what version your .mdb is in - I assume it's Access 97 as it wouldn't open it in Access 95 but I can in Access 2K
Thanks for the e-mail. I did receive it all.
I have a slight problem in that I'm using Access 2000. Can you tell what version your .mdb is in - I assume it's Access 97 as it wouldn't open it in Access 95 but I can in Access 2K
Robert Dunstan
Mr Data
- Local time
- Today, 22:07
- Joined
- Jun 22, 2000
- Messages
- 291
Hi its me again,
OK great news I think I've solved it. Although I've converted it to Access 2K I can downgrade it Access 97. Basically your combo boxes should be bound to the fields in your tables. As it happens the combo boxes were only based on queries therefore everytime you made a selection it changed all existing records to the one you have selected.
Also the .mdb has been compacted during the conversion and is now only just over 1meg big.
I'll e-mail it to you in the next few minutes all being well
OK great news I think I've solved it. Although I've converted it to Access 2K I can downgrade it Access 97. Basically your combo boxes should be bound to the fields in your tables. As it happens the combo boxes were only based on queries therefore everytime you made a selection it changed all existing records to the one you have selected.
Also the .mdb has been compacted during the conversion and is now only just over 1meg big.
I'll e-mail it to you in the next few minutes all being well
Go into your form in design view and click on the 2nd page tab. Click on the form and it will bring up the properties. Make sure that your Parent and Child field links are both set to PatientID. If it is not, then set it. This is where your problem is.
Good luck
Good luck
