Combo Box Drop Down

[loddiemonster]

I created a table that lists 10 services that we offer. The table contains the following fields: Service ID, Service, Client ID, Hours per week. In design view, under the Lookup tab, for display view, I set it at Combo Box Drop Down because one client can have up to three services they can receive.

On my subform (that's linked to the main form by Client ID), that table is the control source. However, now that I enter more new clients with their services selected, that drop down keeps getting longer and longer. The table is longer than it's original size too. It's not showing just the 10 original services with the little check boxes to the left for selection, but many combinations. How can I get only the ten services to show each time, but the services to still follow the client? I guess my reports will still pull correctly, but for cosmetic reasons as well as to reduce user error, I would only like those ten options to show.

Thanks!

 

[Mile-O]

Design issue.

Create a table for services. Then a table for clients. Then a junction table for clients to services. Rough example below:

tblServices
ServiceID (PK, Autonumber)
ServiceName

tblClients
ClientID (PK, Autonumber)
ClientName

tblServicesToClients
ClientID (PK, Number)
ServiceID (PK, Number)
HoursPerWeek

Then, with relevant queries, your parent form can be bound to the tblClients table and your subform to the tblServicesToClient table. Linked by Master/Child field.

 

[CJ_London]

Sounds like you are using the lookup facility in table design with multi select and then trying to populate the tables directly and not through a form or query.

This is a bad idea - remove them from the table and follow Mile-O's advice