Cascade queries

[jean]

Hi

I have a table "tblData" containig many fields [DataCountry],
[DataRegion],[DataCity],[DataName], etc.....

I have 4 dropdown lists on a form name "FrmChoice"

First drop list named "ChoiceCountry" is issued from the field
[DataCountry] of the tblData (unique value)
Second one named "ChoiceRegion" is issued from the field [DataRegion]
of the tblData (unique value)
Third one, named "ChoiceCity" is issued from the field [DataCity] of
the tblData (unique value)
Fourth one, named "ChoiceName" is issued from the field [DataName] of
the tblData (unique value)

To obtain the right result I am working this way

I create a query named "qryData1" Source is tblData. for the field
[DataCountry] i put the following criteria :
=forms!FrmChoice!ChoiceCountry then I add a field "Expr1 : forms!
FrmChoice!ChoiceCountry and on the line under criteria "or" I put
"Is Null"

After I create another query named "qryData2" source is qryData1. for
the field [DataRegion] i put the following criteria :
=forms!FrmChoice!ChoiceRegion then I add a field "Expr1 : forms!
FrmChoice!ChoiceRegion and on the line under criteria "or" I put "Is
Null"

After I create another query named "qryData3" source is qryData2. for
the field [DataCountry] i put the following criteria :
=forms!FrmChoice!ChoiceCountry then I add a field "Expr1 : forms!
FrmChoice!ChoiceCountry and on the line under criteria "or" I put "Is
Null"

Finally I create another query named "qryData4" source is qryData3.
for the field [DataName] i put the following criteria :
=forms!FrmChoice!ChoiceName then I add a field "Expr1 : forms!
FrmChoice!ChoiceName and on the line under criteria "or" I put "Is
Null"

I am producing a report and the source is qryData4 and evrything works
perfectly.

I have use this method for many databases without problems except
one !

If I am using a large database containing 100,000 records with a
cascade of 6 queries, sometimes it takes a while before I get the
report ... but it works

Now I am trying to find an easy way to do the same

Somebody have a suggestion ?

thanks

 

[Dave]

Hi

I have a table "tblData" containig many fields [DataCountry],
[DataRegion],[DataCity],[DataName], etc.....

I have 4 dropdown lists on a form name "FrmChoice"

First drop list named "ChoiceCountry" is issued from the field
[DataCountry] of the tblData (unique value)
Second one named "ChoiceRegion" is issued from the field [DataRegion]
of the tblData (unique value)
Third one, named "ChoiceCity" is issued from the field [DataCity] of
the tblData (unique value)
Fourth one, named "ChoiceName" is issued from the field [DataName] of
the tblData (unique value)

To obtain the right result I am working this way

I create a query named "qryData1" Source is tblData.  for the field
[DataCountry] i put the following criteria :
=forms!FrmChoice!ChoiceCountry   then I add a field "Expr1 : forms!
FrmChoice!ChoiceCountry   and on the line under criteria "or" I put
"Is Null"

After I create another query named "qryData2" source is qryData1.  for
the field [DataRegion] i put the following criteria :
=forms!FrmChoice!ChoiceRegion   then I add a field "Expr1 : forms!
FrmChoice!ChoiceRegion and on the line under criteria "or" I put "Is
Null"

After I create another query named "qryData3" source is qryData2.  for
the field [DataCountry] i put the following criteria :
=forms!FrmChoice!ChoiceCountry   then I add a field "Expr1 : forms!
FrmChoice!ChoiceCountry and on the line under criteria "or" I put "Is
Null"

Finally I create another query named "qryData4" source is qryData3.
for the field [DataName] i put the following criteria :
=forms!FrmChoice!ChoiceName  then I add a field "Expr1 : forms!
FrmChoice!ChoiceName and on the line under criteria "or" I put "Is
Null"

I am producing a report and the source is qryData4 and evrything works
perfectly.

I have use this method for many databases without problems except
one !

If I am using a large database containing 100,000  records with a
cascade of 6 queries, sometimes it takes a while before I get the
report ... but it works

Now I am trying to find an easy way to do the same

