Filtering Data Using Combo Boxes

[07sanchez]

Hi all,

I know im being an idoit, but I have tried using the results from searching but I cant get this problem to work and i'm going crazy....

Got a project to do for a University assignment but it is driving me crazy.

I'm doing a course booking database and I have a form where the user is asked to select a location to hold the course, then from that pick a room based on which location they choose.

I have the form working however I can't limit their choice of rooms to what location they have chosen, it simply shows all rooms, but gives an error if you pick a room which doesnt belong to that location.

Can anyone help

Thanks in advance

My Data is (from Room & Location):

ROOM
Room ID (PK)
Location ID(FK)
Room Name
Room Occupancy

LOCATION
Location ID (PK)
Location Name
Location Add1
Location Add2
Location Town
Location Postcode

 

[jsv2002]

You need to cascade update the second combo box if I am reading what you want correctly something like:

Me.DepartmentID.Enabled = True

Dim sDepartmentSource As String

sDepartmentSource = "SELECT [tblDepartments].[DepartmentID], [tblDepartments].[SiteID], [tblDepartments].[Department] " & _
"FROM tblDepartments " & _
"WHERE [SiteID] = " & Me.SiteID.Value

Me.DepartmentID.RowSource = sDepartmentSource
Me.DepartmentID.Requery

This was what I used to do something similar using sites & departments, the departments table has a filed siteID which is a forighn key from tblsites to identify which departments are allocated to which sites.

For data entry purposes on set up I had a main form Site with a subform in datasheet few to enter departments.

The above code was then behind the afterupdate event procedure of the combo box to select sites.

Hope it points you in the right direction if not can post an example later today

regards John

 

[07sanchez]

Hi John,

Many thanks for your reply and help.

Unfortunately im struggling as im not VB trained (In fact i've never used it). I have tried rewriting the script but failed superbly

Like I say, these are my two tables

LOCATION
Location ID (PK)
Location Name
Location Add1
Location Add2
Location Town
Location Postcode

ROOM
Room ID (PK)
Location ID(FK)
Room Name
Room Occupancy

I'm attempting to limit the rooms combo box to only rooms associated with the Location specified in combo box one.

Anymore hlpe would be appreciated.

Thanks

 

[jsv2002]

Hi again,

It would be easier if you zipped up the data base and posted as I have no idea what type of form/table this information is being used in or what your field names on that form are...

Have a look at the attached the code is in the event procedures of the main form I stripped it out of a COSHH database I'm working on so don't be surprised if any error messages say "COSSH Assessment".... you are looking at Form1 which should open on startup.

good luck any questions post back John

 

[07sanchez]

Hi again,

It would be easier if you zipped up the data base and posted as I have no idea what type of form/table this information is being used in or what your field names on that form are...

Have a look at the attached the code is in the event procedures of the main form I stripped it out of a COSHH database I'm working on so don't be surprised if any error messages say "COSSH Assessment".... you are looking at Form1 which should open on startup.

good luck any questions post back John

Your an absolute star.

I'm all working