Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-19-2018, 04:12 AM   #16
mdex
Newly Registered User
 
Join Date: Jul 2013
Posts: 15
Thanks: 4
Thanked 0 Times in 0 Posts
mdex is on a distinguished road
Re: Filtering Combo Boxes

Quote:
Originally Posted by isladogs View Post
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
Attached Files
File Type: accdb PMXP Latest.accdb (1.56 MB, 11 views)

mdex is offline   Reply With Quote
Old 10-19-2018, 02:46 PM   #17
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,928
Thanks: 92
Thanked 1,707 Times in 1,584 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Filtering Combo Boxes

@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!
Attached Files
File Type: zip PMXP Latest - v2.zip (87.4 KB, 6 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
mdex (10-22-2018)
Old 10-22-2018, 05:43 AM   #18
mdex
Newly Registered User
 
Join Date: Jul 2013
Posts: 15
Thanks: 4
Thanked 0 Times in 0 Posts
mdex is on a distinguished road
Re: Filtering Combo Boxes

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.


mdex is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Filtering Combo Boxes kidrobot Forms 4 05-22-2007 07:03 AM
Filtering on Combo boxes sburgess Forms 0 08-28-2006 06:13 AM
Filtering combo boxes neilorourke General 2 02-24-2005 08:46 AM
Filtering using combo boxes Markvand Forms 2 07-13-2004 12:57 PM
Additional Help with Filtering List Boxes with Combo Boxes Knight General 0 12-27-2002 06:57 AM




All times are GMT -8. The time now is 12:02 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World