Somebody have a suggestion ?

thanks

The underlying problem would be that each query needs to run the
preceding query in order to generate it's own underlying set of
records...a real performance issue in large record sets to be sure. If
each of the four fields are in the same table and are unique (as you
say), that would mean that for Country1 there can only be one record,
along with the assocated values for region, city and name (which also
cannot be in the table more than once if they are unique). I say this
because I take your use of the word 'unique' to mean these table
fields are primary keys. Thus I don't understand why you have multiple
combo boxes based on one table with so many unique fields. Why not
create one query for the report? I must not be understanding exactly
what you've got so I'm probably off base with this possible solution:
You could create a DAO.recordset based on a table or a query (which
can use more than one table if necessary), reference the fields in
that recordset and pass them to the report as a filter.

 

[jean]

Hi

I have a table "tblData" containig many fields [DataCountry],
[DataRegion],[DataCity],[DataName], etc.....

I have 4 dropdown lists on a form name "FrmChoice"

First drop list named "ChoiceCountry" is issued from the field
[DataCountry] of the tblData (unique value)
Second one named "ChoiceRegion" is issued from the field [DataRegion]
of the tblData (unique value)
Third one, named "ChoiceCity" is issued from the field [DataCity] of
the tblData (unique value)
Fourth one, named "ChoiceName" is issued from the field [DataName] of
the tblData (unique value)

To obtain the right result I am working this way

I create a query named "qryData1" Source is tblData.  for the field
[DataCountry] i put the following criteria :
=forms!FrmChoice!ChoiceCountry   then I add a field "Expr1 : forms!
FrmChoice!ChoiceCountry   and on the line under criteria "or" I put
"Is Null"

After I create another query named "qryData2" source is qryData1.  for
the field [DataRegion] i put the following criteria :
=forms!FrmChoice!ChoiceRegion   then I add a field "Expr1 : forms!
FrmChoice!ChoiceRegion and on the line under criteria "or" I put "Is
Null"

After I create another query named "qryData3" source is qryData2.  for
the field [DataCountry] i put the following criteria :
=forms!FrmChoice!ChoiceCountry   then I add a field "Expr1 : forms!
FrmChoice!ChoiceCountry and on the line under criteria "or" I put "Is
Null"

Finally I create another query named "qryData4" source is qryData3.
for the field [DataName] i put the following criteria :
=forms!FrmChoice!ChoiceName  then I add a field "Expr1 : forms!
FrmChoice!ChoiceName and on the line under criteria "or" I put "Is
Null"

I am producing a report and the source is qryData4 and evrything works
perfectly.

I have use this method for many databases without problems except
one !

If I am using a large database containing 100,000  records with a
cascade of 6 queries, sometimes it takes a while before I get the
report ... but it works

Now I am trying to find an easy way to do the same

Somebody have a suggestion ?

thanks

The underlying problem would be that each query needs to run the
preceding query in order to generate it's own underlying set of
records...a real performance issue in large record sets to be sure. If
each of the four fields are in the same table and are unique (as you
say), that would mean that for Country1 there can only be one record,
along with the assocated values for region, city and name (which also
cannot be in the table more than once if they are unique). I say this
because I take your use of the word 'unique' to mean these table
fields are primary keys. Thus I don't understand why you have multiple
combo boxes based on one table with so many unique fields. Why not
create one query for the report? I must not be understanding exactly
what you've got so I'm probably off base with this possible solution:
You could create a DAO.recordset based on a table or a query (which
can use more than one table if necessary), reference the fields in
that recordset and pass them to the report as a filter.- Hide quoted text-

- Show quoted text -

The unique field is just the property for each combo box. The are no
unique fields in the table

 

[jean]

