Validate table via form before update macro or vba

  • Thread starter Thread starter headly
  • Start date Start date
H

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?
 
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
 
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!

Allen Browne said:
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.

headly said:
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?
Click to expand...
Click to expand...
 
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.

headly said:
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!

Allen Browne said:
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.

headly said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top