A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old October 27th, 2009, 03:55 PM posted to microsoft.public.access.forms
Marc T
external usenet poster
 
Posts: 35
Default Cascading Combo Boxes - How to deal with one being empty?

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
  #2  
Old October 27th, 2009, 04:41 PM posted to microsoft.public.access.forms
Mr. B[_4_]
external usenet poster
 
Posts: 171
Default Cascading Combo Boxes - How to deal with one being empty?

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" wrote:

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

  #3  
Old October 27th, 2009, 05:53 PM posted to microsoft.public.access.forms
Marc T
external usenet poster
 
Posts: 35
Default Cascading Combo Boxes - How to deal with one being empty?

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!frmMainCo mboBoxTest!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

"Mr. B" wrote:

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" wrote:

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

  #4  
Old October 27th, 2009, 05:58 PM posted to microsoft.public.access.forms
Marc T
external usenet poster
 
Posts: 35
Default Cascading Combo Boxes - How to deal with one being empty?

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);



"Marc T" wrote:

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!frmMainCo mboBoxTest!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

"Mr. B" wrote:

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" wrote:

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

  #5  
Old October 27th, 2009, 05:59 PM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Cascading Combo Boxes - How to deal with one being empty?

On Tue, 27 Oct 2009 07:55:01 -0700, Marc T
wrote:

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.
--

John W. Vinson [MVP]
  #6  
Old November 2nd, 2009, 11:43 PM posted to microsoft.public.access.forms
anso[_2_]
external usenet poster
 
Posts: 1
Default Cascading Combo Boxes - How to deal with one being empty?


"Marc T" wrote in message
...
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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:33 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.