Hi
I have a table "tblData" containig many fields [DataCountry],
[DataRegion],[DataCity],[DataName], etc.....
I have 4 dropdown lists on a form name "FrmChoice"
First drop list named "ChoiceCountry" is issued from the field
[DataCountry] of the tblData (unique value)
Second one named "ChoiceRegion" is issued from the field [DataRegion]
of the tblData (unique value)
Third one, named "ChoiceCity" is issued from the field [DataCity] of
the tblData (unique value)
Fourth one, named "ChoiceName" is issued from the field [DataName] of
the tblData (unique value)
To obtain the right result I am working this way
I create a query named "qryData1" Source is tblData.  for the field
[DataCountry] i put the following criteria :
=forms!FrmChoice!ChoiceCountry   then I add a field "Expr1 : forms!
FrmChoice!ChoiceCountry   and on the line under criteria "or" I put
"Is Null"
After I create another query named "qryData2" source is qryData1.  for
the field [DataRegion] i put the following criteria :
=forms!FrmChoice!ChoiceRegion   then I add a field "Expr1 : forms!
FrmChoice!ChoiceRegion and on the line under criteria "or" I put "Is
Null"
After I create another query named "qryData3" source is qryData2.  for
the field [DataCountry] i put the following criteria :
=forms!FrmChoice!ChoiceCountry   then I add a field "Expr1 : forms!
FrmChoice!ChoiceCountry and on the line under criteria "or" I put "Is
Null"
Finally I create another query named "qryData4" source is qryData3.
for the field [DataName] i put the following criteria :
=forms!FrmChoice!ChoiceName  then I add a field "Expr1 : forms!
FrmChoice!ChoiceName and on the line under criteria "or" I put "Is
Null"
I am producing a report and the source is qryData4 and evrything works
perfectly.
I have use this method for many databases without problems except
one !
If I am using a large database containing 100,000  records with a
cascade of 6 queries, sometimes it takes a while before I get the
report ... but it works
Now I am trying to find an easy way to do the same
Somebody have a suggestion ?
thanks

The underlying problem would be that each query needs to run the
preceding query in order to generate it's own underlying set of
records...a real performance issue in large record sets to be sure. If
each of the four fields are in the same table and are unique (as you
say), that would mean that for Country1 there can only be one record,
along with the assocated values for region, city and name (which also
cannot be in the table more than once if they are unique). I say this
because I take your use of the word 'unique' to mean these table
fields are primary keys. Thus I don't understand why you have multiple
combo boxes based on one table with so many unique fields. Why not
create one query for the report? I must not be understanding exactly
what you've got so I'm probably off base with this possible solution:
You could create a DAO.recordset based on a table or a query (which
can use more than one table if necessary), reference the fields in
that recordset and pass them to the report as a filter.- Hide quoted text -

- Show quoted text -

The unique field is just the property for each combo box.  The are no
unique fields in the table- Hide quoted text -

- Show quoted text -

if the is 200 recored in the data base with country = Mexico, there
will be only one Mexico in the combo box

 

[Dave]

