Filtering Combo Boxes (1 Viewer)

mdex · Oct 16, 2018

I have a form with 3 combo boxes (TeamNamecbo, CustomerNamecbo and AccountTypecbo).

TeamNamecbo just lists all the teams from Teamtbl

CustomerNamecbo is filtered (for want of a better word) to show only the customers that TeamNamecbo supports using the following.

Code:

SELECT distinct Accounts.ID, accounts.CustomerName, Accounts.PasswordRequirements FROM accounts Where (((accounts.TeamName)=[forms]![Checks]![TeamNamecbo])) UNION select distinct null, null, null FROM accounts
ORDER BY accounts.CustomerName;

PasswordRequirements is used to populate a text box on the form.

The 3rd, AccountTypecbo I want to filter on both TeamNamecbo and CustomerNamecbo. I have tried amending the above code logically but can't get it to display.

Code:

SELECT distinct accounts.ID, accounts.AccountType FROM accounts WHERE (((accounts.TeamName)=[forms]![Checks]![TeamNamecbo])) and (((accounts.CustomerName)=[forms]![Checks]![CustomerNamecbo])) UNION select distinct null, null
ORDER BY accounts.accounttype;

I'm admittedly not very good at writing my own code and usually have to follow multiple different examples or bastardise other peoples code

Could anyone please give any advice as to why the statement on the 3rd combo box is failing or suggest a better way?

isladogs · Oct 16, 2018

Why do you have the UNION select distinct null, null section in the combo row source(s). I can't see why it is included or what it does

mdex · Oct 16, 2018

isladogs said:
Why do you have the UNION select distinct null, null section in the combo row source(s). I can't see why it is included or what it does

I'm not very good at keeping my sources so I can't remember where I found this piece of code I'm afraid.

Whatever it does it works for the CustomerNamecbo.

isladogs · Oct 16, 2018

What the UNION null part does is add an extra blank row at the top of your combo. Why do you want that?

You didn't explain what you meant by 'I couldn't get it to display' but this may well be the issue here
Suggest you remove that section of the code from each combo row source.

Also if you have 3 cascading combos, each should only refer to the one before. Try only putting the 2nd form value in the criteria for the 3rd

arnelgp · Oct 16, 2018

what you need is VBA to filter the last combo.
add this to the Form's vba:

Code:

