Cascading Combo Boxes Not Filtering

Hi everyone, hope someone can help with a problem I've been working on
for two weeks. I have two cascading combo boxes, but I can not get
the second combo box to filter information relating to selection made
in the first combo box. The first combo box is titled
cboOverhaulFacility and is based on a query to show unique records.
The second combo box is titled cboEngineSerialNumbers and should show
only numbers relating to the Overhaul Facility chosen in
cboOverhaulFacility. Below is table and form information.

FORM: frmAllExpeditesPerOverhaul

cboOverhaulFacility: Row Source = qryUniqueOverhaulFacilities
AfterUpdate =
Me.cboEngineSerialNumber.Requery

cboEngineSerialNumber: Row Source =
SELECT EngineSerialNo, [CustomerID] FROM EXPEDITES WHERE
(EngineSerialNo=Forms!cboEngineSerialNo) Or (EngineSerialNo=Forms!
cboEngineSerialNo Is Null) ORDER BY [EngineSerialNo];

Table that links to Expedite table called Overhaul (fields are
Overhaul Facility, Engine Serial Number, Induction Date, and the key
field of Overhaul Number)

A 3rd Table that links to the Expedite table called tblCustomers
(fields are: key field CustomerID, Customer)

A 4th Table that also links to the Expedite table called
tblEngneSerialNumber (fields are: key field Engine Serial Number,
CustomerID)

Thank you in advance for any help in this matter.

Maurita Searcy

Re: Cascading Combo Boxes Not Filtering

Maurita wrote:
> Hi everyone, hope someone can help with a problem I've been working on
> for two weeks. I have two cascading combo boxes, but I can not get
> the second combo box to filter information relating to selection made
> in the first combo box. The first combo box is titled
> cboOverhaulFacility and is based on a query to show unique records.
> The second combo box is titled cboEngineSerialNumbers and should show
> only numbers relating to the Overhaul Facility chosen in
> cboOverhaulFacility. Below is table and form information.
>
> FORM: frmAllExpeditesPerOverhaul
>
> cboOverhaulFacility: Row Source = qryUniqueOverhaulFacilities
> AfterUpdate =
> Me.cboEngineSerialNumber.Requery
>
> cboEngineSerialNumber: Row Source =
> SELECT EngineSerialNo, [CustomerID] FROM EXPEDITES WHERE
> (EngineSerialNo=Forms!cboEngineSerialNo) Or (EngineSerialNo=Forms!
> cboEngineSerialNo Is Null) ORDER BY [EngineSerialNo];
>
> Table that links to Expedite table called Overhaul (fields are
> Overhaul Facility, Engine Serial Number, Induction Date, and the key
> field of Overhaul Number)
>
> A 3rd Table that links to the Expedite table called tblCustomers
> (fields are: key field CustomerID, Customer)
>
> A 4th Table that also links to the Expedite table called
> tblEngneSerialNumber (fields are: key field Engine Serial Number,
> CustomerID)
>
> Thank you in advance for any help in this matter.
>
> Maurita Searcy
>
You use Forms!cboEngineSerialNo. The problem is...which form? See if
Forms!frmAllExpeditesPerOverhaul!cboEngineSerialNo works a bit better in
cboEngineSerialNumber's rowsource .

End of the line
http://youtube.com/watch?v=nSL4Eqpm9l4

Re: Cascading Combo Boxes Not Filtering

On Dec 28, 11:19ï¿=BDam, Salad wrote:
> Maurita wrote:
> > Hi everyone, hope someone can help with a problem I've been working on
> > for two weeks. ï¿=BDI have two cascading combo boxes, but I can not =
get
> > the second combo box to filter information relating to selection made
> > in the first combo box. ï¿=BD The first combo box is titled
> > cboOverhaulFacility and is based on a query to show unique records.
> > The second combo box is titled cboEngineSerialNumbers and should show
> > only numbers relating to the Overhaul Facility chosen in
> > cboOverhaulFacility. ï¿=BDBelow is table and form information.
>
> > FORM: ï¿=BDfrmAllExpeditesPerOverhaul
>
> > cboOverhaulFacility: ï¿=BDRow Source =3D qryUniqueOverhaulFacilities=