Hi
I have a table "tblData" containig many fields [DataCountry],
[DataRegion],[DataCity],[DataName], etc.....
I have 4 dropdown lists on a form name "FrmChoice"
First drop list named "ChoiceCountry" is issued from the field
[DataCountry] of the tblData (unique value)
Second one named "ChoiceRegion" is issued from the field [DataRegion]
of the tblData (unique value)
Third one, named "ChoiceCity" is issued from the field [DataCity] of
the tblData (unique value)
Fourth one, named "ChoiceName" is issued from the field [DataName] of
the tblData (unique value)
To obtain the right result I am working this way
I create a query named "qryData1" Source is tblData.  for the field
[DataCountry] i put the following criteria :
=forms!FrmChoice!ChoiceCountry   then I add a field "Expr1 : forms!
FrmChoice!ChoiceCountry   and on the line under criteria "or" I put
"Is Null"
After I create another query named "qryData2" source is qryData1.  for
the field [DataRegion] i put the following criteria :
=forms!FrmChoice!ChoiceRegion   then I add a field "Expr1 : forms!
FrmChoice!ChoiceRegion and on the line under criteria "or" I put "Is
Null"
After I create another query named "qryData3" source is qryData2.  for
the field [DataCountry] i put the following criteria :
=forms!FrmChoice!ChoiceCountry   then I add a field "Expr1 : forms!
FrmChoice!ChoiceCountry and on the line under criteria "or" I put "Is
Null"
Finally I create another query named "qryData4" source is qryData3.
for the field [DataName] i put the following criteria :
=forms!FrmChoice!ChoiceName  then I add a field "Expr1 : forms!
FrmChoice!ChoiceName and on the line under criteria "or" I put "Is
Null"
I am producing a report and the source is qryData4 and evrything works
perfectly.
I have use this method for many databases without problems except
one !
If I am using a large database containing 100,000  records with a
cascade of 6 queries, sometimes it takes a while before I get the
report ... but it works
Now I am trying to find an easy way to do the same
Somebody have a suggestion ?
thanks
The underlying problem would be that each query needs to run the
preceding query in order to generate it's own underlying set of
records...a real performance issue in large record sets to be sure. If
each of the four fields are in the same table and are unique (as you
say), that would mean that for Country1 there can only be one record,
along with the assocated values for region, city and name (which also
cannot be in the table more than once if they are unique). I say this
because I take your use of the word 'unique' to mean these table
fields are primary keys. Thus I don't understand why you have multiple
combo boxes based on one table with so many unique fields. Why not
create one query for the report? I must not be understanding exactly
what you've got so I'm probably off base with this possible solution:
You could create a DAO.recordset based on a table or a query (which
can use more than one table if necessary), reference the fields in
that recordset and pass them to the report as a filter.- Hide quoted text -
- Show quoted text -

The unique field is just the property for each combo box.  The are no
unique fields in the table- Hide quoted text -

- Show quoted text -

if the is 200 recored in the data base with country = Mexico, there
will be only one Mexico in the combo box- Hide quoted text -

- Show quoted text -

Ok; don't make the data source for a combo box to be a query for
another control. Here's one way: make the cmb's unbound; create a
query for the first control, grab the sql in query design view and
paste it into the cmb rowsource (property sheet for the control). For
each dependant cmb, create another query and use the value of the
preceding control as a parameter in that query. In your case, maybe
forget the Null part and use Like. What if the field record contains
an empty string (not the same as null)? e.g. Like "*" & [forms]!
[frmName].[controlName]. Events for the control (on change, on click)
can drive the requery of the combo boxes. Then for qry4 you use each
combo box as the query parameters. This way, qry4 doesn't have to run
3, which runs 2, which runs 1. You base the report on qry4. You can
create sql in code as a string variable, open a form and set the
form's recordsource to be the sql, so I presume you could do this for
a report; e.g.
sql = "SELECT tblUser.Level FROM tblUser WHERE
(((tblUser.Level)='admin'));"
DoCmd.OpenForm "frmName" (any other open args here)
Forms!frmName.recordsource = sql

There are other approaches you could take, depending on your
proficiecy in VBA.

 

[jean]

