Dynamic RowSource for Combo Box

[gmazza via AccessMonster.com]

Hey there,

I have a table with the following fields:
PatientId, Criteria1, Criteria2, Criteria 3, all the way to Criteria50

I was wondering if there was any way to have a combo box list values from a
single record in a table, and stop when there is a blank field.

So if 1 record in the table goes up to Criteria8, and the rest after that are
all blank, can you populate a combo box with Criteria1 all the way to
Criteria8, stopping there.

Any help is appreciated, thanks!

 

[Ken Snell [MVP]]

Almost anything is possible with VBA code, and what you ask could be done by
using VBA.

However, may I strongly recommend that you change your table structure to a
normalized structure? Your table structure is storing data as field names
(Criteria1, Criteria2, etc.), which is not conducive to writing simple
queries.

What you need is a table like this:

tblPatientCriteria
PatientCriteriaID (autonumber -- primary key)
PatientID (same datatype as you have now for this field)
CriteriaID (Long Integer data type)
CriteriaValue (same datatype as you have now for the Criteria1,
etc. fields)

Then you store data in the table this way:, one record for each patient and
criteria:
PatientCriteriaID (autoassigned by ACCESS)
PatientID 1
CriteriaID 1
CriteriaValue Value1

PatientCriteriaID (autoassigned by ACCESS)
PatientID 1
CriteriaID 2
CriteriaValue Value2

PatientCriteriaID (autoassigned by ACCESS)
PatientID 1
CriteriaID 3
CriteriaValue Value3

PatientCriteriaID (autoassigned by ACCESS)
PatientID 2
CriteriaID 1
CriteriaValue Value1

etc.

Then your query will be very simple for what you want:

SELECT CriteriaValue FROM
tblPatientCriteria
WHERE PatientID = 1;

 

[gmazza via AccessMonster.com]

Thanks for your reply Ken.
I understand what you are saying, it makes better sense.
I need a form though, that the user can enter a number of different criteria,
so like 10 text boxes of different criteria. Then that gets saved to a table,
then on the next form, which will be looking at the table you are talking
about, thats where I want The Criteria they entered on the first form to show
in a combo box, and from there they also have 10 text boxes to add values to
that specific criteria.
Does that make sense?

So I need a form for entering Criteria, and it can be up to 10 text boxes,
they may fill them all in or 1 or 2.
Then, each Criteria they enter on the first form must be accessible in a
combo box on the next form, where they choose a Criteria from the combo, then
there are 10 text boxes again, so they can enter up to 10 values for each
criteria in the combo box.
I need all that saved in a table and accessible.

I understand how having 1 record each is better, but on the first form I need
them to be able to enter multiple Criteria for that Patient, not 1 at a time,
they need to see it all on 1 screen for that patient.

P.S. I'm just using 10 text boxes as an example, I do need 50 text boxes in
case there are that many Criteria's per say.

Almost anything is possible with VBA code, and what you ask could be done by
using VBA.

However, may I strongly recommend that you change your table structure to a
normalized structure? Your table structure is storing data as field names
(Criteria1, Criteria2, etc.), which is not conducive to writing simple
queries.

What you need is a table like this:

tblPatientCriteria
PatientCriteriaID (autonumber -- primary key)
PatientID (same datatype as you have now for this field)
CriteriaID (Long Integer data type)
CriteriaValue (same datatype as you have now for the Criteria1,
etc. fields)

Then you store data in the table this way:, one record for each patient and
criteria:
PatientCriteriaID (autoassigned by ACCESS)
PatientID 1
CriteriaID 1
CriteriaValue Value1

PatientCriteriaID (autoassigned by ACCESS)
PatientID 1
CriteriaID 2
CriteriaValue Value2

PatientCriteriaID (autoassigned by ACCESS)
PatientID 1
CriteriaID 3
CriteriaValue Value3

PatientCriteriaID (autoassigned by ACCESS)
PatientID 2
CriteriaID 1
CriteriaValue Value1

etc.

Then your query will be very simple for what you want:

SELECT CriteriaValue FROM
tblPatientCriteria
WHERE PatientID = 1;

Hey there,

[quoted text clipped - 11 lines]

Any help is appreciated, thanks!

 

[gmazza via AccessMonster.com]

Also, I see what I think needs to be done, and you are right, it needs to be
normalized. Can I somehow allow the user to enter up to 50 Criteria, but each
have a different record, each have a different CriteriaID, all for the same
patient. Just like your suggested table, but can the user see all 50 Criteria
for the 1 patient, and be able to enter and save as many as they want?
Reason I say is because normally you can only see 1 record on a form at a
time.

Thanks for your reply Ken.
I understand what you are saying, it makes better sense.
I need a form though, that the user can enter a number of different criteria,
so like 10 text boxes of different criteria. Then that gets saved to a table,
then on the next form, which will be looking at the table you are talking
about, thats where I want The Criteria they entered on the first form to show
in a combo box, and from there they also have 10 text boxes to add values to
that specific criteria.
Does that make sense?

So I need a form for entering Criteria, and it can be up to 10 text boxes,
they may fill them all in or 1 or 2.
Then, each Criteria they enter on the first form must be accessible in a
combo box on the next form, where they choose a Criteria from the combo, then
there are 10 text boxes again, so they can enter up to 10 values for each
criteria in the combo box.
I need all that saved in a table and accessible.

I understand how having 1 record each is better, but on the first form I need
them to be able to enter multiple Criteria for that Patient, not 1 at a time,
they need to see it all on 1 screen for that patient.

P.S. I'm just using 10 text boxes as an example, I do need 50 text boxes in
case there are that many Criteria's per say.

Almost anything is possible with VBA code, and what you ask could be done by
using VBA.

[quoted text clipped - 48 lines]

 

[John W. Vinson]

Also, I see what I think needs to be done, and you are right, it needs to be
normalized. Can I somehow allow the user to enter up to 50 Criteria, but each
have a different record, each have a different CriteriaID, all for the same
patient. Just like your suggested table, but can the user see all 50 Criteria
for the 1 patient, and be able to enter and save as many as they want?
Reason I say is because normally you can only see 1 record on a form at a
time.

Use a Form based on the patient table with a SUBFORM - a continuous subform,
typically - based on the table Ken suggested. You can enter one, three, fifty,
or 8224 criteria records on the subform if needed.