One primary, many secondary per company

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table/form with Company Name, Contact Name and Contact Level. We
have two contact levels: Primary and Secondary.

What we would like to do is only allow one primary contact per company, and
mulitiple secondary contacts. How/where do I program Access to do this? Is
this even possible?

I only have a basic understanding of Access/VB programming, so please try to
be a descriptive as possible.

Thanks!
Laura
 
I had to do this sometime ago. I accomplished it by creating a field
lngPrimaryContact and then populated it with the ID for the primary
contact. As I recall it was a simple UPDATE query to update the relavent
records to the new ID.

lngContactId txtName lngPrimaryContact
1 David 4
2 Glen 4
3 John 5
4 George 4
5 Mark 5
6 Kerri 5

You'll have to play with it of course depending on the specific db
schema, but that should get you started.
 
assuming that your question is how to prevent more than one ContactLevel of
"Primary" from being assigned to the same company *during data entry in the
form*: one solution would be to check the ContactLevel assigned to the
current record in the form. you can do this in the BeforeUpdate event of
either the ContactLevel control, or the Form itself, as

If DCount(1, "MyTable","CompanyName = '" & _
Me!CompanyName "' And ContactLevel = 'Primary'") > 0 Then
Cancel = True
MsgBox "This company already has a primary contact."
End If

replace MyTable with the name of the table that your form is based on. the
above assumes that the CompanyName field and the ContactLevel field are each
a Text data type.

hth
 
Tina,

Thanks! That's what I was looking for. I'll test it out and let you know
how it worked out.

Laura
 
I used the following code:
If DCount(1, "CM ContactInfoTable", "CompanyName = '" & _
Me!CompanyName & "' And ContactLevel = 'Primary'") > 1 Then
Cancel = True
MsgBox "This company already has a primary contact."
End If

But now, it gets into a loop and won't allow me to change the Contact Level
to Secondary. And even if I do set it to secondary, it says I already have a
'primary' contact. Is there a way to set the field to 'secondary' after it
checks and verifies there is already a 'primary'?

Thanks,
Laura
 
oops! and not oops. my If comparison is wrong, so we need to adjust it.
interestingly, this mistake points out that you already have records in your
table where more than one record with the same CompanyName is assigned as
Primary. you'll have to clean up your data (find those records and fix them)
to avoid being stuck in a validation loop.

once you do that, let's try a modified If comparison, as

If DCount(1, "CM ContactInfoTable", "CompanyName = '" & _
Me!CompanyName & "' And ContactLevel = 'Primary'") > 0 Then
Cancel = True
MsgBox "This company already has a primary contact."
End If

hth
 
I cleaned up the data (there were only a few with more than one primary
contacts), but if someone accidently enters a new record and sets the contact
level to primary when there is already a primary, it gives the correct error
message, but when I go back to change it to secondary, it still gives the
error message and I am unable to save the record because of the error
message. Is there a way to reset the error message so it doesn't trigger
again or set the contant level entry to secondary so it doesn't trigger the
error message box?

Thanks,
Laura
 
sorry, Laura, i've had a run of stupid attacks, it seems. this should take
care of it:

If Me!ContactLevel = "Primary" Then
If DCount(1, "CM ContactInfoTable", "CompanyName = '" & _
Me!CompanyName & "' And ContactLevel = 'Primary'") > 0 Then
Cancel = True
MsgBox "This company already has a primary contact."
Me!ContactLevel.Undo
End If
End If

put the code specifically in the ContactLevel control's BeforeUpdate event,
*not* in the form's BeforeUpdate event.

hth
 
Got it!

Here's the final code:
If Me!ContactLevel = "Primary" Then
If DCount(1, "CM ContactInfoTable", "CompanyName = '" & _
Me!CompanyName & "' And ContactLevel = 'Primary'") > 0 Then
Cancel = True
MsgBox "This company already has a primary contact."
Me!ContactLevel = "Secondary"
End If
End If

I had to change the Me!ContactLevel = "Secondary" because it said the
function undo wasn't available or something.

Thank you so much for your help! You saved me lots of frustration.

God Bless. Laura
 
hurray, together we did it - coool! :)


Laura said:
Got it!

Here's the final code:
If Me!ContactLevel = "Primary" Then
If DCount(1, "CM ContactInfoTable", "CompanyName = '" & _
Me!CompanyName & "' And ContactLevel = 'Primary'") > 0 Then
Cancel = True
MsgBox "This company already has a primary contact."
Me!ContactLevel = "Secondary"
End If
End If

I had to change the Me!ContactLevel = "Secondary" because it said the
function undo wasn't available or something.

Thank you so much for your help! You saved me lots of frustration.

God Bless. Laura
Click to expand...
 
Back
Top