Filtered combobox (1 Viewer)

Chumpalot

Registered User.
Local time
Today, 19:52
Joined
Mar 12, 2015
Messages
76
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:52
Joined
Oct 29, 2018
Messages
21,358
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.
 

Chumpalot

Registered User.
Local time
Today, 19:52
Joined
Mar 12, 2015
Messages
76
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:52
Joined
Oct 29, 2018
Messages
21,358
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
 

Chumpalot

Registered User.
Local time
Today, 19:52
Joined
Mar 12, 2015
Messages
76
This article explains things more clearer than I can and offers a solution for guest types associated to only one country category.
 

Chumpalot

Registered User.
Local time
Today, 19:52
Joined
Mar 12, 2015
Messages
76
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:52
Joined
Oct 29, 2018
Messages
21,358
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.
 

Chumpalot

Registered User.
Local time
Today, 19:52
Joined
Mar 12, 2015
Messages
76
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

Registered User.
Local time
Today, 19:52
Joined
Mar 12, 2015
Messages
76
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

Registered User.
Local time
Today, 19:52
Joined
Mar 12, 2015
Messages
76
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 :)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:52
Joined
Oct 29, 2018
Messages
21,358
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.
 

Users who are viewing this thread

Top Bottom