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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|