0

I am currently creating an Access Database where I have a table of Agencies and a table of potential Services that they might offer. I am making a form to enter details of the Agencies but since the data on the Services offered is in a different table I believe I will require a sub-form to show all the Services as check-boxes.

I have the following table setup:

Agency            AgencyServices            Services
------            --------------            --------
agencyID          agencyID                  serviceID
agencyName        serviceID                 serviceName

I have a form created with all the details on the Agency being displayed but I have come across the part where I am attempting to display all the potential Services. I would like to be able to show all the Services along with a check-box beside them so that any Agency that is displayed will show the Services they are offering as checked, as well as when adding a new Agency to the system all the check-boxes will be displayed for selection.

There will more than likely be additional Services added to the Service table over the course of using this Database so I can't create a bunch of static check-boxes on the form. I would probably be looking for a more dynamic approach if anyone can point me in the right direction. I'm not overly familiar with Access so any help would be appreciated.

Thanks.

BartimaeusTY
  • 1
  • 1
  • It would be a lot easier to work with MS Access and have a subform for AgencyServices on the Agency form with Services as a combobox (eg http://stackoverflow.com/questions/12131211/create-form-to-add-records-in-multiple-tables/12132196#12132196). – Fionnuala Nov 05 '14 at 12:54

1 Answers1

0

I would suggest using a subform bound to a temp table. AgencyServicesTmp(serviceID, serviceName, offered). Make offered a yes/no field. For whichever agency is currently being viewed, populate the table from a query {warning air code!} Select serviceId, serviceName, IIF(AGS.serviceID IS NULL, False, True) as offered FROM Services LEFT JOIN (SELECT * FROM AgencyServices WHERE agencyID = Forms!frmAgency!agencyID) AGS on Services.serviceID = AGS.serviceID. Then in the before_Update event of the subform, add code to insert a new record into AgencyServices if user checks 'offered', or to delete the appropriate record in AgencyServices if user un-checks it.

AVG
  • 1,317
  • 8
  • 12