Check Address (Win2k Access2k)

Topic

New Reply

Am working on a database to track Mobile Homes and Travel Trailers distributed to various addresses.

Sometimes more than one unit is dispatched to the same address (i.e. a small MH or RV Park without Lot numbers).

When a dispatch address is entered into the dispatch form I’d like code behind that textbox to: “If the address already exists in the database, display a message that alerts the user to that fact and asks them if they really want to use that address again with the options available to either proceed or cancel the entry”. I’m assuming it would be something like the “item not in list – do you want to add it?” routine published in various places which I’ve used successfully in the past, but am suck at the moment.

Any help would be much appreciated.

Thanks,

Reply | Quote

Replies

In the before update of the textbox you could use some code like this:

Private Sub AddressTextBox_BeforeUpdate(Cancel As Integer)
Dim Response As Long
Me.RecordsetClone.Findfirst "[AdresFieldName]=" & Me.AddressTextBox
If Me.RecordsetClone.nomatch = False Then
    Response = MsgBox("Address exist already. Do you want to use it ?", vbYesNo)
    If Response = vbYes Then
        Exit Sub
    Else
        Cancel = True
    End If
End If
End Sub

If your address is composed of multiple fields, you could do this in the before update of the form and check for the different fields.

Reply | Quote

Francois:

Thanks much for the direction …

Reply | Quote

Francois:

Thanks much for the direction …

Reply | Quote

In the before update of the textbox you could use some code like this:

Private Sub AddressTextBox_BeforeUpdate(Cancel As Integer)
Dim Response As Long
Me.RecordsetClone.Findfirst "[AdresFieldName]=" & Me.AddressTextBox
If Me.RecordsetClone.nomatch = False Then
    Response = MsgBox("Address exist already. Do you want to use it ?", vbYesNo)
    If Response = vbYes Then
        Exit Sub
    Else
        Cancel = True
    End If
End If
End Sub

If your address is composed of multiple fields, you could do this in the before update of the form and check for the different fields.

Reply | Quote