Cascade combo box selection from another combo box

[willfrank]

I am new to Access; I cannot cascade from Buildings combo box to Rooms combo box. See attached. If anyone can advise, I would much appreciate your help. thanks

[orange]

I have created a copy of your database (changed the name) and

-changed the names of your combos on Form1 since they were named same as fields in your tables
-removed the control source to these combos
-changed the rowsource
-removed the distinct from the SQL
-changed the event in combo cboBuilding from OnChange to AfterUpdate

See if this is what you are looking for. It wasn't clear, but for cascading combos you (typically):
select an entry from combo1, then
in the after Update event, you modify the rowsource of combo2, using the value selected in combo1, then
requery to ensure the modified rowsource is applied.

[willfrank]

I have created a copy of your database (changed the name) and

-changed the names of your combos on Form1 since they were named same as fields in your tables
-removed the control source to these combos
-changed the rowsource
-removed the distinct from the SQL
-changed the event in combo cboBuilding from OnChange to AfterUpdate

See if this is what you are looking for. It wasn't clear, but for cascading combos you (typically):
select an entry from combo1, then
in the after Update event, you modify the rowsource of combo2, using the value selected in combo1, then
requery to ensure the modified rowsource is applied.

thank you so much: perfect: exactly what I am looking for. I appreciate your help...thanks again

[orange]

You are very welcome.
Good luck with your project.

[willfrank]

thanks again for your help last week. It is possible to incorporate the fixed combo box selections to work with my other existing database?

My original database only supports one building; but with the new option (that you provided) I would like to incorporate all 3 buildings.

End user would pick one of the buildings, then pick the appropriate room numbers based on building number and then have, phone number, technician name, and equipment automatically fill vs. my original which you have to select room from the conference room combo.

I also have links to pictures of the conference rooms, currently linked from my computer only, but will eventually have them linked from a shared server location. thanks in advance, if this is possible. See attached:

I have created a copy of your database (changed the name) and

-changed the names of your combos on Form1 since they were named same as fields in your tables
-removed the control source to these combos
-changed the rowsource
-removed the distinct from the SQL
-changed the event in combo cboBuilding from OnChange to AfterUpdate

See if this is what you are looking for. It wasn't clear, but for cascading combos you (typically):
select an entry from combo1, then
in the after Update event, you modify the rowsource of combo2, using the value selected in combo1, then
requery to ensure the modified rowsource is applied.

[orange]

Not enough info.
Make a clear list of the things you want to do.
Identify what things(tables and fields) are required to do it.
Give it a try.
I don't know your buildings. I don't have a link to your c:\ drive so could not see the images, but that is not the big issue.

You have to describe things like you would to child since we don't know your set up.

[willfrank]

Based on my original database: It is a database that I use to indicate incidents that occur in the conference rooms.

1) I want to record a record for each incident.
2) Database needs to display: picture of conference room where incident has taken place.
3) Automatically display, phone number, technician name and room equipment based on Building selection and room number.
4) Additional information manually entered: is: a) date of incident, b) date completed, c) Status, d) Task Title, e) Incident #, f) Equipment Issue, G) Client Name, I) Additional notes
5) Job # Auto number as each record is created
6) Conference room table currently details the possible equipment available in each room.

Ultimately, I am trying to create an incident database that all AV technicians can fill out when a problem occurs: Each record will provide a reference to the problems that have occurred. Currently it is to support 3 buildings, but may increase to six in the future.

Open a new record, select the building associated with the incident, then select available conference rooms based on building selection, and the automatically display phone number, technician name, and available AV equipment in selected room. All other entries are manual and/or select appropriate equipment issue.

Hopefully this gives you a better caption of what I am trying to select. thanks

Not enough info.
Make a clear list of the things you want to do.
Identify what things(tables and fields) are required to do it.
Give it a try.
I don't know your buildings. I don't have a link to your c:\ drive so could not see the images, but that is not the big issue.

You have to describe things like you would to child since we don't know your set up.

[orange]

There are some basic shortcomings in your current database.
You need a data model to help with communications, training, development and maintenance.

Your overall picture, to me anyway:

We are in need of some automation to support the maintenance of a variety of AV equipment located in a number of conference rooms that are distributed over a number of buildings. There are a number of technicians who log and respond to AV equipment issues and will use the proposed database. Each conference room is identified by a number and has an associated phone number. Each conference room has 0 or more pieces of AV equipment. All trouble calls for AV equipment assistance get an auto-assigned Job number. We would like to have the DateOfIncident, DateCompleted, TypeOfProblem, AVEquipmentInvolved, TechnicianAssigned, and any related Notes to be recorded for each incident and accessible to all technicians.
In addition we have images for each conference room and would like to make these available when interfacing with specific incident records.

I am going to direct you to a tutorial from RogersAccessLibrary to get familiar with database design using his procedure. I recommend you work through
either or both of these tutorials.

Student Class Info
Consolidated Widgets


You have to work through them 35-50 minutes each. But you will learn a procedure that you can use with any database.
They each have a problem statement, a procedure and a solution. The solution (data model/tables and relationships) can be tested with sample data. When all is working well, you have a blue-print for your database.

When you finish the tutorial(s), try it with the description I provided (adjust it as required to make sure it matches your situation). Then post back specific questions.

Good luck.

[willfrank]

Thanks for providing me with further direction.

I worked through the tutorials the best that I can. I did recreate the tables and form to reflect what is needed. I included the cascading combo boxes, as selection of Building and Room Name: This function works, except I cannot auto fill the phone number, (from Rooms table). The form also does not hold the cascading selections, once I close and reopen the database.

thanks again...

Based on my original database: It is a database that I use to indicate incidents that occur in the conference rooms.

