Validate table via form before update macro or vba

[headly]

When a user enters a new address, I'd like to check to see if it already
exists before updating the table; So, I select the form, show the properties,
and go to the event - before update; What macro instructions or vba would i
use to search for the typed address value before committing the update?

 

[Allen Browne]

If the user is entering an address into a *bound* form, use the BeforeUpdate
event procedure of the *form* (not control) to check if the address already
exists.

This example will need modifying to match your field names:

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

If (Me.Address = Me.Address.OldValue) AND (Me.State = Me.State.OldValue)
AND (Me.Zip = Me.Zip.OldValue) Then
'do nothing
Else
strWhere = "([Address] = """ & Me.Address & """) AND ([State] = """
Me.State & """) AND ([Zip] = """ & Me.Zip & """)"
varResult = DLookup("AddressID", "tblAddress", strWhere)
If Not IsNull(varResult) Then
strMsg = "Duplicate of address " & varResult & vbCrLf & vbCrLf &
"CONTINUE ANYWAY?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Warning") <>
vbYes Then
Cancel = True
Me.Undo
End If
End If
End If
End Sub

 

[headly]

That was incredibly helpful; It would have taken me days to figure out the
VBA you provided; If you have any links to tutorials like that on your site
please show me more kewl stuff. Thx so much!

If the user is entering an address into a *bound* form, use the BeforeUpdate
event procedure of the *form* (not control) to check if the address already
exists.

This example will need modifying to match your field names:

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

If (Me.Address = Me.Address.OldValue) AND (Me.State = Me.State.OldValue)
AND (Me.Zip = Me.Zip.OldValue) Then
'do nothing
Else
strWhere = "([Address] = """ & Me.Address & """) AND ([State] = """
Me.State & """) AND ([Zip] = """ & Me.Zip & """)"
varResult = DLookup("AddressID", "tblAddress", strWhere)
If Not IsNull(varResult) Then
strMsg = "Duplicate of address " & varResult & vbCrLf & vbCrLf &
"CONTINUE ANYWAY?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Warning") <>
vbYes Then
Cancel = True
Me.Undo
End If
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

When a user enters a new address, I'd like to check to see if it already
exists before updating the table; So, I select the form, show the
properties,
and go to the event - before update; What macro instructions or vba would
i
use to search for the typed address value before committing the update?

 

[Allen Browne]

Hmm. It's great that you are wanting to learn rather than just copy'n'paste.
But there are so many factors here.

Form_BeforeUpdate is the right place to do this kind of validation, and is
the *only* event where you can test if a field was left blank or compare a
combination of fields.

For an explanation of DLookup() see:
http://allenbrowne.com/casu-07.html

A really crucial aspect is the handling of nulls, which is the reason the
'do nothing' part looks the way it does. There's quite a bit to grasp on
that:
http://allenbrowne.com/casu-12.html
Ultimately, you don't want to find an existing record as a duplicate if it's
unchanged, but you do want to test if any field changed (including changed
from/to being null.)
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

That was incredibly helpful; It would have taken me days to figure out the
VBA you provided; If you have any links to tutorials like that on your
site
please show me more kewl stuff. Thx so much!

If the user is entering an address into a *bound* form, use the
BeforeUpdate
event procedure of the *form* (not control) to check if the address
already
exists.

This example will need modifying to match your field names:

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

If (Me.Address = Me.Address.OldValue) AND (Me.State =
Me.State.OldValue)
AND (Me.Zip = Me.Zip.OldValue) Then
'do nothing
Else
strWhere = "([Address] = """ & Me.Address & """) AND ([State] =
"""
Me.State & """) AND ([Zip] = """ & Me.Zip & """)"
varResult = DLookup("AddressID", "tblAddress", strWhere)
If Not IsNull(varResult) Then
strMsg = "Duplicate of address " & varResult & vbCrLf &
vbCrLf &
"CONTINUE ANYWAY?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Warning") <>
vbYes Then
Cancel = True
Me.Undo
End If
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

When a user enters a new address, I'd like to check to see if it
already
exists before updating the table; So, I select the form, show the
properties,
and go to the event - before update; What macro instructions or vba
would
i
use to search for the typed address value before committing the update?