Some help with combo boxes

[dsomers]

Hello all! I have my real estate management software and I need to use combo boxes to specify the building number and room number. There are 2 things that I need and I'm not sure how to implement them.

1. I need for when a new tenant comes in to have a drop down box (combo box) for the building number. Also I need a drop down box for the room number. I need this information to be kept with the tenants info (ie. Always remember Tenant A stays in 1219 Apt. 20).

2. On my manage tenants form, I need a drop down box for building number and a drop down box for room number and be able to pull the tenants information from whatever building and room number I select (ie. I select 1219 Apt. 20 from the 2 drop down boxes, I want to see Tenant A's information).

Like I mentioned above, I'm not very sure how to implement this and how easy or hard this is going to be. If someone could guide me on how to get this started or even has an example floating around out there that does something like this, I would really appreciate it.

Thanks!
David Somers

 

[Rich]

Search here for Cascading Combo boxes, post back for further help

 

[dsomers]

One thing I'd like to add that I've started doing is I have a table that has all the building numbers and apartment numbers in the same table. It has a Building Number and Apt. columns. I also have a tenants table that has the same two columns in it along with all their info about them (ie. MonthlyRent, Sec. Deposit, etc.) I know you can do linking with queries and I have a query set up for the tenants as well.

I guess what I'm needing to know is how to link the two together and provide the functionality in my form so that when I select a building number and room number I get the tenant specific to that location.

Thanks!
David Somers

 

[Rich]

Your structure is incorrect, if you have many rooms for one building then rooms belong in a separate table. You should have a one to many relationship between building and room with the building pk as the fk in the rooms table, the Address ID stored in the Tenants table will be the PK of the rooms table

 

[dsomers]

I noticed in the search I did that in an example sombody uploaded you can group in tables. So, would this idea work: Have the building 1219 as a group and inside have the room numbers that go with that building #, and same thing for 1229 and 1231 and 1233? There are only these building numbers that I'll be using.

If this is correct, how do I create this grouping?

Thanks!
David Somers