Filter Combo Box

[brback]

Hi, ive already searched the forums for a solution to my problem, but still i
havent been able to solve it. Ive also taken a look at
http://www.mvps.org/access/forms/frm0028.htm

I got 4 tables; Car, Cargroup, Rentaltype, Contract
with these fields:

Car
---------------------
CarNR (Primary Key)
Cargroup
Model
Year

Cargroup
--------------------
Cargroup (Primary Key)
Description

Rentaltype
--------------------
Rentalid (Primary Key)
Cargroup
Rentalprice
Timetype

Contract
---------------------
ContractID (Primary key)
CarNR (related from table car)
Cargroup (related from table cargroup)
Rentalid (related from table rentaltype)

Ive made a query with these fields, and combo boxes of ([Car].[CarNR]) and (
[Rentaltype].[Rentalid]) in a new form. What i want to do is to first set
CarNR, which means [Car].[Cargroup] is set along with it. Then i want to
filter the Rentaltype combo box to only show options where [Rentaltype].
[Cargroup] = [Car].[Cargroup]

This is what ive made out of the CarNR_Afterdupate, combo box:

Private Sub CarNR_AfterUpdate()
Dim strSQL As String
strSQL = "Select " & Me!Cargroup
strSQL = strSQL & " from Rentaltype"
Me!Rentalid.RowSourceType = "Table/Query"
Me!Rentalid.RowSource = strSQL
End Sub

I'm able to set CarNR first, but when i try to pick Rentalid im told to set a
parametervalue for what ever value [Car].[Cargroup] may have. In other words
i cant make it work.

I'm pretty new to the forums so if you have any troubles understanding what
im trying to state please say so.
Thanks alot in advance to any help or tips

 

[tina]

each car in the Car table is already assigned to a specific car group. why
are you storing that data again in the Contract table? do specific cars get
moved from one car group to another, over time? if yes, then it makes sense
to store the point-in-time data in the Car table; but if no, then i'd remove
the Cargroup field from the Contract table.

Ive made a query with these fields

which fields, specifically? and what are you doing with the query - is it
bound to the form? or bound to a combo box control? if so, which one. we
need some clarification on your setup.

hth

Hi, ive already searched the forums for a solution to my problem, but still i
havent been able to solve it. Ive also taken a look at
http://www.mvps.org/access/forms/frm0028.htm

I got 4 tables; Car, Cargroup, Rentaltype, Contract
with these fields:

Car
---------------------
CarNR (Primary Key)
Cargroup
Model
Year

Cargroup
--------------------
Cargroup (Primary Key)
Description

Rentaltype
--------------------
Rentalid (Primary Key)
Cargroup
Rentalprice
Timetype

Contract
---------------------
ContractID (Primary key)
CarNR (related from table car)
Cargroup (related from table cargroup)
Rentalid (related from table rentaltype)

Ive made a query with these fields, and combo boxes of ([Car].[CarNR]) and (
[Rentaltype].[Rentalid]) in a new form. What i want to do is to first set
CarNR, which means [Car].[Cargroup] is set along with it. Then i want to
filter the Rentaltype combo box to only show options where [Rentaltype].
[Cargroup] = [Car].[Cargroup]

This is what ive made out of the CarNR_Afterdupate, combo box:

Private Sub CarNR_AfterUpdate()
Dim strSQL As String
strSQL = "Select " & Me!Cargroup
strSQL = strSQL & " from Rentaltype"
Me!Rentalid.RowSourceType = "Table/Query"
Me!Rentalid.RowSource = strSQL
End Sub

I'm able to set CarNR first, but when i try to pick Rentalid im told to set a
parametervalue for what ever value [Car].[Cargroup] may have. In other words
i cant make it work.

I'm pretty new to the forums so if you have any troubles understanding what
im trying to state please say so.
Thanks alot in advance to any help or tips

 

[brback via AccessMonster.com]

each car in the Car table is already assigned to a specific car group. why
are you storing that data again in the Contract table? do specific cars get
moved from one car group to another, over time? if yes, then it makes sense
to store the point-in-time data in the Car table; but if no, then i'd remove
the Cargroup field from the Contract table.

Ive made a query with these fields

which fields, specifically? and what are you doing with the query - is it
bound to the form? or bound to a combo box control? if so, which one. we
need some clarification on your setup.

hth

Hi, ive already searched the forums for a solution to my problem, but still i
havent been able to solve it. Ive also taken a look at

[quoted text clipped - 52 lines]

im trying to state please say so.
Thanks alot in advance to any help or tips

hi, and thank you for your response. made this post last night when i was
kinda sleepy so made some mistakes, sorry about that. the field cargroup
should not be included in contract. instead the tables Car, Cargroup are
related with each others trough the field cargroup. having these tables a
make a new query:

Rent-out
----------------------
ContractID (From contract)
CarNr (From contract)
Cargroup (From Car)
Description (From Cargroup)
Model (From Car)
Year (From Car)
Rentalid (From Contract)
Cargroup (From Rentaltype)
Rentalprice (From Rentaltype)
Timetype (From Rentaltype)

Having this query i make a new form bound to this. As you see there is two
fields with cargroups in it. In this form i make combo boxes out of CarNR and
Rentalid, which i guess is bound to their tables Car and Rentaltype. Till now
everything works fine, but as i tried to explain earlier i want to be able to
make one of the values from the CarNR combo box determine the rowsource for
Rentalid combo box. When i pick CarNR from the combo box, Car.Cargroup is
chosen along with it, lets say to a value "B". When i now pick the Rentalid,
i only want its combo box to show the options where Rentaltype.Cargroup = B.

Hope this made any more sense. If i still havent been able to explain myself
to you, please say so as i really need to get this figured out. Once more
thanks alot in advance for any help or tips

 

[brback via AccessMonster.com]

I might have figured this out somehow now, but i still need some help
If i set the rowsource on Rentalid like this:

SELECT Rentaltype.Rentalid, Rentaltype.Cargroup, Rentaltype.Time, Rentaltype.
Rentalprice FROM Rentaltype WHERE Rentaltype.group=Forms!myform!car_cargroup;


this only works when the value of [Car_Cargroup] is set when i open the form.
So i guess what i need is to make this take place in the afterupdate event of
the CarNr combo box. How can i do this ?

 

[tina]

just requery the combo box control in the AfterUpdate event of the control
that is used in the combo's RowSource criteria.

hth