Hi
I have a table "tblData" containig many fields [DataCountry],
[DataRegion],[DataCity],[DataName], etc.....
I have 4 dropdown lists on a form name "FrmChoice"
First drop list named "ChoiceCountry" is issued from the field
[DataCountry] of the tblData (unique value)
Second one named "ChoiceRegion" is issued from the field [DataRegion]
of the tblData (unique value)
Third one, named "ChoiceCity" is issued from the field [DataCity]of
the tblData (unique value)
Fourth one, named "ChoiceName" is issued from the field [DataName] of
the tblData (unique value)
To obtain the right result I am working this way
I create a query named "qryData1" Source is tblData.  for the field
[DataCountry] i put the following criteria :
=forms!FrmChoice!ChoiceCountry   then I add a field "Expr1 : forms!
FrmChoice!ChoiceCountry   and on the line under criteria "or" Iput
"Is Null"
After I create another query named "qryData2" source is qryData1. for
the field [DataRegion] i put the following criteria :
=forms!FrmChoice!ChoiceRegion   then I add a field "Expr1 : forms!
FrmChoice!ChoiceRegion and on the line under criteria "or" I put "Is
Null"
After I create another query named "qryData3" source is qryData2. for
the field [DataCountry] i put the following criteria :
=forms!FrmChoice!ChoiceCountry   then I add a field "Expr1 : forms!
FrmChoice!ChoiceCountry and on the line under criteria "or" I put"Is
Null"
Finally I create another query named "qryData4" source is qryData3.
for the field [DataName] i put the following criteria :
=forms!FrmChoice!ChoiceName  then I add a field "Expr1 : forms!
FrmChoice!ChoiceName and on the line under criteria "or" I put "Is
Null"
I am producing a report and the source is qryData4 and evrything works
perfectly.
I have use this method for many databases without problems except
one !
If I am using a large database containing 100,000  records witha
cascade of 6 queries, sometimes it takes a while before I get the
report ... but it works
Now I am trying to find an easy way to do the same
Somebody have a suggestion ?
thanks
The underlying problem would be that each query needs to run the
preceding query in order to generate it's own underlying set of
records...a real performance issue in large record sets to be sure.If
each of the four fields are in the same table and are unique (as you
say), that would mean that for Country1 there can only be one record,
along with the assocated values for region, city and name (which also
cannot be in the table more than once if they are unique). I say this
because I take your use of the word 'unique' to mean these table
fields are primary keys. Thus I don't understand why you have multiple
combo boxes based on one table with so many unique fields. Why not
create one query for the report? I must not be understanding exactly
what you've got so I'm probably off base with this possible solution:
You could create a DAO.recordset based on a table or a query (which
can use more than one table if necessary), reference the fields in
that recordset and pass them to the report as a filter.- Hide quoted text -
- Show quoted text -
The unique field is just the property for each combo box.  The are no
unique fields in the table- Hide quoted text -
- Show quoted text -

if the is 200 recored in the data base with country = Mexico, there
will be only one Mexico in the combo box- Hide quoted text -

- Show quoted text -

Ok; don't make the data source for a combo box to be a query for
another control. Here's one way: make the cmb's unbound; create a
query for the first control, grab the sql in query design view and
paste it into the cmb rowsource (property sheet for the control). For
each dependant cmb, create another query and use the value of the
preceding control as a parameter in that query. In your case, maybe
forget the Null part and use Like. What if the field record contains
an empty string (not the same as null)? e.g. Like "*" & [forms]!
[frmName].[controlName]. Events for the control (on change, on click)
can drive the requery of the combo boxes. Then for qry4 you use each
combo box as the query parameters. This way, qry4 doesn't have to run
3, which runs 2, which runs 1. You base the report on qry4. You can
create sql in code as a string variable, open a form and set the
form's recordsource to be the sql, so I presume you could do this for
a report; e.g.
sql = "SELECT tblUser.Level FROM tblUser WHERE
(((tblUser.Level)='admin'));"
DoCmd.OpenForm "frmName" (any other open args here)
Forms!frmName.recordsource = sql

There are other approaches you could take, depending on your
proficiecy in VBA.- Hide quoted text -

- Show quoted text -

Hi

Thanks for the answer, but this is a bit too complex for me. I will
continue my search hoping I can find an example of what I am looking
for

 

[jean]

