look up query

[marix]

Hi,

In my access table(2010) I have a field called 'Site'(lookup to a query to list all sites)

I also have a field called 'rooms'.

Now for the tricky part.

When I select a value in the 'site' field I want to display all the rooms from the chosen site in the 'room' filed, so the user can then select which room is required.



I can do it manuall using this sql:

SELECT tblLocation.Room
FROM tblLocation
WHERE tblLocation.site="Site1";
I have six sites each with different rooms.

So, in this example "Site1" needs to get its information from the selected text in the 'site' filed.
I am finding this hard to explain so I hope somebody can understand me :O)

Thanks in advance

[alansidman]

What you are describing are called cascading combo boxes. Here is good tutorial on how to make it work for you.

http://www.fontstuff.com/access/acctut10.htm

Alan

[marix]

Hi,

Thanks for the response. Yes, this is what I want but I don't want to use forms. I am working from the table directly. Is it possible to do it this way?

[alansidman]

ooooh. Working directly in tables in not a good idea. To many opportunities to make errors and overwrite fields. It is always best to have users work with in forms so that you limit what can or cannot be accessed or changed. Having given my spiel on that, I don't know how you would attach any VBA actions to a table. I have never done it nor even heard of it. All automation is through forms.

And finanlly, here is a link on why you shouldn't use lookup tables in tables.

http://access.mvps.org/access/lookupfields.htm

Sorry to be so down on your methodology but ......

Alan

[marix]

Thank you so much for your time. I have taken your advice and achieved exactly what I wanted by using forms and VB. Can't believe I didn't want to use forms - things have just become much more interesting!

cheers!