Multi Field Optional unique Index

[Guest]

I've been searching this group for days looking for an answer that exactly
fits my problem. I sure hope some of you fine people can help me.

Access2003

Two of the fields in one table- CustStreetNumber and CustAddress - contain
the number and name of a street. I originally had these set as the PK to
avoid duplicates but now they want duplicates to be allowed but they want a
pop-up warning so they can check the other address to see if it's the same
job. I know how to create the msgbox with VB. *I know enough VB to usually
break things and cause problems*

The real problem is that I can't figure out how to check this scenario in
the table. I have experimented with Dlookup to no avail - I'm sure Microsoft
couldn't make that syntax more complicated! I've seen some examples using a
combination of Dlookup and DCount but I believe I burned some brain cells
reading them. I can create a query to find these duplicates. I just keep
hitting a wall bringing it all together. The answer is probably simple but I
feel like I am too close to figure it out!

How do you make two fields an optional unique index with a msgbox to prompt?

Thanks in advance.

 

[Allen Browne]

You won't be able to do that in a table.
Do it in a form.

In the BeforeUpdate event procedure of the form, you can test if the
combination of the 2 fields already exists and give the warning. The event
procedure will be something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String

If IsNull(Me.CustStreetNumber) OR IsNull(Me.CustAddress) OR _
((Me.CustStreetNumber = Me.CustStreetNumber.OldValue) AND _
(Me.CustAddress = Me.CustAddress.OldValue)) Then
'do nothing
Else
strWhere = "(CustStreetNumber = """ & Me.CustStreetNumber & _
""") AND (CustAddress = """ & Me.CustAddress & """)"
varResult = DLookup("ID", "Table1", strWhere)
If Not IsNull(varResult) Then
strMsg & "ID " & varResult & " is for the same number and
street." & vbCrLf & "Proceed anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Possible Duplicate
Warning") <> vbYes Then
Cancel = True
'Me.Undo
End If
End If
End If
End Sub

DLookup() is not such a cryptic beast. See:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

 

[Guest]

Amazing.

Allen, I have read so many posts from you that I feel like I know you. You
never fail. This worked exactly as I had hoped and I can't thank you and
many other frequent posters enough. You guys don't ever get enough credit.

I actually learned the Dlookup function from the link you posted, only it
was some time ago to another lost user! My biggest problem was adding a
second field and I can never get the quotes and ( )'s right.

Thanks again. I owe you a drink.