Public Function fncAccountTypeFilter()
	Dim strFilter As String
	If Me.TeamNamecbo.ListIndex <> -1 Then _
		strFilter = "TeamName = """ & Me.TeamNamecbo & """"
	
	If Me.CustomerNamecbo.ListIndex <> -1 Then
		If Len(strFilter) > 0 Then
			strFilter = strFilter & " And CustomerName = """ & Me.CustomerNamecbo & """"
		
		Else
			strFilter = "CustomerName = """ & Me.CustomerNamecbo & """"
		End If
	End If
	
	If Len(strFilter) <> 0 Then
		Me.AccountTypecbo="SELECT distinct accounts.ID, accounts.AccountType FROM accounts " & _
			"WHERE " & strFilter
	
	Else
		Me.AccountTypecbo="SELECT distinct accounts.ID, accounts.AccountType FROM accounts " & _
			"WHERE (1=0)"
	End If
        Me.AccountTypecbo.Requery
End If

change the rowsource of AccountTypecbo combobox first to:

Code:

SELECT distinct accounts.ID, accounts.AccountType FROM accounts WHERE (1=0)

this will ensure that the combo is blank.

on the AfterUpdate Events of TeamNamecbo and CustomerNamecbo combos, put this:

Code:

=fncAccountTypeFilter()

mdex · Oct 16, 2018

isladogs said:
What the UNION null part does is add an extra blank row at the top of your combo. Why do you want that?

That makes sense now. I don't want that. Have removed it from the 2nd combo box code and that still works fine.

Code:

SELECT DISTINCT accounts.ID, accounts.CustomerName, accounts.PasswordRequirements
FROM accounts
WHERE (((accounts.TeamName)=[forms]![Checks]![TeamNamecbo]))
ORDER BY accounts.CustomerName;

isladogs said:
You didn't explain what you meant by 'I couldn't get it to display' but this may well be the issue here
Suggest you remove that section of the code from each combo row source.

The drop down opens but the combo box isn't populated. I thought it may be a bound column issue but pulling my hair out as I can't get it to work. Bet it's something so simple

This is the code in the 3rd combo box now.

Code:

SELECT DISTINCT accounts.ID, accounts.AccountType
FROM accounts
WHERE (((accounts.CustomerName)=[forms]![Checks]![CustomerNamecbo]))
ORDER BY accounts.AccountType;

isladogs said:
Also if you have 3 cascading combos, each should only refer to the one before. Try only putting the 2nd form value in the criteria for the 3rd

If that's how I need to work it so be it. In my environment Team A can work with Customer 1 using account types X and Y. Team B may also work with Customer 1 but only using account type X which is why I wanted to filter on both Team and Customer.

isladogs · Oct 16, 2018

Reading your last sentence perhaps you do after all need both forms referenced in the third combo.
Hard to tell from here

I expect its something simple as well. Have you tried arnel's code?

If you can't figure it out, suggest you provide a stripped down copy of your db with the relevant forms & tables/queries need for it to work

mdex · Oct 16, 2018

arnelgp said:
what you need is VBA to filter the last combo.
add this to the Form's vba:

I get a compile error: End If without block If

mdex · Oct 16, 2018

isladogs said:
If you can't figure it out, suggest you provide a stripped down copy of your db with the relevant forms & tables/queries need for it to work

Thanks Isladogs, I'll have a play for the rest of the day and upload the db tomorrow if needed.

mdex · Oct 16, 2018

mdex said:
I get a compile error: End If without block If

Thanks Arnel,

I think the End If at the end was meant to be End Function?

The AccountType combo is still blank though.

I'll upload a copy of the DB.

EDIT:

Arnels code is giving a value but it isn't visible in the combo box. When I check the datasheet view of the table the form is servicing the following is in the AccountType field.

SELECT distinct accounts.ID, accounts.AccountType FROM accounts WHERE TeamName = "2" And CustomerName = "4"

arnelgp · Oct 16, 2018

id numeric the result you should use TeamId or code and CustomerId Or code.

isladogs · Oct 16, 2018

Aha.
Your combo boxes have a number field as the bound column. It's probably a hidden ID field in each case.
Do one of the of the following
1. Change the expression to use the bound ID field for each combo and remove the text delimiters if it's included
OR
2. Modify to use the correct column where the numbering starts at zero. So the 2nd Columbia Column(1). This won't work in a query
OR
3. Change the bound column to the one you want to use

I'd suggest the first method

mdex · Oct 17, 2018

I'm really appreciative of all the help being provided.

Ok, so I've really screwed something up here.

I'd initially used lookups on tables but read that's not a sensible way of doing things so have removed those and used just the form combos to handle the entries. I've also amended 2 input forms (Teams and Accounts) to enter the text values into the tables rather than ID's.

Now the Checks form suggestions you've both helped with don't work. The row source statements that were working now don't. I've attached the DB now.

mdex · Oct 19, 2018

Thanks arnelgp and isladogs for your help so far.

I've done a bit of reading in the last 48 hours and 'fixed' some mistakes I made building my tables. I'm now using proper foreign keys and don't have any lookups in my tables.

Tables:

AccountType - ID, AccountType
Customer - ID, CustomerName
Teams - ID, Team Name

Accounts - ID, TeamID, CustomerID, AccountTypeID
Checks - ID, TeamID, CustomerID, AccountTypeID

There are other fields but at don't think their relevant to this query.

I'm back to having the 2nd combo box fitering on the entry from combo box 1.

Code:

SELECT DISTINCT Accounts.CustomerID, Accounts.TeamID, Customer.CustomerName
FROM Teams INNER JOIN (Customer INNER JOIN Accounts ON Customer.ID = Accounts.CustomerID) ON Teams.ID = Accounts.TeamID
WHERE (((Accounts.TeamID)=[Forms]![Checks]![TeamIDcbo]));

I've also tried amending the code from Arnelgp to query the 3rd combo using vba but get a 'Subscript out of range' error when selecting an option on the first combo box. My combos are called TeamIDcbo, CustomerIDcbo and AccountTypeIDcbo.

Code:

Public Function fncAccountTypeFilter()

    Dim strFilter As String
    If Me.TeamIDcbo.ListIndex <> -1 Then _
        strFilter = "TeamID = """ & Me.TeamIDcbo & """"
    
    If Me.CustomerIDcbo.ListIndex <> -1 Then
        If Len(strFilter) > 0 Then
            strFilter = strFilter & " And CustomerID = """ & Me.CustomerIDcbo & """"
        
        Else
            strFilter = "CustomerID = """ & Me.CustomerIDcbo & """"
        End If
    End If
    
    If Len(strFilter) <> 0 Then
        Me.AccountIDcbo = "SELECT distinct accounts.ID, accounts.AccountTypeID FROM accounts " & _
            "WHERE " & strFilter
    
    Else
        Me.AccountIDcbo = "SELECT distinct accounts.ID, accounts.AccountTypeID FROM accounts " & _
            "WHERE (1=0)"
    End If
        Me.AccountIDcbo.Requery
        
End Function

I appreciate you've helped me a lot so far and I promise I have tried to resolve this myself. Any advice appreciated.

isladogs · Oct 19, 2018

Is the file in post 13 the latest version of your database? If not please post that.

Also please tell us what to click to see what you are describing

mdex · Oct 19, 2018

isladogs said:
Is the file in post 13 the latest version of your database? If not please post that.

Also please tell us what to click to see what you are describing

Latest DB attached.

From switchboard click Perform Password Check button. This opens the form 'Checks'.

Ignore everything other than the 3 combos at the top. TeamID shows a list of all TeamName from Teams table. CustomerID is filtered depening on the entry in TeamID. AccountTypeID should in theory filter using arnelp's code.

=fncAccountTypeFilter() is in the after update event for both TeamIDcbo and CustomerIDcbo but this seems to be giving me the 'subscript out of range' error.

Here is the amended code it calls.

Code:

Public Function fncAccountTypeFilter()

    Dim strFilter As String
    If Me.TeamIDcbo.ListIndex <> -1 Then _
        strFilter = "TeamID = """ & Me.TeamIDcbo & """"
    
    If Me.CustomerIDcbo.ListIndex <> -1 Then
        If Len(strFilter) > 0 Then
            strFilter = strFilter & " And CustomerID = """ & Me.CustomerIDcbo & """"
        
        Else
            strFilter = "CustomerID = """ & Me.CustomerIDcbo & """"
        End If
    End If
    
    If Len(strFilter) <> 0 Then
        Me.AccountTypeIDcbo = "SELECT distinct accounts.ID, accounts.AccountTypeID FROM accounts " & _
            "WHERE " & strFilter
    
    Else
        Me.AccountTypeIDcbo = "SELECT distinct accounts.ID, accounts.AccountTypeID FROM accounts " & _
            "WHERE (1=0)"
    End If
        Me.AccountTypeIDcbo.Requery
        
End Function

isladogs · Oct 19, 2018

@mdex
You had managed to get this into a bit of a mess.
If it was my form I would have done this rather differently
As it was I had to make several alterations to make it work

For example:
- You were using text delimiters for number fields
- bound column for Customer combo was 2 - should have been 1

and various other small things that all combined to make this take a while to fix ... which I believe it is now

I've removed the Teams table from the rowsource for combo 2
I've cleared the startup row source for combo 3 so its blank on form load (was showing 0)
I've also removed the AccountID field from combo3 row source code to prevent unwanted duplicated values
I've also removed the Change event & put all code in AfterUpdate

Next I've added Option Explicit at the top of each code module - ALWAYS DO THIS! When I compiled there was were several errors in the Accounts form & one in Switchboard. I've fixed all these and left comments

With apologies to arnelgp, I've altered some of his code in the above changes.
Arnel would have done it just as well if he had the 'benefit' of your database to work with but each of us has a slightly different style of coding

Test it & check it gives the expected results
Good luck with the rest of it

Oh yes, I also changed it to overlapping documents in Access options.
You may want to change that back to tabs if you prefer them!

mdex · Oct 22, 2018

Thanks isladogs.

Appreciate the help. I'll read through all the changes you've made and find some reading materials to familiarise myself as to why.

Filtering Combo Boxes (1 Viewer)

Registered User.

MVP / VIP

Registered User.

MVP / VIP

..forever waiting... waiting for jellybean!

Registered User.

MVP / VIP

Registered User.

Registered User.

Registered User.

..forever waiting... waiting for jellybean!

MVP / VIP

Registered User.

Attachments

Registered User.

MVP / VIP

Registered User.

Attachments

MVP / VIP

Attachments

Registered User.

Similar threads

Users who are viewing this thread