1) I want to record a record for each incident.
2) Database needs to display: picture of conference room where incident has taken place.
3) Automatically display, phone number, technician name and room equipment based on Building selection and room number.
4) Additional information manually entered: is: a) date of incident, b) date completed, c) Status, d) Task Title, e) Incident #, f) Equipment Issue, G) Client Name, I) Additional notes
5) Job # Auto number as each record is created
6) Conference room table currently details the possible equipment available in each room.

Ultimately, I am trying to create an incident database that all AV technicians can fill out when a problem occurs: Each record will provide a reference to the problems that have occurred. Currently it is to support 3 buildings, but may increase to six in the future.

Open a new record, select the building associated with the incident, then select available conference rooms based on building selection, and the automatically display phone number, technician name, and available AV equipment in selected room. All other entries are manual and/or select appropriate equipment issue.

Hopefully this gives you a better caption of what I am trying to select. thanks

[orange]

You do not have a complete model that matches your requirements. Where is your Incidents table?
Why do you not record the PhoneNumber with the Rooms table? Why a separate table that is in a 1:1 relationship?

This is incorrect in my view:

6) Conference room table currently details the possible equipment available in each room.

You should have an AVEquipment table and junction table between Room and AVEquipment if some equipment or equipment types can be in many rooms.
There may be other constructs to achieve that, but somewhere there is an EquipmentInRoom table --at least conceptually.

Good luck.

[willfrank]

Thanks for your help and guidance: I have attached a new database that provides better relationships etc. I only need to other options at this point: Auto populate text box (PhoneNumber) from Rooms table in Text box 32... This is from a null cascading combo box (RoomName) selection. I will have other auto fill text boxes based on the same once I have the PhoneNumber text box autofilling.

I will then only need to add my linked pictures to the database, based on the same RoomNumber selection.. thanks

You do not have a complete model that matches your requirements. Where is your Incidents table?
Why do you not record the PhoneNumber with the Rooms table? Why a separate table that is in a 1:1 relationship?

This is incorrect in my view:

You should have an AVEquipment table and junction table between Room and AVEquipment if some equipment or equipment types can be in many rooms.
There may be other constructs to achieve that, but somewhere there is an EquipmentInRoom table --at least conceptually.

Good luck.

[orange]

I'm looking at your database. I don't see any relationships???

What is the meaning of the Technician field in the Rooms table?? seems like it is misplaced. You would normally have a Technician table and assign a Technician to a Room or an Incident.
The fields Polycom,whiteboard and flipchart seem to represent AV Equipment and should be in a separate table.

How do you identify your Technicians?? Seems only through Rooms. Can a technician be reassigned? Can 2 or more technicians work on an Incident?

Again, I do not think your database design reflects or supports your requirements.

[willfrank]

thanks, but I do have one relationship: Buildings and Rooms. The relationship provides me with a selection of a Building number and then I can select the appropriate rooms based on the building selection. Technicians do not vary: The records are so we can just see who is responsible for each room.

I just need a dlook up or code to auto populate the PhoneNumber Text box field from the Rooms Table. Once I have that working, I will add other autofills as necessary. thanks again.

[orange]

??? Do technicians take holidays, go on training or ever get sick?? Can more than 1 technician work on the same incident?
Why can you not assign a phone number to the Room -- a field in the room table? Why would the phone number change?

[willfrank]

Thanks again for the suggestions: Technicians is just a reference as to who is responsible for that building: yes it can change etc. but for now just need a reference point. I have attached a new version, all working the way I want to; just need code to link pictures.

I tried my existing code for linked pictures, but on Form Current already has a requery, and link pictures on same does not work. Here is the code I was using:

Option Compare Database
Private Sub RoomID_Click()
Select Case RoomID.Value
Case "US-PLB-3-1243"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\1243.p ng"
Case "US-PLB-3-2009"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\2009.p ng"
Case "US-PLB-3-2057"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\2057.p ng"
Case "US-PLB-3-2172"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\2172.p ng"
Case "US-PLB-3-2201"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\2201.p ng"
Case "US-PLB-3-3172"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\3172.p ng"
Case "US-PLB-3-3200"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\3200.p ng"
Case "US-PLB-3-3201"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\3201.p ng"
Case "US-PLB-3-4172"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\4172.p ng"
Case "US-PLB-3-4200"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\4200.p ng"
Case "US-PLB-3-4201"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\4201.p ng"
Case "US-PLB-3-4285"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\4285.p ng"
Case "US-PLB-3-5103"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\5103.p ng"
Case "US-PLB-3-5200"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\5200.p ng"
Case "US-PLB-3-5201"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\5201.p ng"
Case "US-PLB-3-5202"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\5202.p ng"
End Select
End Sub
Private Sub Form_Current()
Select Case RoomID.Value
Case "US-PLB-3-1243"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\1243.p ng"
Case "US-PLB-3-2009"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\2009.p ng"
Case "US-PLB-3-2057"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\2057.p ng"
Case "US-PLB-3-2172"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\2172.p ng"
Case "US-PLB-3-2201"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\2201.p ng"
Case "US-PLB-3-3172"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\3172.p ng"
Case "US-PLB-3-3200"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\3200.p ng"
Case "US-PLB-3-3201"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\3201.p ng"
Case "US-PLB-3-4172"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\4172.p ng"
Case "US-PLB-3-4200"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\4200.p ng"
Case "US-PLB-3-4201"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\4201.p ng"
Case "US-PLB-3-4285"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\4285.p ng"
Case "US-PLB-3-5103"
Image14.Picture = "C:\Users\franklw2\Desktop\DatabasePictures\5103.p ng"
Case "US-PLB-3-5200"