Hi
I have a table "tblData" containig many fields [DataCountry],
[DataRegion],[DataCity],[DataName], etc.....
I have 4 dropdown lists on a form name "FrmChoice"
First drop list named "ChoiceCountry" is issued from the field
[DataCountry] of the tblData (unique value)
Second one named "ChoiceRegion" is issued from the field [DataRegion]
of the tblData (unique value)
Third one, named "ChoiceCity" is issued from the field [DataCity] of
the tblData (unique value)
Fourth one, named "ChoiceName" is issued from the field [DataName] of
the tblData (unique value)
To obtain the right result I am working this way
I create a query named "qryData1" Source is tblData.  for thefield
[DataCountry] i put the following criteria :
=forms!FrmChoice!ChoiceCountry   then I add a field "Expr1 : forms!
FrmChoice!ChoiceCountry   and on the line under criteria "or"I put
"Is Null"
After I create another query named "qryData2" source is qryData1.  for
the field [DataRegion] i put the following criteria :
=forms!FrmChoice!ChoiceRegion   then I add a field "Expr1 :forms!
FrmChoice!ChoiceRegion and on the line under criteria "or" I put "Is
Null"
After I create another query named "qryData3" source is qryData2.  for
the field [DataCountry] i put the following criteria :
=forms!FrmChoice!ChoiceCountry   then I add a field "Expr1 : forms!
FrmChoice!ChoiceCountry and on the line under criteria "or" I put "Is
Null"
Finally I create another query named "qryData4" source is qryData3.
for the field [DataName] i put the following criteria :
=forms!FrmChoice!ChoiceName  then I add a field "Expr1 : forms!
FrmChoice!ChoiceName and on the line under criteria "or" I put "Is
Null"
I am producing a report and the source is qryData4 and evrything works
perfectly.
I have use this method for many databases without problems except
one !
If I am using a large database containing 100,000  records with a
cascade of 6 queries, sometimes it takes a while before I get the
report ... but it works
Now I am trying to find an easy way to do the same
Somebody have a suggestion ?
thanks
The underlying problem would be that each query needs to run the
preceding query in order to generate it's own underlying set of
records...a real performance issue in large record sets to be sure. If
each of the four fields are in the same table and are unique (as you
say), that would mean that for Country1 there can only be one record,
along with the assocated values for region, city and name (which also
cannot be in the table more than once if they are unique). I say this
because I take your use of the word 'unique' to mean these table
fields are primary keys. Thus I don't understand why you have multiple
combo boxes based on one table with so many unique fields. Why not
create one query for the report? I must not be understanding exactly
what you've got so I'm probably off base with this possible solution:
You could create a DAO.recordset based on a table or a query (which
can use more than one table if necessary), reference the fields in
that recordset and pass them to the report as a filter.- Hide quoted text -
- Show quoted text -
The unique field is just the property for each combo box.  The are no
unique fields in the table- Hide quoted text -
- Show quoted text -
if the is 200 recored in the data base with country = Mexico, there
will be only one Mexico in the combo box- Hide quoted text -
- Show quoted text -

Ok; don't make the data source for a combo box to be a query for
another control. Here's one way: make the cmb's unbound; create a
query for the first control, grab the sql in query design view and
paste it into the cmb rowsource (property sheet for the control). For
each dependant cmb, create another query and use the value of the
preceding control as a parameter in that query. In your case, maybe
forget the Null part and use Like. What if the field record contains
an empty string (not the same as null)? e.g. Like "*" & [forms]!
[frmName].[controlName]. Events for the control (on change, on click)
can drive the requery of the combo boxes. Then for qry4 you use each
combo box as the query parameters. This way, qry4 doesn't have to run
3, which runs 2, which runs 1. You base the report on qry4. You can
create sql in code as a string variable, open a form and set the
form's recordsource to be the sql, so I presume you could do this for
a report; e.g.
sql = "SELECT tblUser.Level FROM tblUser WHERE
(((tblUser.Level)='admin'));"
DoCmd.OpenForm "frmName" (any other open args here)
Forms!frmName.recordsource = sql

There are other approaches you could take, depending on your
proficiecy in VBA.- Hide quoted text -

- Show quoted text -

Hi

Thanks for the answer, but this is a bit too complex for me.  I will
continue my search hoping I can find an example of what I am looking
for- Hide quoted text -

- Show quoted text -

Thanks to Allen Browne

I found a perfect example of what I needed and ..... it works perfectly

 

[Dave]

