Cascading Combo Boxes - Expression is too complex to be evaluated ...

[Maurita]

Hi all, hope everyone is doing well. I have spent a week trying to
get my cascading combo boxes to work. I get the first combo box to
return unique values, but I get the error message "Expression is too
complex to be evaluated ..." for the second combo box.

The code is as follows: SELECT EngineSerialNo, [InductionDate] FROM
EXPEDITES WHERE (EngineSerialNo=Forms!frmAllExpeditesPerOverhaul1) Or
(EngineSerialNo=Forms!frmAllExeditesPerOverhaul1 Is Null) ORDER BY
[EngineSerialNo];

My tables are as follows:

Main Table: EXPEDITES (fields: CustomerID [same as Overhaul
Facility) - OverhaulFacility - EngineSerial Number - Induction Date
OverhaulNumber - there are other fields that are not pertinent to this
post)

2nd Table: OVERHAUL [*this table links directly to the EXPEDITES
table by the OverhaulNumber] - (fields: OverhaulFacility,
EngineSerialNumber, InductionDate,OverhaulNumber)

3rd Table: tblCustomers [*this table links directly to the OVERHAUL
table by the CustomerID and OverhaulFacility fields] - (fields:
CustomerID, Customer)

4th Table: tblEngineSerialNumber [*this table links directly to the
OVERHAUL table by the EngineSerial Numer field] - (fields:
EngineSerialNumber, CustomerID)

On my frm titled "frmAllExpeditesPerOverhaul1", I have two combo
boxes. The first combo box is titled cboOverhaulFacility. This combo
box's row source is qryUniqueOverhaulFacilities - and works great.
The second combo box is titled cboEngineSerialNumber and has the row
source is the code reference above, which doesn't work. The engine
serial numbers come up, but all engine numbers come up, not just the
ones pertaining to the selected overhaul facility in
cboOverhaulFacility.

Please, help me with this problem. I have tried different scenarios
and keep coming up with the same problem. I've compacted and repaired
the database and created a new database and imported only the tables,
queries and forms necessary; but I get the same error message.

Thank you in advance for your help in this matter.

Maurita Searcy

 

[Douglas J. Steele]

That should be

SELECT EngineSerialNo, [InductionDate] FROM
EXPEDITES WHERE (EngineSerialNo=Forms!frmAllExpeditesPerOverhaul1) Or
(orms!frmAllExeditesPerOverhaul1 Is Null) ORDER BY
[EngineSerialNo];

 

[Maurita]

That should be

SELECT EngineSerialNo, [InductionDate] FROM
EXPEDITES WHERE (EngineSerialNo=Forms!frmAllExpeditesPerOverhaul1) Or
(orms!frmAllExeditesPerOverhaul1 Is Null) ORDER BY
[EngineSerialNo];

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

Hi all, hope everyone is doing well. �I have spent a week tryingto
get my cascading combo boxes to work. �I get the first combo boxto
return unique values, but I get the error message "Expression is too
complex to be evaluated ..." for the second combo box.

The code is as follows: �SELECT EngineSerialNo, [InductionDate] FROM
EXPEDITES WHERE (EngineSerialNo=Forms!frmAllExpeditesPerOverhaul1) Or
(EngineSerialNo=Forms!frmAllExeditesPerOverhaul1 Is Null) ORDER BY
[EngineSerialNo];

My tables are as follows:

Main Table: �EXPEDITES �(fields: CustomerID [same as Overhaul
Facility) - OverhaulFacility - EngineSerial Number - Induction Date
OverhaulNumber - there are other fields that are not pertinent to this
post)

2nd Table: �OVERHAUL [*this table links directly to the EXPEDITES
table by the OverhaulNumber] - (fields: OverhaulFacility,
EngineSerialNumber, InductionDate,OverhaulNumber)

3rd Table: �tblCustomers [*this table links directly to the OVERHAUL
table by the CustomerID and OverhaulFacility fields] - (fields:
CustomerID, Customer)

4th Table: �tblEngineSerialNumber [*this table links directly tothe
OVERHAUL table by the EngineSerial Numer field] - (fields:
EngineSerialNumber, CustomerID)

On my frm titled "frmAllExpeditesPerOverhaul1", I have two combo
boxes. �The first combo box is titled cboOverhaulFacility. �This combo
box's row source is qryUniqueOverhaulFacilities - and works great.
The second combo box is titled cboEngineSerialNumber and has the row
source is the code reference above, which doesn't work. �The engine
serial numbers come up, but all engine numbers come up, not just the
ones pertaining to the selected overhaul facility in
cboOverhaulFacility.

Please, help me with this problem. �I have tried different scenarios
and keep coming up with the same problem. �I've compacted and repaired
the database and created a new database and imported only the tables,
queries and forms necessary; but I get the same error message.

Thank you in advance for your help in this matter.

Maurita Searcy- Hide quoted text -

- Show quoted text -

Doug,

Thank you so much for your help. I tried the code and I no longer
have the error message, thank you. But, the second combo box still is
not filtering out the choice made in combo1. I am at a loss. For a
week now, I've put in various code and gotten this same problem. I
have no idea what do do next.

Maurita Searcy

 

[Linq Adams via AccessMonster.com]

I'm the first to admit that SQL statements are not my forte, but

WHERE (EngineSerialNo=Forms!frmAllExpeditesPerOverhaul1) Or (Forms!
frmAllExeditesPerOverhaul1 Is Null)

looks like you're asking if a field

EngineSerialNo

is equal to a form

Forms!frmAllExpeditesPerOverhaul1

or if the form Is Null.

 

[Maurita]

I'm the first to admit that SQL statements are not my forte, but

WHERE (EngineSerialNo=Forms!frmAllExpeditesPerOverhaul1) Or (Forms!
frmAllExeditesPerOverhaul1 Is Null)

looks like you're asking if a field

EngineSerialNo

is equal to a form

Forms!frmAllExpeditesPerOverhaul1

or if the form Is Null.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200712/1

Thanks for all your help but I am still having trouble. I am so tired
of working on this problem for over a week.

Have a Merry Christmas.