Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-10-2019, 01:15 PM   #1
Chumpalot
Newly Registered User
 
Join Date: Mar 2015
Posts: 68
Thanks: 28
Thanked 7 Times in 7 Posts
Chumpalot is on a distinguished road
Filtered combobox

Evening all,

I have a combobox displaying guest types. The row source is from a table called 'tbl_guesttypes'. The values are:

[PK] [GuestType]
1, Royals
2, Heads of State
3, Premiers
4, Governors
5, Heads of Department
6, Ambassadors
7, Prime Ministers

I would like to limit this list depending on the value of a textbox on another form (which is always open and never visible to the user). The value is a country category chosen by the user when navigating through the menus. They are:

European Heads of State
Other Foreign Heads of State
Royal Head of State
Heads of Department
Not invited
etc

So for example, if a user has chosen the European Heads of State category I would like the combobox to only show those guest types pertaining to that category. Say Royals, Prime Ministers and Ambassadors.

If they choose Heads of Department I would like the combobox to be limited to just the Heads of Department guest type. And so on.

I have absolutely no idea how I would structure this in a table (or two tables?) I will continue searching online but if anyone could offer up any help I would really appreciate it.

Thanks in advance.
David

Chumpalot is offline   Reply With Quote
Old 07-10-2019, 01:19 PM   #2
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,601
Thanks: 38
Thanked 875 Times in 858 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Filtered combobox

Hi David. Since, I think, simply using the tbl_guesttypes table alone won't tell us which category each guest type belongs, then you may need to add another table to associate each guest type to their appropriate category. You can then use this new table to limit the list shown on the combobox.
__________________
Just my 2 cents...

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.
theDBguy is online now   Reply With Quote
Old 07-10-2019, 01:27 PM   #3
Chumpalot
Newly Registered User
 
Join Date: Mar 2015
Posts: 68
Thanks: 28
Thanked 7 Times in 7 Posts
Chumpalot is on a distinguished road
Re: Filtered combobox

Hi theDBGuy and thanks for your reply.

Yes, I think so too but my mind has drawn a blank on how to get this done. I know how to get it working if only one guest type relates to one country category but I need multiple guest types to be related to multiple categories. I am just not sure how to structure this in a table.

Chumpalot is offline   Reply With Quote
Old 07-10-2019, 01:28 PM   #4
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,601
Thanks: 38
Thanked 875 Times in 858 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Filtered combobox

Quote:
Originally Posted by Chumpalot View Post
Hi theDBGuy and thanks for your reply.

Yes, I think so too but my mind has drawn a blank on how to get this done. I know how to get it working if only one guest type relates to one country category but I need multiple guest types to be related to multiple categories. I am just not sure how to structure this in a table.
Maybe something like:


tblGuestTypes
GuestTypeID, PK
GuestType


tblCountries
CountryID, PK
Country


tblCountryGuestTypes
CountryGuestType, PK
CountryID, FK
GuestTypeID, FK
__________________
Just my 2 cents...

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.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Chumpalot (07-10-2019)
Old 07-10-2019, 01:29 PM   #5
Chumpalot
Newly Registered User
 
Join Date: Mar 2015
Posts: 68
Thanks: 28
Thanked 7 Times in 7 Posts
Chumpalot is on a distinguished road
Re: Filtered combobox

This article explains things more clearer than I can and offers a solution for guest types associated to only one country category.
Chumpalot is offline   Reply With Quote
Old 07-10-2019, 01:36 PM   #6
Chumpalot
Newly Registered User
 
Join Date: Mar 2015
Posts: 68
Thanks: 28
Thanked 7 Times in 7 Posts
Chumpalot is on a distinguished road
Re: Filtered combobox

Quote:
Originally Posted by theDBguy View Post
Maybe something like:

tblGuestTypes
GuestTypeID, PK
GuestType

tblCountries
CountryID, PK
Country

tblCountryGuestTypes
CountryGuestType, PK
CountryID, FK
GuestTypeID, FK
Would I need to include the country table? Since the individual country data is handled elsewhere. Could something like this work?

tbl_CountryGuestTypes
ID, PK
CountryGuestType, FK
CountryCategoryID, FK

Then my combobox would run off code similar to the following:

------------
Else
.RowSource = "SELECT [GuestTypeID] " & _
"FROM TblCountryGuestTypes " & _
"WHERE [CountryCategoryID]=" & [Forms]![frm_capture]!textbox
End If
---------
Call .Requery
Chumpalot is offline   Reply With Quote
Old 07-10-2019, 01:40 PM   #7
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,601
Thanks: 38
Thanked 875 Times in 858 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Filtered combobox

