problem with lookup field

[purksd]

Table 2 (Rooms) has the following fields:<br> Building (primary key)<br> Room<br><br>Table 2 (computers) has a bunch of fields including:<br> Building<br> Room<br><br>While doing data entry in Table 2, the user has entered data in the Building field (using a pulldown). When entering data for the field Room, I want to do a lookup in Table 1 based on the Building that was just entered. Under the lookup tab for the field Room, I have Display Control=Combo Box, Row Source=Table/Query, and the following for Row Source:<br> <br> SELECT Rooms.Room FROM Rooms WHERE (([Rooms].[Building]=[Building]));<br><br>And it doesn't work.&nbsp;&nbsp;When I click on the pulldown in the Room field, I see every record in the Rooms table, not just the ones for the entered Building.<br><br>I think I must be close because if I replace the [Building] parameter with a text field (such as &quot;Building 460&quot, the pulldown list then shows only the rooms which are associated with that building:<br><br> SELECT Rooms.Room FROM Rooms WHERE (([Rooms].[Building]=&quot;Building 460&quot);<br><br>Suggestions on how to fix this problem?<br><br>Also, what's the easiest way to verify that the room is correct if the user changes the building?&nbsp;&nbsp;I'm assuming this would be handled in an AfterUpdate event?<br>

 

[DougP]

Well anytime to pass a variable to a SQL statement it has to be outside the String<br>Like so:<br>SQL = &quot;SELECT Room, Building FROM Rooms WHERE Building = &quot; & Me!Building & &quot;;&quot;<br><br>Now I'm not sure if I have your field on your form correct<br>I assumed it was &quot;Building&quot; <br><br>But you get the idea.<br>

 

[purksd]

I must be missing something....Under the Lookup tab, Row Source, if I enter quotes around the select, I get an error: The Microsft Jet database engine cannot fine the input table or query &quot;'SELECT Rooms.Room .... ;'&quot;. Make sure it exists and that its name is spelled correctly.<br><br>If I enter &quot;&ME!Building&&quot; or &ME!Building& I get: Syntax error (missing operator) in query expression.<br><br>If I enter ME!BUILDING, when I click on the field I get a popup asking for the value of ME!Building

 

[Jonathan]

I think that the text you want in the Row Source is:<br><br><FONT FACE=monospace>SELECT Rooms.Room FROM Rooms WHERE Building = [Forms]![<i>insert form name here</i>]![Building]</font><br><br>Not entirely sure what you mean by your last question - can you elaborate?<br><br>Jonathan<br><br>

 

[purksd]

This gives me the same thing I've seen before - a popup that says <br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Enter Parameter Value<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Forms!Name/Location!Building<br><br>As for my last question.....Let's say that the computer is being moved from Building 1 Room 500 to Building 2 Room 100.&nbsp;&nbsp;The user picks Building 2 from the list, but forgets to change the Room (room 100 is the only room in building 2).&nbsp;&nbsp;Before leaving the record, I'd want to display an error indicating that the value in Room is now invalid and needs to be corrected.<br>

 

[purksd]

More information.....If I then enter a value in the popup box (for example, Building 2), the correct list of values for Room is displayed.&nbsp;&nbsp;When I go to the next record and change the value of Building to Building 3, when I go to the Room field it doesn't display the popup box again - it displays the list of Rooms for Building 2.

 

[Jonathan]

OK.&nbsp;&nbsp;I've done this kind of thing before, and I could never be bothered to devote the time to making it work correctly.&nbsp;&nbsp;So, I bodged it:<br><br>Set up a query on your Buildings and Rooms tables, joining them on the Building field of each.&nbsp;&nbsp;Set up the query to output two columns; the room, and a string which is a combination of Building and Room.&nbsp;&nbsp;Then you will only need a single combo on your form, based on this query.&nbsp;&nbsp;Hiding the first column will mean that the user will see an entry like &quot;Building 1/Room 100&quot;.&nbsp;&nbsp;This greatly simplifies things, as your second problem is solved without further effort.<br><br>Hope this is of use<br><br>Jonathan <p> Jonathan<br><a href=mailto:j.w.george@virginnet.co.uk>j.w.george@virginnet.co.uk</a><br><a href= > </a><br>Working with: Visual Basic 6, Access 97, Visual Interdev 6, VBScript, Active Server Pages, SQL Server 6.5, Oracle 7, users who really have no idea what they want...

 

[gol4]

try to requery the form

 

[purksd]

Requery the form how?