ado and cbo boxes

[skicrud]

I have two table in access with the following fields:
HotelID and Location in table hotel and
HotelID and UserName in table user.
When I populate a cbouser with the username I need to populate cbolocation with the location. The table are related through Hotel.HotelID and user.HotelID. As I select a username in cboUser I need to see the related record in cbolocation.
Does anyone have any suggestions as to how to do this?

[mendhak]

SELECT U.HotelId, U.UserName, H.Location FROM User U LEFT JOIN Hotel H ON U.HotelID = H.HotelId

[dRAMmer]

What do you mean by related information. Does one username can have different location? If so then

private sub cboUser_Click()
'by mendhak
rs.open "SELECT U.HotelId, U.UserName, H.Location FROM User U LEFT JOIN Hotel H ON U.HotelID = H.HotelId WHERE U.Username ='" & cboUser.Text & "'",YourCN, adOpenStatic, adLockReadOnly
if not (rs.bof and rs.eof) then
   'populate your cboLocation
end if

also it's better if the where cluase will be by code, say UserID, like so:

private sub cboUser_Click()
'by mendhak
rs.open "SELECT U.HotelId, U.UserName, H.Location FROM User U LEFT JOIN Hotel H ON U.HotelID = H.HotelId WHERE U.ID =" & cboUser.ItemDate(cbouser.listindex)",YourCN, adOpenStatic, adLockReadOnly
if not (rs.bof and rs.eof) then
   'populate your cboLocation
end if

[skicrud]

Each user can have only one location.

[mendhak]

Use drammer's code with my SQL query instead. You can change the other combobox's item in the first combo's change event.

[dRAMmer]

Originally posted by skicrud
When I populate a cbouser with the username I need to populate cbolocation with the location. As I select a username in cboUser I need to see the related record in cbolocation.

Each user can have only one location.

Why do you need to populate a combo box then? If only one location can be designated to one user then why not use a textbox instead?