DLookup and combobox ( Selecting value based on yes/no )

[ramindya]

I have a table named tblMain and the schema is: ( MRN is Primary key )

tblMAIN
MRN STUDY1ELIGIBLE STUDY2ELIGIBLE STUDY3ELIGIBLE ..... upto STUDY30ELIGIBLE

Sample data for the table is: ( For simplicity I reduced to 3 studies actually there are 30 more studies )
MRN STUDY1ELIGIBLE STUDY2ELIGIBLE STUDY3ELIGIBLE
1234 1 0 1
4567 0 1 1

Where Studyeligible means patient eligible for studies. This table/patient data is shown in the main page as dashboard for each patient.
For example, main dashboard (formname is Mainform) will show MRN : 1234 STUDY1ELIGIBLE: 1 STUDY2ELIGIBLE: 0 STUDY3ELIGIBLE : 1 and there is a button to open another form
named ( Scheduleform ) and behind the form is a table name tblSchedule whose schema is:

tblSchedule: ( ID is Primaykey and autonumber datatype)
ID MRN STUDYNAME SCHEDULEDATE



The relationship between tblMain and tblSchedule is ( One to Many ) coz a patient can enroll in any number of studies.

Question: I want to have a combo box for the Studyname field in Scheduleform which populates the Studynames based on the previous selection which I have enrolled (criteria is StudyXELIGIBLE = 1 )
in the tblMain.How to populate the combobox values with those studynames ?

For example: on opening the Scheduleform for MRN 1234 the combobox should show only STUDY1ELIGIBLE and STUDY2ELIGIBLE and select one of them and save it in the Studyname field of Studyname of tblSchedule.

Thanks for your kind help!

[alansidman]

Crossposting
http://www.access-programmers.co.uk/...d.php?t=222579

[ssanfu]

It would take a LOT of programming to do what you want because you do not have a normalized structure as evidenced by the repeating field names: "STUDY1ELIGIBLE STUDY2ELIGIBLE STUDY3ELIGIBLE ..... up to STUDY30ELIGIBLE".

This is described as "Committing spreadsheet". The table is "short and fat" instead of being "tall and skinny". You should have a table for patients, a table for studies and a junction table linking patients to studies; each patient can be in many studies and each study can have many patients.

I worked on a database for scoring Karate tournaments (a hobby). I spent months trying to get it to work right. The table had Score1, Score2, Score3,..., Score5. It was such a struggle to get anything done I finally asked for help from a programmer friend that worked on mainframe databases at a bank. After looking at it for all of 2 minutes, I was told straight to my face "Can't help you. It is a bad design. Fix it, then come back." All my months effort for nothing.....except I got a good lesson on normalization.

Sooo.... The first thing you need to do is normalize your structure.

There are lots of sites to help with normalization. Here is one by Crystal:
http://www.accessmvp.com/Strive4Peace/