> > ï¿=BD ï¿=BD ï¿=BD ï¿=BD ï¿=BD ï¿=BD ï¿=BD =
ï¿=BD ï¿=BD ï¿=BD ï¿=BD ï¿=BD ï¿=BD ï¿=BD ï¿=
=BD ï¿=BD ï¿=BD ï¿=BD AfterUpdate =3D
> > Me.cboEngineSerialNumber.Requery
>
> > cboEngineSerialNumber: ï¿=BDRow Source =3D
> > SELECT EngineSerialNo, [CustomerID] FROM EXPEDITES WHERE
> > (EngineSerialNo=3DForms!cboEngineSerialNo) Or (EngineSerialNo=3DForms!
> > cboEngineSerialNo Is Null) ORDER BY [EngineSerialNo];
>
> > Table that links to Expedite table called Overhaul (fields are
> > Overhaul Facility, Engine Serial Number, Induction Date, and the key
> > field of Overhaul Number)
>
> > A 3rd Table that links to the Expedite table called tblCustomers
> > (fields are: key field CustomerID, Customer)
>
> > A 4th Table that also links to the Expedite table called
> > tblEngneSerialNumber (fields are: key field Engine Serial Number,
> > CustomerID)
>
> > Thank you in advance for any help in this matter.
>
> > Maurita Searcy
>
> You use Forms!cboEngineSerialNo. ï¿=BDThe problem is...which form? See=
if
> Forms!frmAllExpeditesPerOverhaul!cboEngineSerialNo works a bit better in
> cboEngineSerialNumber's rowsource .
>
> End of the linehttp://youtube.com/watch?v=3DnSL4Eqpm9l4- Hide quoted text =
-
>
> - Show quoted text -

Thank you for your response, but changing the rowsource does not
work. What else do you suggest that I check.

Thank you.

Maurita

Re: Cascading Combo Boxes Not Filtering

On Fri, 28 Dec 2007 12:23:38 -0800 (PST), Maurita
wrote:

>On Dec 28, 11:19?am, Salad wrote:
>> Maurita wrote:
>> > Hi everyone, hope someone can help with a problem I've been working on
>> > for two weeks. ?I have two cascading combo boxes, but I can not get
>> > the second combo box to filter information relating to selection made
>> > in the first combo box. ? The first combo box is titled
>> > cboOverhaulFacility and is based on a query to show unique records.
>> > The second combo box is titled cboEngineSerialNumbers and should show
>> > only numbers relating to the Overhaul Facility chosen in
>> > cboOverhaulFacility. ?Below is table and form information.
>>
>> > FORM: ?frmAllExpeditesPerOverhaul
>>
>> > cboOverhaulFacility: ?Row Source = qryUniqueOverhaulFacilities
>> > ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? AfterUpdate =
>> > Me.cboEngineSerialNumber.Requery
>>
>> > cboEngineSerialNumber: ?Row Source =
>> > SELECT EngineSerialNo, [CustomerID] FROM EXPEDITES WHERE
>> > (EngineSerialNo=Forms!cboEngineSerialNo) Or (EngineSerialNo=Forms!
>> > cboEngineSerialNo Is Null) ORDER BY [EngineSerialNo];
>>
>> > Table that links to Expedite table called Overhaul (fields are
>> > Overhaul Facility, Engine Serial Number, Induction Date, and the key
>> > field of Overhaul Number)
>>
>> > A 3rd Table that links to the Expedite table called tblCustomers
>> > (fields are: key field CustomerID, Customer)
>>
>> > A 4th Table that also links to the Expedite table called
>> > tblEngneSerialNumber (fields are: key field Engine Serial Number,
>> > CustomerID)
>>
>> > Thank you in advance for any help in this matter.
>>
>> > Maurita Searcy
>>
>> You use Forms!cboEngineSerialNo. ?The problem is...which form? See if
>> Forms!frmAllExpeditesPerOverhaul!cboEngineSerialNo works a bit better in
>> cboEngineSerialNumber's rowsource .
>>
>> End of the linehttp://youtube.com/watch?v=nSL4Eqpm9l4- Hide quoted text -
>>
>> - Show quoted text -
>
>Thank you for your response, but changing the rowsource does not
>work. What else do you suggest that I check.
>
>Thank you.
>
>Maurita

