Linking 2 records with Many to Many Relationship

[Dontremb]

Not sure what's going on, but I really can't seem to get it.

I'm getting this error:

The current field must match the join key '?' in the table that serves as the 'one' side of one-to-many relationship. Enter a record in the 'one' side table with the desired key value, and then make the entry with the desired join key in the 'many-only' table.

I'm seriously stuck.

 

[PHV]

I'm getting this error
Where ? When ? ... ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dontremb]

PHV, this basically relates to my other problem. I'm new to using Many-to-many relationships, and I'm just trying to link a person in my tblPersonnel, with a vacancy in my tblVacancies.

I tried making a combo box with the IDPersonnel as the main field in my subform, but it's not updating correctly or something.

There's just something I'm simply not 'getting' in regards to my relationships.

 

[dhookom]

Dontremb,
How about helping us out a little? Can you share:
[li]Form and subform record sources[/li]
[li]Combo Box Row Source, bound column, control source[/li]
[li]Link Master/Child properties[/li]




Duane
Hook'D on Access
MS Access MVP

 

[Dontremb]

Ok, I've been messing with it all day. I THINK I almost have it.

I've got:

tblPersonnel:
IDPersonnel
LName
FName

tblVacancies:
IDVacancy
VacancyNumber
JobTitle

tblCompilation:
IDCompilation
IDPersonnel (foreign key)
IDVacancy (foreign key)
Hired Y/N

The FORM is all the information from the tblVacancies.
The SUBFORM is information from tblPersonnel and tblCompilation.

I've got it working to the point where I can put in the Vacancy information, (which creates a new Vacancy ID), I can also create new people in the subform (which creates a new Personnel ID, AND a new Compilation ID.)

Most recently, I've figured out how to directly input the IDPersonnel directly into the subform to make it automatically populate the subform with that person. So, I guess the only thing I'm really trying to do is to make a drop down menu in the subform, so that I can choose from preexisting Personnel. If the person I want isn't on the list, then I can just manually input him.

I apologize if I'm not making a lot of sense. I'm really trying to make it as clear as possible.

 

[dhookom]

The subform should be based on tblCompilation. The link master/child would be IDVacancy and the combo box properties would be:

Control Source: IDPersonnel
Row Source:
SELECT IDPersonned, LName & ", " & FName As FullName
FROM tblPersonnel
ORDER BY LName & ", " & FName
Bound Column: 1
Column Count: 2
Column Widths: 0,1


Duane
Hook'D on Access
MS Access MVP

 

[Dontremb]

Ok, thanks for the help, dhookom. I actually JUST figured it out right before you posted.

The subform uses it's Primary key, Plus the foreign keys from tblVacancy and tblPersonnel.

Then, I used the rest of the information from tblPersonnel and tblCompilation in the subform.

For my lookup, I used the tblPersonnel, and had it assign IDPersonnel to the subform's IDPersonnel (which came from tblCompilation)

And that did it.

Jeez, that was a bit more complicated than I thought it was going to be.