Text field update depending on list box value

[jdrighter]

I realize there are several threads already dedicated to this particular topic but my situation is slightly different... First, my tables are set up like so:

tblStudent(studentID,LName,FName,MI,Gender)
tblRack(rackID,roomID,studentID,rackNumber)
tblRooms(roomID,roomNumber)

There are 3 racks to a room and a total of 90 rooms. A room can hold up to 3 students. A list box holds values that I have typed in (the room numbers). I went with typing them in rather than pulling them from a query because of the way my tables are set up. If I pull from a query as the tables stand, room numbers show up 3 times, once for each rack. I want to be able to fill in text boxes with information on students assigned to the room I choose from my list box (FName, LName, etc...) I'm kind of at a loss here on how to go about getting the info that I want. Using the column property to populate text fields is probably going to be much easier but a query is needed (I think) and using a query causes room numbers to show up 3 times in my list box... Anyone want to throw me a bone?? Thanks!

 

[mikebaldam]

I want to be able to fill in text boxes with information on students assigned to the room I choose from my list box (FName, LName, etc...)

Can you use a DISTINCT select query...?

Just to get the ones you want..?

Or using an event procedure on Change

me.txtwhatever = tblStudent.Room

...?

Sorry your questions was a bit vague..

 

[jeremie_ingram]

WEhy not get the structure tighter and create another table. Pull the student and room ID from the Rack table, then create a junction tbl by putting all three keys into a single table?