updating combo box

[mikeco555]

I am writing a application in access to facilitate medical records.
In this application under a patients antibiotic history I have 8 combo boxes that pull any one of 100 antibiotics(a patient can be on multiple antibotics at the same time) that are in a base table, this works very nicely except when I create a new patient and proceed to his/her antibiotic history(at which time all combo boxes are blank indicating that the patient is not currently on any antibiotics) and try to use the first combo box to select an antibiotic, when I do this I get a error message(with an exclemation) saying that this field cannot be updated, I click ok and this message is repeated 7 times. After the 7th time I click ok the combo box does indeed get updated and saved properly. The problem as you can probabley imagine is that this unacceptable for an end-user to see.(even though It seems to be functioning properly) Is there some property that I may have missed.

Thanks
Michael Oakes

 

[neileg]

Sounds like you have repeating groups, which means your structure is not normalised. Rather than have 8 fields to store your antibiotics, you should have a separate table that holds the patient ID and an antibiotic ID for each antibiotic they are using. This way you can have an unlimited number of antibiotics per patient.

By using a main form for the patient details and a subform for the antibiotics, you only need one lookup to add a new record to your table.

In practice, you will need three tables, a patient table, an antibiotic table and a junction table to handle the many to many relationship. Search for junction tables in this forum, especially posts from Pat Hartman.