Linked combo box, but no selection in previous combo box

[Andrew Fergus]

Hi everyone, another Access question....

I have a combo box on a form where the contents that are displayed are set by the options selected by the user in 2 other combo boxes. This is working fine. But how do I alter the code behind the combo box to still show all records where nothing has been selected in either, or both, of the other combo boxes?

Thanks in advance
Andrew

 

[meloncolly]

unless we know the code you have used for the third box we can't really help, since there are several ways you COULD have done it.

for example, you might have the row source list this for combo 3

select field1, field2 from mytable where
field3 = forms!myform!combo1 AND
field4 = forms!myform!combo2

and this could be built right into the combo3 or you could be using code in the after update event of combo1 and combo2 that requeries combo3 or ...etc etc

 

[Andrew Fergus]

Hi Colm

Thanks for taking an interest in this. The code for the 'row source' property in the 3rd combo box is this :

SELECT Format([qryInvoices].[Invoice],"#0"),Format( [qryInvoices].[Date],"dd/mm/yy"), [qryInvoices].[Client_Name], [qryInvoices].[Unique_ID],  [qryInvoices].[Entity_Name],[qryInvoices].[Entity_ID_Link], [qryInvoices].[Client_Code_Link] FROM [qryInvoices] WHERE ((([qryInvoices].[Entity_ID_Link])=[Entity_ID]) AND (([qryInvoices].[Client_Code_Link])=[Client_Code])) ORDER BY [Client_Name], [Invoice];

The 'Entity_ID' field is the name of the 1st combo box and 'Client_Code' is the name of the 2nd combo box. I have used almost identical code (see below) in the After_Update properties of the first 2 combo boxes. Here is an example of the After_Update event :

Private Sub Entity_ID_AfterUpdate()
Dim str1 As String, str2 As String
 str1 = "'#0'"
 str2 = "'dd/mm/yy'"
 Me.Select_Invoice = Null
 Me.Select_Invoice.RowSource = "SELECT Format([qryInvoices].[Invoice]," & str1 & "),Format( [qryInvoices].[Date]," & str2 & "), [qryInvoices].[Client_Name], [qryInvoices].[Unique_ID], [qryInvoices].[Entity_ID_Link], [qryInvoices].[Client_Code_Link] FROM [qryInvoices] WHERE ((([qryInvoices].[Entity_ID_Link])=[Entity_ID]) AND (([qryInvoices].[Client_Code_Link])=[Client_Code])) ORDER BY [Client_Name], [Invoice];"
End Sub

Where 'Select_Invoice' is the name of the 3rd combo box.

How would the code be modified to allow the user to not select either an entity or a client but the 3rd box still shows records?

Thanks
Andrew

 

[Andrew Fergus]

any thoughts?
A

 

[SydneyGeek]

Andrew sent me a copy of his database and I came up with this solution. It has the advantage that it's extensible -- you can have as many upstream combos as you like. Everything points to one function, which makes code maintenance straightforward. Here it is...

'Declarations at top of module
'initialise SQL strings -- these will be used to build the final rowsource for Select_Invoice
Dim SQL_WHERE As String
Const SQL1 = "SELECT Format([qryInvoices].[Invoice],""0"") AS Expr1, qryInvoices.Date, qryInvoices.Client_Name, qryInvoices.Unique_ID, qryInvoices.Entity_Name, qryInvoices.Entity_ID_Link, qryInvoices.Client_Code_Link FROM qryInvoices "
Const SQL2 = "ORDER BY qryInvoices.Invoice DESC;"

'the AfterUpdate events for the 2 combos
'Select_invoice is the final, downsream combo box -- the one whose RowSource you need to adjust

Private Sub Entity_ID_AfterUpdate()
    Me.Select_Invoice = Null
    BuildSQL
End Sub

Private Sub Client_Code_AfterUpdate()
    Me.Select_Invoice = Null
    BuildSQL
End Sub

'the Form_Open event
Private Sub Form_Open(Cancel As Integer)
SQL_WHERE = ""
Me.Select_Invoice.RowSource = SQL1 & SQL_WHERE & SQL2 'build final string
Me.Select_Invoice = Null
End Sub

'the Select_Invoice_Click event
Private Sub Select_Invoice_Click()
BuildSQL
End Sub

'the BuildSQL function that does the processing
Function BuildSQL()
    'check to see whether either or both upstream controls are null
    'based on their status, build the row source for Select_Invoice
    SQL_WHERE = ""
    If IsNull(Me.Entity_ID) Then
        'do nothing
        SQL_WHERE = SQL_WHERE
    Else 'item selected
        SQL_WHERE = "WHERE qryInvoices.Entity_ID_Link = [Entity_ID] "
    End If
    If IsNull(Me.Client_Code) Then
        'do nothing
        SQL_WHERE = SQL_WHERE
    Else 'item selected
        If Not IsNull(Me.Entity_ID) Then 'two items selected
            SQL_WHERE = SQL_WHERE & "AND qryInvoices.Client_Code_Link = [Client_Code] "
        Else 'only client selected
            SQL_WHERE = "WHERE qryInvoices.Client_Code_Link = [Client_Code] "
        End If
    End If
    Me.Select_Invoice.RowSource = SQL1 & SQL_WHERE & SQL2 'build final string
    Me.Select_Invoice.Requery
    Debug.Print Me.Select_Invoice.RowSource
End Function

Once you are happy that everything is working for you, you can remove the final Debug.Print statement

Denis

 

[Andrew Fergus]

Thank you very very much for your help Denis. This works perfectly. I owe you one.