Filter combobox or not

[matty ratafairy]

Hello, on my [BookingDetails] subform I have the following comboboxes:
[Country] which filters
[Schools] which in turn filters
[Course] and [Accom].

[Accom] only includes the accom options available from the selected school
based on the tbl [AccomPrices].
This tbl has fields:
IDAccomPrice
IDSchool (from tbl[Schools])
IDAccomType (From tbl[AccomType] 1=Hostel, 2=Family, 3=Hotel)
IDRoomType (from tbl[RoomType] 1= Single, 2= Double, 3=twin)
IDMealType (from tbl[MealType] 1=FB, 2=HB, 3=No)
AccomPrice

I want to also add the options "Not Selected" (as the default value) and
"Reservation Only" to the [Accom] combobox on the subform but don't know
where to put them as they are not linked to an IDAccomPrice or to one
particular school. Is there some way to have a combobox use these values AND
the results of the filter or do I need to put them in a table soemwhere?

I hope Ive explained well enough. Thanks in advance

 

[Klatuu]

You do this by filtering the Accom combo box on the value in the Schools
combo box. Then in the After Update event of the Schools combo box, you
requery the Accom combo box:

Me.accom.Requery

Now, the unknow is what field the Accom combo box is bound to an what the
row source of the Accom combo box is. You need to identify a value for each
of the options that the bound field will accept and recognize.

Post back with the row source of the Accom combo, the field and data type it
is bound to and I think I can show you how it is done.

 

[matty ratafairy]

Thanks for the quick reply. Here is the info you asked for:

The Accom Combobox is bound to IDAccomPrice from the AccomPrices table.

The RowSource SQL is:

SELECT AccomPrices.IdAccomPrice, AccomType.IDAccomType, RoomType.IDRoomType,
MealType.IDMealType, Schools.IDSchool
FROM Schools INNER JOIN (MealType INNER JOIN (RoomType INNER JOIN (AccomType
INNER JOIN AccomPrices ON AccomType.IDAccomType = AccomPrices.IDAccomPrice ON
RoomType.IDRoomType = AccomPrices.IDRoomType) ON MealType.IDMealType=
AccomPrices.IDMealType) ON Schools.IDSchool = AccomPrices.IDSchool
WHERE
(((Schools.IDSchool)=[Forms]![Clients]![Orders].[Form]![BookingDetails].[Form]![ChooseSchool]));

The DataType is AutoNumber.

I hope that's the info you need. If not I'm more than happy to help you help
me..

 

[Klatuu]

Is that SQL for the form or for the combo box? That is a lot of rows for a
combo box. I don't think you need them all unless you are populating other
controls on your form from the columns in the combo.

Can you clarify this for me?
Is ChooseSchool the name of the combo you use to select a school for the form?

 

[matty ratafairy]

The reason there are so many parameters is that the three parts of the
accommodation option (AccomType, RoomType and MealType) make up the
AccomPrice table. For example Hostel, Single Room, HalfBoard = 200$/ week. I
have all three together as it is quicker than selecting three different
comboboxes. I need to be able to see all three parts to be able to select the
correct option.
I guess if I had three different comboboxes for selecting the AccomPrice
(ChooseAccomType,ChooseRoomType and ChooseMealType) then it would be easier
to get to where I want but it would be a lot slower for the end-user.

Is ChooseSchool the name of the combo you use to select a school for the form?

Yes it is . The underlying table [Schools] is related to the [AccomPrices]
table by the [IdSchool] field.

 

[Klatuu]

Okay, then all you should have to do is to requery the accom combo in the
after upate event of the choose school combo.
--
Dave Hargis, Microsoft Access MVP

The reason there are so many parameters is that the three parts of the
accommodation option (AccomType, RoomType and MealType) make up the
AccomPrice table. For example Hostel, Single Room, HalfBoard = 200$/ week. I
have all three together as it is quicker than selecting three different
comboboxes. I need to be able to see all three parts to be able to select the
correct option.
I guess if I had three different comboboxes for selecting the AccomPrice
(ChooseAccomType,ChooseRoomType and ChooseMealType) then it would be easier
to get to where I want but it would be a lot slower for the end-user.

Is ChooseSchool the name of the combo you use to select a school for the form?

Yes it is . The underlying table [Schools] is related to the [AccomPrices]
table by the [IdSchool] field.

 

[matty ratafairy]

Hello. That doesn't work as the "not required and "Reservation only" options
are not associated with any school. The requery works fine for the options
associated with each school but I need these "global" options to be avaiable
whichever school is selected, alongside those which are filtered for the
selected school.

 

[Klatuu]

Oh, right. I forgot about that.

What you need, then is to modify the query you have for the accom combo box
to make it a Union query that has the "Not Selected" and
"Reservation Only" options included. You will need to define values for
those options that will be stored in the field the accom combo box is bound
to. They should be values that will not be in any of the fields selected in
your query and should be lower in the sort order so they will be at the top
of the list.

 

[matty ratafairy]

Ah-Ha. It's "that" simple then. Thank you so much for your help. Now I've
just got to set up the tables, sort out the query........

 

[Klatuu]

It isn't necessary to have tables for union queries. That is the beauty of
them. They can be based on nothing at all. Here is an example of one I use
in one of my applications:

SELECT DISTINCT Client.ClientID, Client.ClientName FROM (Client LEFT JOIN
Property ON Client.ClientID = Property.PropertyClientID) LEFT JOIN Contract
ON Property.PropertyID = Contract.ContractPropID WHERE
(((Contract.ContractType)="D")) AND Contract.ContractStatus LIKE
IIf(Forms!frmSubMtrInstMgr!cboStatus = "
","*",Forms!frmSubMtrInstMgr!cboStatus) AND ClientName <> "All Clients" ORDER
BY Client.ClientName UNION SELECT " " As Expr1, "[All Clients]" As Expr2 FROM
Client;

Note there is no client with a space as the client ID or the name [All
Clients]. It is just used to present the option to the users.

 

[Douglas J. Steele]

Don't think so, Dave. The ORDER BY needs to be at the end of the SQL, not in
the middle.

SELECT DISTINCT Client.ClientID, Client.ClientName FROM (Client LEFT JOIN
Property ON Client.ClientID = Property.PropertyClientID) LEFT JOIN Contract
ON Property.PropertyID = Contract.ContractPropID WHERE
(((Contract.ContractType)="D")) AND Contract.ContractStatus LIKE
IIf(Forms!frmSubMtrInstMgr!cboStatus = "
","*",Forms!frmSubMtrInstMgr!cboStatus) AND ClientName <> "All Clients"
UNION
SELECT " " As Expr1, "[All Clients]" As Expr2 FROM Client
ORDER BY Client.ClientName

There's also no real need for the "As Expr1" and "As Expr2" in there: the
Union query takes its field names from the first subquery. And an argument
could be made that if Client is a large table, you might want to select a
smaller table to use in the second subquery.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

It isn't necessary to have tables for union queries. That is the beauty
of
them. They can be based on nothing at all. Here is an example of one I
use
in one of my applications:

SELECT DISTINCT Client.ClientID, Client.ClientName FROM (Client LEFT JOIN
Property ON Client.ClientID = Property.PropertyClientID) LEFT JOIN
Contract
ON Property.PropertyID = Contract.ContractPropID WHERE
(((Contract.ContractType)="D")) AND Contract.ContractStatus LIKE
IIf(Forms!frmSubMtrInstMgr!cboStatus = "
","*",Forms!frmSubMtrInstMgr!cboStatus) AND ClientName <> "All Clients"
ORDER
BY Client.ClientName UNION SELECT " " As Expr1, "[All Clients]" As Expr2
FROM
Client;

Note there is no client with a space as the client ID or the name [All
Clients]. It is just used to present the option to the users.

--
Dave Hargis, Microsoft Access MVP

Ah-Ha. It's "that" simple then. Thank you so much for your help. Now I've
just got to set up the tables, sort out the query........

 

[Klatuu]

Well, it is in production as I posted it.
May not be absolutely correct, but it does work as is.
In this case, Client is only 248 rows.

But I do appreciate the input.
--
Dave Hargis, Microsoft Access MVP

Don't think so, Dave. The ORDER BY needs to be at the end of the SQL, not in
the middle.

SELECT DISTINCT Client.ClientID, Client.ClientName FROM (Client LEFT JOIN
Property ON Client.ClientID = Property.PropertyClientID) LEFT JOIN Contract
ON Property.PropertyID = Contract.ContractPropID WHERE
(((Contract.ContractType)="D")) AND Contract.ContractStatus LIKE
IIf(Forms!frmSubMtrInstMgr!cboStatus = "
","*",Forms!frmSubMtrInstMgr!cboStatus) AND ClientName <> "All Clients"
UNION
SELECT " " As Expr1, "[All Clients]" As Expr2 FROM Client
ORDER BY Client.ClientName

There's also no real need for the "As Expr1" and "As Expr2" in there: the
Union query takes its field names from the first subquery. And an argument
could be made that if Client is a large table, you might want to select a
smaller table to use in the second subquery.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

It isn't necessary to have tables for union queries. That is the beauty
of
them. They can be based on nothing at all. Here is an example of one I
use
in one of my applications:

SELECT DISTINCT Client.ClientID, Client.ClientName FROM (Client LEFT JOIN
Property ON Client.ClientID = Property.PropertyClientID) LEFT JOIN
Contract
ON Property.PropertyID = Contract.ContractPropID WHERE
(((Contract.ContractType)="D")) AND Contract.ContractStatus LIKE
IIf(Forms!frmSubMtrInstMgr!cboStatus = "
","*",Forms!frmSubMtrInstMgr!cboStatus) AND ClientName <> "All Clients"
ORDER
BY Client.ClientName UNION SELECT " " As Expr1, "[All Clients]" As Expr2
FROM
Client;

Note there is no client with a space as the client ID or the name [All
Clients]. It is just used to present the option to the users.

--
Dave Hargis, Microsoft Access MVP

Ah-Ha. It's "that" simple then. Thank you so much for your help. Now I've
just got to set up the tables, sort out the query........

:

Oh, right. I forgot about that.

What you need, then is to modify the query you have for the accom combo
box
to make it a Union query that has the "Not Selected" and
"Reservation Only" options included. You will need to define values
for
those options that will be stored in the field the accom combo box is
bound
to. They should be values that will not be in any of the fields
selected in
your query and should be lower in the sort order so they will be at the
top
of the list.
--
Dave Hargis, Microsoft Access MVP


:



Hello. That doesn't work as the "not required and "Reservation only"
options
are not associated with any school. The requery works fine for the
options
associated with each school but I need these "global" options to be
avaiable
whichever school is selected, alongside those which are filtered for
the
selected school.