ComboBox is null therefore all records show

[melanie]

Good day

Please can someone assist me with this combo box issue?

Main form which has a set of two combo boxes. (No 1 is Bank and No 2 is Branch).

Second one relies on the first for its records. (Select Bank first then Select the Branch pertaining to that Bank).


The subform is linked to main form by BankID. BranchId is a where statement on the query feeding the subform.

The subform relies on its records on the selection of first and second combobox...all fine so far, works fine....but I want to be able to view all records based on first combobox (bank) if the bank selected does not have a branch in particular or receives its invoices directly. (Combo2 is Null)

I have found some code on the forum and have played with it, but I am clearly doing something wrong. Below is the code I found:

SELECT State Aid Projects.*

FROM State Aid Projects

WHERE (([County]=Forms!YourFormName!YourComboBoxName Or Forms!YourFormName!YourComboBoxName Is Null)=True)

ORDER BY (whatever order you wanted the data in);The query behind what I described would look something like the above. In this case, if you selected a specific county, the report would return only that county, but if you didn't choose a county (left the combo blank) it would list ALL the counties.
__________________

So, in essence, I'm trying to achieve the above, no record selected in Combo2 (Branch) then give me all the records in my subform pertaining to the Banks, exclude the branch from the equasion.

Any assistance would be gratefully accepted.

Melanie

 

[mhartman]

Hello:

It sounds like you want a cascading combo box effect. Please see the below link to Candace Tripp's site. She has a downloadable example of how to do this.

Regards
Mark

http://www.candace-tripp.com/pages/access_downloads.aspx

 

[melanie]

Mark

Thank you so much for responding. Am looking the site now. I have got them cascading, the problem is, sometimes I just want to use the value from the first combobox and for the query to ignore the second combo and give me <All> the records. Going to look at Candice's examples.

Many thanks
Melanie

 

[melanie]

Mark

I think you have pointed me to the answer, will give it a try and let you know.


From the bottom of my heart, thank you
Melanie

 

[mhartman]

Hello

Your welcome and good luck with this. Post a sample if your having trouble. There is a lot of good folks here.

Regards
Mark

 

[melanie]

and you are one of them!!

I used just a piece of Candice's code and modified...used an option box with the if statement to get a PERFECT result:

Private Sub Option17_AfterUpdate()
If Me.Option17 = True Then

Me.SelectforInvoicing.LinkChildFields = ""
Me.SelectforInvoicing.LinkMasterFields = ""

Me.SelectforInvoicing.LinkChildFields = "BANKID"
Me.SelectforInvoicing.LinkMasterFields = "BANK"


Else
If Me.Option17 = False Then
Me.SelectforInvoicing.LinkChildFields = ""
Me.SelectforInvoicing.LinkMasterFields = ""

Me.SelectforInvoicing.LinkChildFields = "BankID;CostCentre"
Me.SelectforInvoicing.LinkMasterFields = "Bankid;CostCentre"
End If
End If

End Sub

Mark, because of you, I can finally get some sleep, now 2:30am my side. I am so relieved to have an answer, I wasn't going to give up until it was resolved.

kindest kindest regards
Melanie

 

[mhartman]

Hello

Glad you got it working, and good luck on future projects.

Regards
Mark

 

[melanie]

Thank you, Mark, I would like my life back though........(future projects: Kill me first, ok!!)

 

[johnsqftwr]

Table Fields

I too have found Candace's examples very useful, however the example usese a table with:
RowField = Text
ColumnField = Text
NumericField = Number

I need to have the RowField = Date/Time but I can't get it to work.

Any help greatly appreciated.


John

 

[utterangel]

Option Compare Database
Option Explicit

Private m_strShortDateFormat As String

