verifying addresses

[icepunk]

hello, I'm trying to create a form that verifies if a person has already been added to the table by using thier phone number. I've tried simple scripting in VB but I have a few problems when it comes to getting the phone number variable.

Thanks to ahead of time for any help.

 

[fneily]

On the AfterUpdate event of the PhoneNumber control, you could have:

Private Sub PhoneNumber_AfterUpdate()
Dim DB As Database
Dim RS As Recordset
Dim strWhere As String

Set DB = CurrentDb()
Set RS = DB.OpenRecordset("YourTableName", dbOpenDynaset)
strWhere = "[PhoneNumber] = " & Chr(34) & Me![PhoneNumber] & Chr(34)
RS.FindFirst strWhere
If RS.NoMatch Then
RS.AddNew
RS![PhoneNumber] = Me![PhoneNumber]
RS![Other fields to add]
RS.Update
Else
MsgBox "Phone Number Already Entered"
End If
RS.Close
DB.Close
Set RS = Nothing
Set DB = Nothing
End Sub

 

[BoxHead]

icepunk, fneily's answer looks right on, but I read the question differently.

If you're not forcing a single style of data entry for phone numbers with a mask or code, then you may want to use a function so that Access knows that
[tt] 555-555-5555
555.555.5555
(555)555-5555
(555) 555-5555
5555555555[/tt]
are all the same phone number.


Something like

Public Function gdPhone(phoneVar) As String

Dim bldStr As String, X As Integer
phoneVar = Nz(phoneVar, "")
bldStr = ""

For X = 1 To Len(phoneVar)
If IsNumeric(Mid(phoneVar, X, 1)) Then
bldStr = bldStr & Mid(phoneVar, X, 1)
End If
Next X

gdPhone = bldStr

End Function

HTH


John






When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.