I keep getting Compile error: Expected Sub, Function, or Property for
strSaveSSN
:
Sorry about my last dumb question, I was getting your post and
another
confused. (they are similar in nature).
Doh!
I think I just found the problem. The Undo is clearing the txtSSN
control,
so there is nothing to find. Here is a change that saves it to a
variable so
we don't loose it.
Private Sub SSN_BeforeUpdate(Cancel As Integer)
Dim strSaveSSN
If Not IsNull(DLookup("[SSN]", "Security", "[SSN]= '" &
Me![txtSSN] &
"'"))
Then
MsgBox "This SSN already exists."
strSaveSSN Me.txtSSN
Cancel = True
Me.Undo
With Me.RecordsetClone
.FindFirst "[SSN] = '" & strSSN & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub
--
Dave Hargis, Microsoft Access MVP
:
The code is on the BeforeUpdate event under the txtSSN field.
:
Where are you putting the code?
In an event (is so, which event) or in the control source of a
control on
your form.
--
Dave Hargis, Microsoft Access MVP
:
Hello Klatuu,
Maybe it is something that I am doing wrong, but I did change
SSN to txtSSN
and put in your code but when I do that I get a Compile error
Expected:Then
or GoTo
:
Give Bruce's information, if you change the control name, it
may cure the
problem. The code I posted should work.
--
Dave Hargis, Microsoft Access MVP
:
Hellow Klatuu,
Yes, Security is the name of the table.
:
Is Security the name of the table that is the record
source of your form?
--
Dave Hargis, Microsoft Access MVP
:
Klatuu this doesn't work. The only thing is does is
bring up a blank form.
I want it to bring up the correct record.
:
Private Sub SSN_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[SSN]", "Security",
"[SSN]= '" & Me![SSN] & "'"))
Then
MsgBox "This SSN already exists."
Cancel = True
Me.Undo
With Me.RecordsetClone
.FindFirst "[SSN] = '" & Me.SSN & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub
--
Dave Hargis, Microsoft Access MVP
:
Right now if I type in a SSN for
example:111-11-1111 and it says it already
exists. Instead of opening that SSN it opens the
first record. Is there
something else I need to add to the code listed
below?
Private Sub SSN_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[SSN]", "Security", "[SSN]=
'" & Me![SSN] & "'")) Then
MsgBox "This SSN already exists."
Cancel = True
Me.Undo
DoCmd.GoToRecord , , acFirst
DoCmd.FindRecord SSN
End If
End Sub