Salad has correctly identified one of the errors in your RowSource
entry. Did you correct them both? Do you understand that Forms!Control
makes no sense to Access? You need to use Me!Control or
Forms!FormName!Control.

Re: Cascading Combo Boxes Not Filtering

On Dec 28, 10:04 am, Maurita wrote:
> Hi everyone, hope someone can help with a problem I've been working on
> for two weeks. I have two cascading combo boxes, but I can not get
> the second combo box to filter information relating to selection made
> in the first combo box. The first combo box is titled
> cboOverhaulFacility and is based on a query to show unique records.
> The second combo box is titled cboEngineSerialNumbers and should show
> only numbers relating to the Overhaul Facility chosen in
> cboOverhaulFacility. Below is table and form information.
>
> FORM: frmAllExpeditesPerOverhaul
>
> cboOverhaulFacility: Row Source = qryUniqueOverhaulFacilities
> AfterUpdate =
> Me.cboEngineSerialNumber.Requery
>
> cboEngineSerialNumber: Row Source =
> SELECT EngineSerialNo, [CustomerID] FROM EXPEDITES WHERE
> (EngineSerialNo=Forms!cboEngineSerialNo) Or (EngineSerialNo=Forms!
> cboEngineSerialNo Is Null) ORDER BY [EngineSerialNo];
>
> Table that links to Expedite table called Overhaul (fields are
> Overhaul Facility, Engine Serial Number, Induction Date, and the key
> field of Overhaul Number)
>
> A 3rd Table that links to the Expedite table called tblCustomers
> (fields are: key field CustomerID, Customer)
>
> A 4th Table that also links to the Expedite table called
> tblEngneSerialNumber (fields are: key field Engine Serial Number,
> CustomerID)
>
> Thank you in advance for any help in this matter.
>
> Maurita Searcy

There are three problems (perhaps more);

1. As Salad points out Forms! without specifying which Form;
2. You have a cyclical action in setting cboEngineSerialNumber's
rowsource to be filtered on cboEngineSerialNumber;
3. EngineSerialNo=Forms!cboEngineSerialNo Is Null; Forms!
cboEngineSerialNo Is Null will evaluate to a boolean, and given Access/
VBA's penchant for type-conversion, it's likely we'll be looking for
an EngineSerialNo of 0 or -1.

I started to write some air code to help but stopped when I realized
#2 above.
Here it is but until we clear up that problem I suspect that it's
useless:

Private Sub RefreshCboEngineSerialNumber()
Dim SQL$
Dim EngineSerialNo
SQL = "SELECT [EngineSerialNo], [CustomerID] FROM Expedites"
On Error GoTo RefreshCboEngineSerialNumberErr
EngineSerialNo = Me.CboEngineSerialNo
If IsNumeric(EngineSerialNo) Then
SQL = SQL & " WHERE EngineSerialNo = " & EngineSerialNo
End If
RefreshCboEngineSerialNumberExit:
With Me.CboEngineSerialNumber
.RowSource = SQL
.Requery
End With
Exit Sub
RefreshCboEngineSerialNumberErr:
Resume RefreshCboEngineSerialNumberExit
End Sub

Private Sub cboOverhaulFacility_AfterUpdate()
RefreshCboEngineSerialNumber
End Sub

I suggest that you describe the two combo boxes and their row sources
and what you want done, in general terms, when a row is chosen from
the first. Some Combo Box guru is likely to help you.