Private Sub cboColumnField_AfterUpdate()

    Dim strSQL As String
    Dim strSQLSF As String
        
    cboNumericField = Null
    
    
    strSQL = " SELECT DISTINCT tblDemo.NumericField FROM tblDemo "
    strSQL = strSQL & " WHERE Format(tblDemo.RowField, '" & m_strShortDateFormat & "') = #" & cboRowField & "# And  "
    strSQL = strSQL & " tblDemo.ColumnField = '" & cboColumnField & "'"
    strSQL = strSQL & " ORDER BY tblDemo.NumericField;"
    
    cboNumericField.RowSource = strSQL
    
    strSQLSF = " SELECT * FROM tblDemo "
    strSQLSF = strSQLSF & " WHERE Format(tblDemo.RowField, '" & m_strShortDateFormat & "') = #" & cboRowField & "# And  "
    strSQLSF = strSQLSF & " tblDemo.ColumnField = '" & cboColumnField & "'"
          
    
    Me!sfrmForm.LinkChildFields = ""
    Me!sfrmForm.LinkMasterFields = ""
     
    Me!sfrmForm.LinkChildFields = "RowField;ColumnField"
    Me!sfrmForm.LinkMasterFields = "RowField;ColumnField"
    Me.RecordSource = strSQLSF
    Me.Requery

End Sub

Private Sub cboNumericField_AfterUpdate()

    Dim strSQLSF As String
    
    strSQLSF = " SELECT * FROM tblDemo "
    strSQLSF = strSQLSF & " WHERE Format(tblDemo.RowField, '" & m_strShortDateFormat & "') = #" & cboRowField & "# And  "
    strSQLSF = strSQLSF & " tblDemo.ColumnField = '" & cboColumnField & "' And "
    strSQLSF = strSQLSF & " tblDemo.NumericField = " & cboNumericField
    
    Me!sfrmForm.LinkChildFields = ""
    Me!sfrmForm.LinkMasterFields = ""
     
    Me!sfrmForm.LinkChildFields = "RowField;ColumnField;NumericField"
    Me!sfrmForm.LinkMasterFields = "RowField;ColumnField;NumericField"
    Me.RecordSource = strSQLSF
    Me.Requery
    
End Sub

Private Sub cboRowField_AfterUpdate()

     Dim strSQL As String
     Dim strSQLSF As String
          
     cboColumnField = Null
     cboNumericField = Null
     
     strSQL = "SELECT DISTINCT tblDemo.ColumnField FROM tblDemo "
     strSQL = strSQL & " WHERE Format(tblDemo.RowField, '" & m_strShortDateFormat & "') = #" & cboRowField & "#"
     strSQL = strSQL & " ORDER BY tblDemo.ColumnField;"
     
     cboColumnField.RowSource = strSQL
               
     strSQLSF = "SELECT * FROM tblDemo "
     strSQLSF = strSQLSF & " WHERE Format(tblDemo.RowField, '" & m_strShortDateFormat & "') = #" & cboRowField & "#"
          
     Me!sfrmForm.LinkChildFields = "RowField"
     Me!sfrmForm.LinkMasterFields = "RowField"
     Me.RecordSource = strSQLSF
     Me.Requery
          
End Sub

Private Sub cmdShowAll_Click()
On Error GoTo err_cmdShowAll_Click

     cboRowField = Null
     cboColumnField = Null
     cboNumericField = Null
     Me!sfrmForm.LinkChildFields = ""
     Me!sfrmForm.LinkMasterFields = ""
     Me.RecordSource = "tblDemo"
     Me.Requery
          
exit_cmdShowAll_Click:
    Exit Sub
    
err_cmdShowAll_Click:
    MsgBox Err.Description
    Resume exit_cmdShowAll_Click
    
End Sub

Private Sub Form_Open(Cancel As Integer)

    Dim strSQL As String
    
    strSQL = " SELECT DISTINCT tblDemo.RowField FROM tblDemo ORDER BY tblDemo.RowField"
    cboRowField.RowSource = strSQL
    
    m_strShortDateFormat = "m/d/yyyy"

End Sub