Combo Box filter

[Guest]

I have a form where there are a number of preloaded buildings and also their
respective rooms. I want to be able to select, from a combo box, the
building number and instead of entering that as data on the table, it will
actually filter for all the records of that building. After that, I want
only the rooms that correspond to that particular building show up in the
room drop down. Finally once the room is selected, the form filters down to
the exact record corresponding to the building and room. I am still very new
at access and coding so I appreciate simple stupid responses.

 

[Marshall Barton]

I have a form where there are a number of preloaded buildings and also their
respective rooms. I want to be able to select, from a combo box, the
building number and instead of entering that as data on the table, it will
actually filter for all the records of that building. After that, I want
only the rooms that correspond to that particular building show up in the
room drop down. Finally once the room is selected, the form filters down to
the exact record corresponding to the building and room. I am still very new
at access and coding so I appreciate simple stupid responses.

Here's an example of how to do that.
http://www.mvps.org/access/forms/frm0028.htm

 

[Al Campagna]

B,
This is an often asked question, so a Google Groups search on Access NGs would yield
many solutions.
Try "public.access" "cascading combos" to start.
On my website Access Tips below, I have a small sample (A97 or A2003) file called
Synched Combos, that shows how to limit a 2nd combo according to the value of the 1st
combo.

 

[Guest]

I was able to use this code to make the cascading combo boxes so that I can
choose a building and then it filters to only the rooms associated to that
building. The problem, however, is that it isn't filtering but simply
changing the record I am on. I want it to find the appropriate records that
correspond to whatever building and room is chosen. Is there a way to use
this same code to perform this function or is there a better solution all
together?

Example:
building 100 has five rooms (1-5)
record # 45 is associated to building 100 / room 5
If I select building 100 I can then scroll through the records for rooms 1-5
If I select building 100 and room 5 I am directed to record # 45

 

[Al Campagna]

I was able to use this code to make the cascading combo boxes so that I can
choose a building and then it filters to only the rooms associated to that
building. The problem, however, is that it isn't filtering but simply
changing the record I am on. I want it to find the appropriate records that
correspond to whatever building and room is chosen. Is there a way to use
this same code to perform this function or is there a better solution all
together?

Example:
building 100 has five rooms (1-5)
record # 45 is associated to building 100 / room 5
If I select building 100 I can then scroll through the records for rooms 1-5
If I select building 100 and room 5 I am directed to record # 45

 

[Marshall Barton]

The code to filter the form based on the selected values in
the combo box(es?) is very similar to the code to filter the
room combo box. The only real difference is that you need
to set the form's RecordSource property instead of the combo
box's RwoSource.

It sounds like you have some (probably wizard generate)
navigation code in a combo box that needs to be removed.

If you need further assistance with this, please post a
Copy/Paste of the code in the two combo boxes' AfterUpdate
event/

 

[Guest]

Well, I currently am set up so that my room row source is being pulled from a
query that looks at the building number I put in on the form. I am not sure
where I set the RecordSource property, I am very new at this so please excuse
my ignorance. I can send you a zipped file of my mdb. it isn't very big and
it has very limited data

The code to filter the form based on the selected values in
the combo box(es?) is very similar to the code to filter the
room combo box. The only real difference is that you need
to set the form's RecordSource property instead of the combo
box's RwoSource.

It sounds like you have some (probably wizard generate)
navigation code in a combo box that needs to be removed.

If you need further assistance with this, please post a
Copy/Paste of the code in the two combo boxes' AfterUpdate
event/
--
Marsh
MVP [MS Access]

I was able to use this code to make the cascading combo boxes so that I can
choose a building and then it filters to only the rooms associated to that
building. The problem, however, is that it isn't filtering but simply
changing the record I am on. I want it to find the appropriate records that
correspond to whatever building and room is chosen. Is there a way to use
this same code to perform this function or is there a better solution all
together?

Example:
building 100 has five rooms (1-5)
record # 45 is associated to building 100 / room 5
If I select building 100 I can then scroll through the records for rooms 1-5
If I select building 100 and room 5 I am directed to record # 45

 

[Marshall Barton]

Change the query to use the room combo box the same way it
uses the building combo box.

The example I pointed you to also included how to set the
RowSource to an SQL statement.

While I prefer setting the RowSource, either way should do
what you want for this situation.

Please do not send or post any files Instead, just post a
Copy/Paste of your query's SQL statement and the combo
boxes' AfterUpdate event procedures.