Updating data in many to one outer join

[adamrj]

I wonder if someone could help me. I have a table of customers and a table of the companies they work for. The customer to company relationship is many to one as various customers work for the same company. I've built a form based on a query which includes these two tables outer-joined but I'm unable to update the company fields from the form: "Cannot enter value into blank field on 'one' side of outer join". Ideally what I'd like is the ability to type the first few letters of the company, if it exists the record will show, if not a new record is created. Am I dreaming?!

I've done a fair bit of searching for this but haven't found anything relevant so I'd be very grateful for any help.

 

[Pat Hartman]

The "Access" way to do this is with a combo box for company. Set the LimitToList property to true. Then in the not in list event you can open a form to add a record to the list. Newer versions of Access allow you to define the form to open as a property (ListItemsEditForm property).

In a raw query that contains both the company name from the company table and the CompanyID FK from the many-side table, you can enter data in either field but not both. If you type in the company name field, a new row will be added to the one-side table. If you type an ID in the FK field, the company name field will automatically populate with the existing data.

 

[adamrj]

Many thanks for that Pat, it worked perfectly. I also needed to pull in some related fields which I used DLookup for and set them to update when the combo box changes. Obviously they're not editable in-line but can be updated in the pop-up form.