You sat down to build your database and things were going great. You made a table for Members and one for Members Contacts. Only you now realize that the Members Contacts can also be Members. At first you think… that will be okay. But then you go to build your forms and realize this is a problem. If you create a UNION query to show all the Members, you can’t edit the information AND you notice that some Member’s ID is the same as some Member Contacts ID.
This is when you need to think outside the box. You can store all your Members, Company’s, Contacts and Individuals, in the same table and still keep track of Member’s Contacts which will make querying much easier. But let’s be clear, this is the exception to the rule, not the rule. Normally, I would recommend a table for Company’s and a seperate table for Contacts. However, we all know in the real world the *standard* does not always apply!
For this example we’ll use the table as outlined below (To see the complete Data Model and download the Sample Database, click here)…
tblMembers
mActive
mMemberID (PK)
mRegistrationID
mAttachedTo (circular reference to Member ID in this table)
mCompanyName
mRegistrationID
mTitle
mFirstName
mLastName
etc…
In the table we’ll enter…
As you can see Access Diva (Company) has a MemberID of 1. Gina Whipp, who works for Access Diva but is also a Member has a MemberID of 2. In order to *relate* Gina to the Company in the mAttachedTo field you would enter a 1 or, since this would be on a Form with a Combo Box to select from, you would select Access Diva (Figure 2).
The RowSource for the Combo Box in Figure 2 is…
SELECT tblMembers.mMemberID, tblMembers.mCompanyName FROM tblMembers WHERE (((tblMembers.mAttachedTo) Is Null));
You can either build one Form to enter Members OR you could build a form that has a Main Form for Company’s and Subform for Company Contacts. Using the Main Form/Subform scenario you could use the mMemberTypeID or the mRegistrationID to identify which Contacts are also Members. To seperate the two is easy… Take a look at the following two queries…
Companies…
SELECT tblMembers.mMemberID, tblMembers.mCompanyName, tblMembers.mFirstName, tblMembers.mLastName
FROM tblMembers
WHERE (((tblMembers.mAttachedTo) Is Null));
Contacts/Individuals…
SELECT tblMembers.mMemberID, tblMembers.mCompanyName, tblMembers.mFirstName, tblMembers.mLastName
FROM tblMembers
WHERE (((tblMembers.mAttachedTo) Is Not Null));
Download the Sample Database here… Complaints.zip
1,922 total views, 1 views today
Thanks Gina
Think I cracked it I put this in the forms properties:
Private Sub Form_AfterInsert()
Me.Combo0.Requery
End Sub
And it seems to work 🙂
Now for the rest of the form
Tony
PS Sorry, should have tried all those things before I posted, lesson learnt 🙁
I see you didn’t need me after all…
I also see, by your last comment, that the sample I uploaded is missing the Forms! I will fix that later today as I must have uploaded the one from my website which only includes the tables. Sorry about that but THANKS for pointing that out!
Hi Gina, I did download that example but there are only tables and a couple of queries, no forms?
I created one of my own based on your website example.I have got it to work,just need to figure out how to update the combobox immediately after I’ve entered a new company. So that if the next new record is to be attached to the new company record it’s in the list. I’m going to try Docmd.Requery in a few places.
I love challenges!
Tony
Tony…
.ZIP file has been updated to include a sample Form!
Hello Tony,
Yes, the Control Source for the *Relate To* control is *mAttachedTo*. To see a sample, at the bottom of the article, is a downloadable file *Complaints.zip*, it has a sample Form demonstrating what I described.
Hi Gina What would be the Control source for the Relate to control, would it be mAttachedTo? Any chance including this form in the zip file?
Thanks
Tony