Hi
I have a table "tblData" containig many fields [DataCountry],
[DataRegion],[DataCity],[DataName], etc.....
I have 4 dropdown lists on a form name "FrmChoice"
First drop list named "ChoiceCountry" is issued from the field
[DataCountry] of the tblData (unique value)
Second one named "ChoiceRegion" is issued from the field [DataRegion]
of the tblData (unique value)
Third one, named "ChoiceCity" is issued from the field [DataCity] of
the tblData (unique value)
Fourth one, named "ChoiceName" is issued from the field [DataName] of
the tblData (unique value)
To obtain the right result I am working this way
I create a query named "qryData1" Source is tblData.  for the field
[DataCountry] i put the following criteria :
=forms!FrmChoice!ChoiceCountry   then I add a field "Expr1 : forms!
FrmChoice!ChoiceCountry   and on the line under criteria "or" I put
"Is Null"
After I create another query named "qryData2" source is qryData1.  for
the field [DataRegion] i put the following criteria :
=forms!FrmChoice!ChoiceRegion   then I add a field "Expr1: forms!
FrmChoice!ChoiceRegion and on the line under criteria "or" I put "Is
Null"
After I create another query named "qryData3" source is qryData2.  for
the field [DataCountry] i put the following criteria :
=forms!FrmChoice!ChoiceCountry   then I add a field "Expr1 : forms!
FrmChoice!ChoiceCountry and on the line under criteria "or" Iput "Is
Null"
Finally I create another query named "qryData4" source is qryData3.
for the field [DataName] i put the following criteria :
=forms!FrmChoice!ChoiceName  then I add a field "Expr1 : forms!
FrmChoice!ChoiceName and on the line under criteria "or" I put "Is
Null"
I am producing a report and the source is qryData4 and evrything works
perfectly.
I have use this method for many databases without problems except
one !
If I am using a large database containing 100,000  records with a
cascade of 6 queries, sometimes it takes a while before I getthe
report ... but it works
Now I am trying to find an easy way to do the same
Somebody have a suggestion ?
thanks
The underlying problem would be that each query needs to run the
preceding query in order to generate it's own underlying set of
records...a real performance issue in large record sets to be sure. If
each of the four fields are in the same table and are unique (as you
say), that would mean that for Country1 there can only be one record,
along with the assocated values for region, city and name (which also
cannot be in the table more than once if they are unique). I say this
because I take your use of the word 'unique' to mean these table
fields are primary keys. Thus I don't understand why you have multiple
combo boxes based on one table with so many unique fields. Why not
create one query for the report? I must not be understanding exactly
what you've got so I'm probably off base with this possible solution:
You could create a DAO.recordset based on a table or a query (which
can use more than one table if necessary), reference the fieldsin
that recordset and pass them to the report as a filter.- Hide quoted text -
- Show quoted text -
The unique field is just the property for each combo box.  The are no
unique fields in the table- Hide quoted text -
- Show quoted text -
if the is 200 recored in the data base with country = Mexico, there
will be only one Mexico in the combo box- Hide quoted text -
- Show quoted text -
Ok; don't make the data source for a combo box to be a query for
another control. Here's one way: make the cmb's unbound; create a
query for the first control, grab the sql in query design view and
paste it into the cmb rowsource (property sheet for the control). For
each dependant cmb, create another query and use the value of the
preceding control as a parameter in that query. In your case, maybe
forget the Null part and use Like. What if the field record contains
an empty string (not the same as null)? e.g. Like "*" & [forms]!
[frmName].[controlName]. Events for the control (on change, on click)
can drive the requery of the combo boxes. Then for qry4 you use each
combo box as the query parameters. This way, qry4 doesn't have to run
3, which runs 2, which runs 1. You base the report on qry4. You can
create sql in code as a string variable, open a form and set the
form's recordsource to be the sql, so I presume you could do this for
a report; e.g.
sql = "SELECT tblUser.Level FROM tblUser WHERE
(((tblUser.Level)='admin'));"
DoCmd.OpenForm "frmName" (any other open args here)
Forms!frmName.recordsource = sql
There are other approaches you could take, depending on your
proficiecy in VBA.- Hide quoted text -
- Show quoted text -

Hi

Thanks for the answer, but this is a bit too complex for me.  I will
continue my search hoping I can find an example of what I am looking
for- Hide quoted text -

- Show quoted text -

Thanks to Allen Browne

I found a perfect example of what I needed and ..... it works perfectly

Glad you found what you needed! Sorry I was not more explicit, but I
didn't know how much detail you needed as your level of coding
knowledge wasn't evident. You can be sure I would have delved into it
deeper for you.
Good luck!