How to make unbound combo box select all if related query record is null

[rocketD]

Hi, I've read any thread I could find even remotely related to this,
but I can't seem to get what I need to work. I have 3 fields,
clinicID, surveyID, and cohortID that I want to use in a query to
limit the records that appear in my form. Each have a combo box in
the form, and data for each are recorded in their own lookup tables to
keep the database normalized. CohortID and surveyID are both tied to
clinicID, but not to eachother directly. In the query my form is
based on, I have the criteria set to select clinicID and surveyID and
cohortID using what is chosen in the form (each is a "Like
forms.formname.fieldID"). This works, BUT.

In the table where the records are stored, there is clinicID,
surveyID, cohortID, and info unique to each record (e.g., date,
personID, etc.). Not every survey requires a cohort group, so not
every survey record has a cohortID (those have just null cohort
fields). When I select a clinic and survey, the cohort box is empty
if there are no cohorts for that survey (that's fine). However, if
there are no cohorts for that survey, NOTHING shows up, even though
there are many records for the survey. This all works perfectly when
a survey has cohorts associated with it.

My goal is to do what it's doing now, which is not show anything in
the cohort box if there are no cohorts for a survey, but to show all
records for that survey. I still want it to limit by cohorts if there
ARE cohorts associated with the survey. Using info I found on one
thread, I tried entering

Me.refresh
Me.cohortID = null

in the OnChange event of clinicID. That prevents cohortID from
limiting anything at all, but shows all records even for surveys with
no cohorts. I'm at a loss here - do I have to enter a placeholder
cohort value for all records without cohort info to be able to limit
by cohort but still show all the other survey records?

Thanks,
Dara

 

[rocketD]

Take a look at this article and example:
    Search form - Handle many optional criteria
at:
   http://allenbrowne.com/ser-62.html

I think that's what you are seeking to do.

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"rocketD"  wrote in message


Hi, I've read any thread I could find even remotely related to this,
but I can't seem to get what I need to work.  I have 3 fields,
clinicID, surveyID, and cohortID that I want to use in a query to
limit the records that appear in my form.  Each have a combo box in
the form, and data for each are recorded in their own lookup tables to
keep the database normalized.  CohortID and surveyID are both tied to
clinicID, but not to eachother directly.  In the query my form is
based on, I have the criteria set to select clinicID and surveyID and
cohortID using what is chosen in the form (each is a "Like
forms.formname.fieldID").  This works, BUT.

In the table where the records are stored, there is clinicID,
surveyID, cohortID, and info unique to each record (e.g., date,
personID, etc.).  Not every survey requires a cohort group, so not
every survey record has a cohortID (those have just null cohort
fields).  When I select a clinic and survey, the cohort box is empty
if there are no cohorts for that survey (that's fine).  However, if
there are no cohorts for that survey, NOTHING shows up, even though
there are many records for the survey.  This all works perfectly when
a survey has cohorts associated with it.

My goal is to do what it's doing now, which is not show anything in
the cohort box if there are no cohorts for a survey, but to show all
records for that survey.  I still want it to limit by cohorts if there
ARE cohorts associated with the survey.  Using info I found on one
thread, I tried entering

Me.refresh
Me.cohortID = null

in the OnChange event of clinicID.  That prevents cohortID from
limiting anything at all, but shows all records even for surveys with
no cohorts.  I'm at a loss here - do I have to enter a placeholder
cohort value for all records without cohort info to be able to limit
by cohort but still show all the other survey records?

Thanks,
Dara

Mr. Browne,

Thank you for answering me. This is a good example. I hope that in
the future I will have the time to figure out how it works (I'm not a
programmer so it's a very slow process for me to get to understand
code). I'll have to come up with a work around for now, but it would
be great to use this type of setup in the future. It's very kind of
you to make it public.

Regards,
Dara