M-t-M on a form with custom combo box

[rdonohoe]

Hi everyone, new to access and have a question someone might be able to help me with. I've tried to Google but don't really know what to type.

For the purposes of this question I have three tables related many-to-many as follows

tblRegister                       jncEmployee-Reg        tblEmployee
regID                                id                             employeeID
date*                               regID                        fname*
Company Giving*                employeeID                lname*
Company Receiving*                                           Active

As you can see there is a many-to-many relationship with a junction table.

Now I have built a query which shows * items above and then there is a form built on top of the query.

Access does a good job a adding the new record to the junction table but always create a new employee obviously, because the employeeID isn't there.

I would like to avoid the user seeing the employeeID and would like to minimise spelling mistakes etc, by having a combobox that would contain all the active employees which would then map to the junction table properly to allow for my many-to-many relationship to work.

I hope this makes sense.

Any help would be great.

 

[boblarson]

So the big question of how to do this depends on which way you want to come at it. When you have a many to many relationship, you really have two one-to-many relatinships.

So you could have an Employees form which is bound to the Employees table and then a subform with the junction table as its record source.

Or you could have a Register form which has the junction table as the subform.

On the subform you basically have a combo box as the non linked ID. So, with the Employees form as the main form and the junction table as the subform (it would be in all cases), you would link the Master/Child links on EmployeeID and have a combo to select the reg ID from the reg table and bind the combo to the RegID field.

But it looks like you should have the Registry table be the main form's record source and link the junction table (subform) on RegID and then have a combo to select the employee and have its record source from the Employees table and store the Emp ID.

The field you have set as the linked field (master/child) will be filled out by Access automatically.

Does that help?

 

[rdonohoe]

Hey,

This looks good. Thanks a lot. I'll have a go an ticker around and see what I can do.

If I have some more questions I'll pop back.

Thanks again,

R