Duplicate warning
============
Use code in the BeforeUpdate event procedure of the form.
Decide what constitutes a duplicate (same name? address? birthdate? gender?)
Create a SQL string based on these values, and OpenRecordset.
Be sure to exclude this record. (It is not a duplicate of itself.)
Loop through the Recordset, building up a string listing the first 10
possible duplicates.
MsgBox() the string, asking whether to continue.
If user says No, Cancel the event (and Undo the form.)
Street names
==========
You cannot automatically parse the street name from an address reliably, as
there are just too many forms of address. It is not just about prefixes
(East, South, ...) suffixes (St, Rd, Way, ...), and double suffixes (Main St
South), but also streets with unparsable names (The Boulevade, Grand
Promenade, ...), different streets with similar names (Boag St and Boag
Place, ...) and addresses that are post boxes, street corners, building
names, locked boxes, RMDs, not to mention ethic communities (Rue de Paris),
etc, etc.
If this is something you will often search for, you may want to add an extra
field to store the StreetName. You can write some code in the AfterUpdate
event proedure of your Address1 text box to attempt to parse the street name
and write to the StreetName field, but the user may need to overwrite it for
some cases.
Here's one we did for Australia, where the postal codes are 4-digits:
Private Function CalcStreetName(varAddress As Variant) As Variant
'Purpose: Return a string that starts with the street name from the
addresss passed in.
'Return: Street name
Dim strStreet As String
Dim lngI As Long
Dim lngPos As Long
If Not IsNull(varAddress) Then
strStreet = Trim$(varAddress)
'Lose trailing postcode (4 digits after a space.)
If strStreet Like "* ####" Then
strStreet = Trim$(Left$(strStreet, Len(strStreet) - 4))
End If
'Lose everything up to the last digit in the string.
For lngI = 1& To Len(strStreet)
Select Case Asc(Mid(strStreet, lngI, 1&))
Case vbKey0 To vbKey9
lngPos = lngI
End Select
Next
If lngPos > 0& Then
strStreet = Trim$(Mid$(strStreet, lngPos + 1&))
End If
'Lose everything after the first space
lngPos = InStr(strStreet, " ")
If lngPos > 0& Then
strStreet = Trim$(Left$(strStreet, lngPos))
End If
End If
'Return what's left, or Null if nothing found.
If strStreet <> vbNullString Then
CalcStreetName = strStreet
Else
CalcStreetName = Null
End If
End Function
