BeforeUpdate Problem

G

Guest

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
 
G

Guest

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
 
G

Guest

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.

Klatuu said:
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


Sher said:
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
Click to expand...
Click to expand...
 
G

Guest

Is Security the name of the table that is the record source of your form?
--
Dave Hargis, Microsoft Access MVP


Sher said:
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.

Klatuu said:
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


Sher said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hello BruceM,

What exactly do you mean?

BruceM said:
Start by making sure the field and the control have different names.

Sher said:
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
Click to expand...
Click to expand...
 
G

Guest

Hellow Klatuu,

Yes, Security is the name of the table.

Klatuu said:
Is Security the name of the table that is the record source of your form?
--
Dave Hargis, Microsoft Access MVP


Sher said:
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.

Klatuu said:
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
Click to expand...
Click to expand...
Click to expand...
 
B

BruceM

Is SSN a field in a table? Is it also the name of a text box on the form?
In the Access world a text box is a control, as is just about anything else
(combo box, label, check box, line, etc.) you can put onto a form or report.
If the field and the control (text box) bound to the field both have the
same names, Access can get confused. Access doesn' t help matters by using
the name of the field as the default name for a text box bound to that field
when you add the field by dragging it from a field list. I think the form
wizard does the same thing. I would use txtSSN as the name of the text box,
and keep SSN as the name of the field, but the main point is to be
consistent.

Having explained my meaning, I will refer you to Klatuu's part of this
thread for further assistance.

Sher said:
Hello BruceM,

What exactly do you mean?

BruceM said:
Start by making sure the field and the control have different names.

Sher said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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


Sher said:
Hellow Klatuu,

Yes, Security is the name of the table.

Klatuu said:
Is Security the name of the table that is the record source of your form?
--
Dave Hargis, Microsoft Access MVP


Sher said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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

Klatuu said:
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


Sher said:
Hellow Klatuu,

Yes, Security is the name of the table.

Klatuu said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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


Sher said:
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

Klatuu said:
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


Sher said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

The code is on the BeforeUpdate event under the txtSSN field.

Klatuu said:
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


Sher said:
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

Klatuu said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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


Sher said:
The code is on the BeforeUpdate event under the txtSSN field.

Klatuu said:
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


Sher said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I keep getting Compile error: Expected Sub, Function, or Property for
strSaveSSN

Klatuu said:
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


Sher said:
The code is on the BeforeUpdate event under the txtSSN field.

Klatuu said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Had I not been in a hurry, I would have done it correctly, my apologies.
Corrected version:

Private Sub SSN_BeforeUpdate(Cancel As Integer)
Dim strSaveSSN As String
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


Sher said:
I keep getting Compile error: Expected Sub, Function, or Property for
strSaveSSN

Klatuu said:
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


Sher said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Klatuu thanks for your help with this. I think maybe I am tired and need to
step away because it keeps giving me errors. Been at this all day. This is
my last issue with the database before release. Suppose to have an early
morning meeting. May have to postpone.

Do appreciate you working with me.

Klatuu said:
Had I not been in a hurry, I would have done it correctly, my apologies.
Corrected version:

Private Sub SSN_BeforeUpdate(Cancel As Integer)
Dim strSaveSSN As String
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


Sher said:
I keep getting Compile error: Expected Sub, Function, or Property for
strSaveSSN

Klatuu said:
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
Click to expand...
Click to expand...
Click to expand...
 
B

BruceM

I assume that SSN is a text field, but can't find a specific statement to
that effect in this thread. The code assumes it is a text field.

Try adding a message box or Debug.Print before the DLookup line of code:
msgbox DLookup("[SSN]", "Security", "[SSN]= '" & Me![txtSSN] & "'"))
Then type an existing SSN into the box.
Excuse me if I am stating the obvious or something that has already been
discussed, but if you are searching for a SSN you would typically type the
value into an unbound text box (or select it from an unbound combo box). If
txtSSN is bound to the SSN field then SSN will equal what you just typed
into the text box.

Sher said:
Klatuu thanks for your help with this. I think maybe I am tired and need
to
step away because it keeps giving me errors. Been at this all day. This
is
my last issue with the database before release. Suppose to have an early
morning meeting. May have to postpone.

Do appreciate you working with me.

Klatuu said:
Had I not been in a hurry, I would have done it correctly, my apologies.
Corrected version:

Private Sub SSN_BeforeUpdate(Cancel As Integer)
Dim strSaveSSN As String
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


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

BeforeUpdate event 3
Check for duplicate before update 9
How to pass multiple Fields to new Form 2
Error Message with Duplicate Values 10
Replace Access error message with more descriptive message. 4
Forms 3
Autofill 3
Coding Help - Please 4

Top