Combo box to list items related to current record

[GPC]

Im sure this is simple, but I cannot see how to display a list of function rooms available at a given hotel.



I have a hotels database(table) where a single record contains the usual address/telephone/email... details about each hotel;

I would like to have a combo box on my form where I can select previously added room names or type new rooms names (which will get added to the existing list) at that hotel and have this information saved to a single field in that hotels record.

Is this possible?

[jzwp11]

Since a hotel can have many rooms (a one-to-many relationship), I assume that you have a separate but related table that holds the rooms, correct?

If so, what is typically done, is to have a subform based on the room table within your main form that is based on the hotel table. You would see only those rooms that are associated with the hotel record shown in the main form. To add a new room you would just add a new record to the subform and it will automatically be linked to the hotel record (provide that you establish the relationship in the Relationship Window beforehand).

You can still have a combo box on the main form, but you will have to filter that combo box based on the current record; you would use a query for that. Additionally, you would need to utilize some Visual Basic for Application (VBA) code in the Not In List Event of the combo box to add new records to the rooms table. Allen Browne shows how to do this on his site.

[khalid]

Hi,
This can be achieved by calling the criteria of the current record (a filed on the form) in the combo box rowsource property.

The combo box will filter the records based on your criteria and will populate/list the records:
'------------
SELECT table1.field1, table1.field2 WHERE table1.field1=[field1OnYourForm]
'------------

Best Regards,
Khalid Afridi