Subform assistance

[deathcake]

Hi there,

I have been asked to move an VB.net/SQL system over to Access(FE)/SQL(BE) as the company feels it would be cheaper to pick up the work if the people who created it left. I'm by no means a database or programming professional but sadly, probably the person most able to do this.

Anyway we have, simplified for the purpose of requesting help, 3 tables. A SITE table, PERSON table and JOBASSIGNMENT table.

The JOBASSIGNMENT table assigns a PERSON to a SITE. It's a junction table with a one to many relationship (one person, many jobassignments/ one site, many jobassignments)

My main issue is I want to cycle through the sites, in order, and find out who is on those sites. I'm struggling to get this to work. I tried to use a sub form, with the sub form containing the record source for only the SITE table. With this I could query the JOBASSIGNMENT table based on the output of a textbox with the "site number" field and find out who is on it. I can't get the subform with the SITE table as the record source to show anything. If I use a distinct query only showing distinct SITE numbers it never displays as a form view. It always returns with a dataview regardless of whether i go into the sub form in design view and set it's default view as Form.

I feel like i'm fumbling about in the dark here so I'd appreciate any help or perhaps a different approach. Thanks in advance!

(I have edited the post slightly)

 

[pbaldy]

Unless I'm misunderstanding, I'd have the main form bound to the sites table and the subform bound to the junction table, with master/child links on the site ID. That would mean as you choose a site on the main form, you'd see the people on that site in the subform.

 

[deathcake]

[Ignore below. I was changing the record source of the main form in VBA and trying to stuff two records into one textbox i think. Sorry!]

Yeah, that makes so much sense! Works a treat, thank you!

I'm having an issue here with the sites that have no people on them. My subform is a query using the site number to pull only the people assigned to it in the junction table.

It all works fine expect for the sites that the query returns no results for. The full form goes blank. I am currently using dcount to catch the query returning no results, but I don't know what to set the Source Object of the subform control to to stop the form going blank.

 

[pbaldy]

Ignore below means you have it working?

 

[deathcake]

Yes, I do. Apologies for the confusion and thanks for the help so far! Should I remove the post?

 

[pbaldy]

No, it's fine. Glad you got it sorted!