Cascading Combo Boxes - How to deal with one being empty?

[Marc T]

Hi again,

I have a set of three cascading combo boxes for the following;

Supplier Code, Doc Number, Revision

all is working great, but what I'd like to do is allow the Doc Number combo
to show data for all suppliers if the Supplier Code combo is left empty. At
the moment it shows as a blank if no supplier is selected.

Any help would be great!
Marc

 

[Mr. B]

This sounds like one of those situations where you many need to dynamically
build the sql statement using VBA code that populates the Doc Number combo
box.

Here is a link that might help:
http://msdn.microsoft.com/en-us/library/dd627355.aspx

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

 

[Marc T]

Thanks for the reply!

I have managed to almost solve with the following:

Private Sub Combo0_GotFocus()

If Combo6.Value <> "" Then
Combo0.RowSource = "SELECT DISTINCT tblAllDocsAppend.DOC_NO FROM
tblAllDocsAppend WHERE
((MID(tblAllDocsAppend.DOC_NO,7,4)=Forms!frmMainComboBoxTest!Combo6)) ORDER
BY [DOC_NO]; "
Else
Combo0.RowSource = "SELECT DISTINCT tblAllDocsAppend.DOC_NO FROM
tblAllDocsAppend ORDER BY [DOC_NO]; "
End If

End Sub

The only remaining stickler is how to allow "all" to be selected from the
Supplier combo box seeing as the rowsource is a query. Any ideas?

Cheers,
Marc

 

[Marc T]

Sorry was too eager with posting!

Here's the rowsource for the Suppliers combo:

SELECT DISTINCT Mid(tblAllDocsAppend.DOC_NO,7,4) AS Expr1
FROM tblAllDocsAppend
ORDER BY Mid(tblAllDocsAppend.DOC_NO,7,4);

Thanks for the reply!

I have managed to almost solve with the following:

Private Sub Combo0_GotFocus()

If Combo6.Value <> "" Then
Combo0.RowSource = "SELECT DISTINCT tblAllDocsAppend.DOC_NO FROM
tblAllDocsAppend WHERE
((MID(tblAllDocsAppend.DOC_NO,7,4)=Forms!frmMainComboBoxTest!Combo6)) ORDER
BY [DOC_NO]; "
Else
Combo0.RowSource = "SELECT DISTINCT tblAllDocsAppend.DOC_NO FROM
tblAllDocsAppend ORDER BY [DOC_NO]; "
End If

End Sub

The only remaining stickler is how to allow "all" to be selected from the
Supplier combo box seeing as the rowsource is a query. Any ideas?

Cheers,
Marc

This sounds like one of those situations where you many need to dynamically
build the sql statement using VBA code that populates the Doc Number combo
box.

Here is a link that might help:
http://msdn.microsoft.com/en-us/library/dd627355.aspx

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

 

[John W. Vinson]

Hi again,

I have a set of three cascading combo boxes for the following;

Supplier Code, Doc Number, Revision

all is working great, but what I'd like to do is allow the Doc Number combo
to show data for all suppliers if the Supplier Code combo is left empty. At
the moment it shows as a blank if no supplier is selected.

Any help would be great!
Marc

Use a criterion

[Supplier Code] = Forms!yourform!comboboxname OR Forms!yourform!comboboxname
IS NULL

as a criterion in the doc number combo's row source.

If this isn't making sense please post how you're doing the cascade.

 

[anso]

Hi again,

I have a set of three cascading combo boxes for the following;

Supplier Code, Doc Number, Revision

all is working great, but what I'd like to do is allow the Doc Number
combo
to show data for all suppliers if the Supplier Code combo is left empty.
At
the moment it shows as a blank if no supplier is selected.

Any help would be great!
Marc