Junction Table Data Entry

[Zydeceltico]

Hi All,

I have three tables: tblJob, tblContacts, and junction table tblJobContacts.

tblJob has multiple fields including the PK JobID (autonumber). tblJobContacts has typical fields for contact info and PK ContactID (autonumber).

tblJobContacts links to JobID on tblJob via JobID_FK. tblJobContacts links to ContactID on tblContacts via ContactID_FK.

tblJobContacts has 4 fields: JobContactsID (PK), JobID_FK, ContactID_FK, and Role (which is linked to lookup table tblRole).

One job will have two Contacts always. A Customer Contact and a Jobsite Contact. They will typically be different (95% of the time) but conceivably could be the same.

Also, different jobs could have repeated contacts - in other words a single contact may be associated with many jobs.

Desired workflow is to open a form to add a new Job knowing that I will be adding two contacts associated with that job (one of which will be immediately - the second at a later date). My challenge is not knowing how to create a data entry form based on qryJobContacts or frmJobContactDS that allows for more than a single entry into tblJobContacts remembering that a single Job will have more than one record in tblJobContacts. frmJobContactDS shows how that should be represented. It also shows that even though there will be two Contacts per job, they don't both always get entered at the same time. In fact they rarely do. NOTE: JobID is not stored in tblContacts. tblContacts acts solely as global address book of ALL contacts regardless of role related to any given Job.

However, I would like my data entry form to allow for two contact entries: One is always the customer and the second is always the jobsite contact. Perhaps, this makes the lookup from tblRole inconsequential and I should instead add fields Customer and JobsiteContact to tblJobContacts.

I need to "AddNewJob" and have that form prompt me to enter multiple new entries to tblContacts.

I just don't have any idea what my options are and how to proceed.

I would later like to open a form or report that takes JobNumber (directly related to JobID in tblJob) as search criteria and returns BOTH contacts (Customer and JobsiteContact) info.

I hope I haven't muddles my question too much.

As always - thank you very much in advance for any and all insight!

Tim

 

[MarkK]

See the fJob and fJobContactRole forms I added to your Db. The second is a subform which is bound the the main form on 2 fields, the JobID, and the Role of the contact. See how this both correctly filters the subforms, and enters a default value into the Role field if you enter a new row.
This may give you some ideas about how to proceed.
Mark

 

[mike60smart]

Hi Tim

Mark's example probably fits better than mine but this is what I had as an example.

 

[Pat Hartman]

You can limit the number of contacts for a job by adding a unique index to the tblJobContacts table. Then make a normal subform. I would have done it but you have other issues and I couldn't get the forms to work correctly. So I attached a sample database that implements a m-m relationship so you can see how it will work.

You also need to add the correct relationships in the relationship window.

Personally, I would make the FK match the PK but if you like the FK suffix, that's fine but the main part of the name should otherwise be identical. So role to JobRole is confusing as is ToolID to ToolNum

Also, you have a table level lookup for Salesperson and I guarantee that sooner rather than later, this will cause a problem. It is best to get rid of it now. There's lots of reasons to not use them starting with - users NEVER, EVER see queries directly. That means the only person who benefits is YOU and YOU, if you are developing an app for other people know better because you know the problems that will be caused in queries and code.

 

[Zydeceltico]

Also, you have a table level lookup for Salesperson and I guarantee that sooner rather than later, this will cause a problem. It is best to get rid of it now. There's lots of reasons to not use them starting with - users NEVER, EVER see queries directly. That means the only person who benefits is YOU and YOU, if you are developing an app for other people know better because you know the problems that will be caused in queries and code.

Hi Pat,

Thank you for all of the advice. Your example DB is most helpful. I'm working on it today and will post later as to my success or more questions.

In the meantime, and not to change the subject, but since you mention it, I have struggled with the "table level lookup" you mention above. I have read multiple, multiple times not to use lookup fields but instead use lookup tables and vice versa. I understand the cause for concern but must admit that after reading countless posts and articles I am only more confused about either option and still have not had anyone directly point me to a bulletproof means to accomplish presenting the end-user a consistent, static, and repeated list of options to fill a certain data field. Are you able to point me to an alternative? Is a list box a better alternative?

As always, thank you for your assistance.

Tim

 

[Zydeceltico]

This may give you some ideas about how to proceed.
Mark

Hi Mark,

This does give me some ideas. I'm thinking that "wedding" your approach with some tips from Pat (below) will likely accomplish what I am after.

I'll post later as to my outcome.

Thank You!

Tim

 

[Pat Hartman]

You're welcome.

My suggestion (and that of the others) is NOT to not use lookups. It is simply to not use them on tables. Lookups on forms work great and as long as you set them to limit to list = yes, they will prevent users from entering an item that is not in the list. The example I posted almost certainly contains combos on a form so you can see how they work.