Access subform with lookup combo box

[GrphicGal]

Hello!

I’m having a frustrating problem with my form and subform with a lookup combo box. Please help me! My main form is the Project form. Each project can have many Court Staff and Court Staff can be on many projects. I think this is a many to many relationship. So I made a junction table.

1) tblProject
Project_ID = AutoNumber
ProjectTitle = Text

2) tblCourtStaff
CourtStaff_ID = AutoNumber
StaffName = Text

3) tblProCourt (junction table)
Procourt_ID = AutoNumber
Project_ID = Number
CourtStaff_ID = Number

I want a subform datasheet with a lookup combo box that I can choose as many court staff as needed for each project. I’ve used the wizard but just don’t understand how to do this.

Thank you for any help you can give me!

 

[dhookom]

Your subform should be bound to the table tblProCourt. You can link master/child with the Project_ID field. Your subform will consist of a single combo box bound to the CourtStaff_ID field and will have a row source of:

SELECT CourtStaff_ID, StaffName
FROM tblCourtStaff
ORDER BY StaffName;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GrphicGal]

Thanks for your reply. I tried to do what you said, but apparantly I'm not understanding how to bound my subform to the table tblProCourt. Also when I made the combo box, it would only let me choose one name, not as many as I want.

 

[dhookom]

To bind a subform or form to a table or query, set the subform's record source to the table or query.

You should not select multiple names in one record. You select one name in each record in the subform.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]