Quote:
Originally Posted by Chumpalot View Post
Would I need to include the country table? Since the individual country data is handled elsewhere. Could something like this work?

tbl_CountryGuestTypes
ID, PK
CountryGuestType, FK
CountryCategoryID, FK

Then my combobox would run off code similar to the following:

------------
Else
.RowSource = "SELECT [GuestTypeID] " & _
"FROM TblCountryGuestTypes " & _
"WHERE [CountryCategoryID]=" & [Forms]![frm_capture]!textbox
End If
---------
Call .Requery
I think so. Give it a try and let us know how it goes.

__________________
Just my 2 cents...

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.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Chumpalot (07-10-2019)
Old 07-10-2019, 01:43 PM   #8
Chumpalot
Newly Registered User
 
Join Date: Mar 2015
Posts: 68
Thanks: 28
Thanked 7 Times in 7 Posts
Chumpalot is on a distinguished road
Re: Filtered combobox

I'll have to give it a go tomorrow as I only have access to my Macbook right now. Thanks again for your help.
Chumpalot is offline   Reply With Quote
Old 07-10-2019, 01:51 PM   #9
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,601
Thanks: 38
Thanked 875 Times in 858 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Filtered combobox

Quote:
Originally Posted by Chumpalot View Post
I'll have to give it a go tomorrow as I only have access to my Macbook right now. Thanks again for your help.
No problem. Good luck!
__________________
Just my 2 cents...

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.
theDBguy is online now   Reply With Quote
Old 07-11-2019, 02:25 AM   #10
Chumpalot
Newly Registered User
 
Join Date: Mar 2015
Posts: 68
Thanks: 28
Thanked 7 Times in 7 Posts
Chumpalot is on a distinguished road
Re: Filtered combobox

Morning all,

I am close (I think) to getting this working. I am using the following code in the on load event of the form the combo box is on

Code:
Private Sub Form_Load()
Me.cbo_selectcguesttype.RowSource = "SELECT GuestType " & _
"FROM tbl_CountryGuestTypes " & _
"WHERE [CountryCategory] = [Forms]![frm_capture]!TextBox"
End sub
This works to a fashion but only when i put the CountryCategory ID into the first textbox. The combox also only gives me a list of numbers (though they do correspond to what is in tbl_CountryGuestTypes which is good.

I have messed around with the bound columns on the combobox but no changes seem to make a difference.

Any help would be greatly appreciated.
Chumpalot is offline   Reply With Quote
Old 07-11-2019, 02:43 AM   #11
Chumpalot
Newly Registered User
 
Join Date: Mar 2015
Posts: 68
Thanks: 28
Thanked 7 Times in 7 Posts
Chumpalot is on a distinguished road
Re: Filtered combobox

I managed to get this working. I changed both bound columns in tbl_CountryGuestTypes to 2 instead of 1 and now the text values are being pulled through rather than the ID.

I realise it's not best practices to use lookups in a table but this has given me a better understanding on how to structure things going forward.

Thank you again
Chumpalot is offline   Reply With Quote
Old 07-11-2019, 04:28 AM   #12
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,601
Thanks: 38
Thanked 875 Times in 858 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Filtered combobox

Quote:
Originally Posted by Chumpalot View Post
I managed to get this working. I changed both bound columns in tbl_CountryGuestTypes to 2 instead of 1 and now the text values are being pulled through rather than the ID.

I realise it's not best practices to use lookups in a table but this has given me a better understanding on how to structure things going forward.

Thank you again
Hi. Congratulations. Glad to hear you got it sorted out. Good luck with your project.

__________________
Just my 2 cents...

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.
theDBguy is online now   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
Adding to each item only filtered items from combobox jaryszek General 11 09-03-2018 12:43 AM
data entry in form filtered combobox Siegfried Forms 3 11-07-2015 04:07 AM
Combobox filtered on 2 unbound text boxes Geordie2008 Forms 4 04-04-2008 04:27 AM
ComboBox Erratic for Displaying Filtered Data Steve R. Modules & VBA 7 09-25-2007 11:07 AM
Filtered Combobox in Continuous form jonnie_c Forms 2 06-07-2005 07:00 AM




All times are GMT -8. The time now is 12:26 PM.


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

Featured Forum post


Sponsored Links


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