INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Find Record Form - Include Related Records?

Find Record Form - Include Related Records?

(OP)


I have the following structure:

Contact (ID, txtContact, txtNotes)

Match (ID, FKContact, txtMatch, txtNotes)

Contact Match Record (ID, FKMatch, txtsomestuff, txtNotes)

I user starts a new record by assigning it to a contact and a match. Now we have had to add 2 extra tables to the structure, so a user can be on a record and link it to other contacts and/or other matches.

Those tables are as follows:

CODE

SELECT [ID]
      ,[FKRecord]
      ,[FKContact]
  FROM [dbo].[tblMContacts_RelatedContacts]

SELECT [ID]
      ,[FKRecord]
      ,[FKMatch]
  FROM [dbo].[tblMContacts_RelatedMatches] 

Now I am struggling to update the rowsource for a listbox, on a form for finding a record. When a user looks for a record, they go to a form that looks like this:

Find Record Form

This is the row source of that list box, which functions off the user choosing an option from the contact combo box, the match combo box, or both.

CODE

SELECT tblMContacts.ID, tblContact.ContactID, 
tblMatch.MatchID, tblContact.ContactID AS [Contact#], 
tblMatch.MatchID AS [Match#], tblContactType.txtContactType AS Type, 
tblMContactStatus.txtContactStatus AS Status, tblDocType.txtDocType AS [Doc Type], 
tblDMDocsMContact.dtStart AS Start, tblDMDocsMContact.dtEnd AS [End]
FROM (((((tblContact 
LEFT JOIN tblMatch ON tblContact.ID = tblMatch.FKContact) 
LEFT JOIN tblMContacts ON tblMatch.ID = tblMContacts.FKMatch) 
LEFT JOIN tblContactType ON tblMContacts.FKContactType = tblContactType.ID) 
LEFT JOIN tblDMDocsMContact ON tblMContacts.ID = tblDMDocsMContact.FKMC) 
LEFT JOIN tblMContactStatus ON tblMContacts.FKMContactStatus = tblMContactStatus.ID) 
LEFT JOIN tblDocType ON tblDMDocsMContact.FKDocType = tblDocType.ID
WHERE (((tblMContacts.ID) Is Not Null) AND ((tblContact.ContactID)=[Forms]![frmMContacts_FindContacts]![cboFilterContact])) 
OR (((tblMContacts.ID) Is Not Null) AND ((tblMatch.MatchID)=[Forms]![frmMContacts_FindContacts]![cboFilterMatch])) 
OR (((tblMContacts.ID) Is Not Null) AND ((tblContact.ContactID)=[Forms]![frmMContacts_FindContacts]![cboFilterContact]) AND ((tblMatch.MatchID)=[Forms]![frmMContacts_FindContacts]![cboFilterMatch]))
ORDER BY tblContact.ContactID, tblMatch.MatchID; 

I am figuring i will need to union any records that are not directly related to a contact or match, chosen. I will need to also list records that are in either of the related tables, which match that contact or match id.

Does this make sense? I tried to give all the details, but please let me know if I'm missing something.

I would love any help with how to get records to show up for any records that are in the related tables, as well as directly linked records.

Thank you.

misscrf

It is never too late to become what you could have been ~ George Eliot

RE: Find Record Form - Include Related Records?

Would be helpful if we had the real table and field names. And a description of what it holds and how it relates. Only need the primary and foreign keys. Something like this. I cannot decipher what you are saying. You say there is another 2 tables and provide a sql string. You show real names and notional names. Maybe an example of what you would enter in the combos and the different records from the different tables you would expect to be returned.

CODE -->

tblContact
  contactID (long primary key)
  (other fields holding information about a buisiness contact)

tblMatch
  ID (primary key autonumber)
  FKContact (A long FK, relates to contactid in tblContact)
  table describes.... 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close