Before Update problem 1

[mommom]

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

 

[mommom]

Can someone please help me with this. I just don't know why it keeps going to the first record instead of opening the record with the SSN that was put in.

 

[missinglinq]

Try DoCmd.FindRecord "[SSN]= '" & Me![SSN] & "'"

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[TheAceMan1]

mommom . . .

The question is . . . what do you want to do if the SSN exist?

See Ya! . . . . . .

 

[mommom]

Hello AceMan,

If the SSN exist I want the record to open up.

 

[mommom]

missinglinq,

I tried as listed below and it still doesn't work.


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]= '" & Me![SSN] & "'"
End If

End Sub

 

[PHV]

And what about this ?

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
    Me.Recordset.FindFirst "SSN='" & Me![SSN] & "'"     
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mommom]

Hello PHV,

When I put the code in after it says "This SSN already exists."

The field goes blank and doesn't bring up the information.

 

[PaulF]

doesn't

me.undo

remove the value you want to search for?

you need to capture that to a variable and then search for the variable

Private Sub SSN_BeforeUpdate(Cancel As Integer)

Dim strSSN As String

If Not IsNull(DLookup("[SSN]", "Security", "[SSN]= '" & Me![SSN] & "'")) Then
MsgBox "This SSN already exists."
strSSN = Me![SSN]
Cancel = True
Me.Undo
DoCmd.GoToRecord , , acFirst
DoCmd.FindRecord "[SSN]= '" & strSSN & "'"
End If

End Sub


PaulF

 

[PHV]

[CODE Revised]
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.Recordset.FindFirst "SSN='" & Me![SSN] & "'"
End If[/CODE]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mommom]

Hello PHV,

Did your code and it gave me a Run-time error '3426': This action was cancelled by an associated object.


Hello Paul,

Tried your code also and it brought me to the first record as my code is doing now.

 

[PaulF]

mommom
I like to use RecordsetClone for this type of action

Private Sub SSN_BeforeUpdate(Cancel As Integer)

Dim strSSN As String

If Not IsNull(DLookup("[SSN]", "Security", "[SSN]= '" & Me![SSN] & "'")) Then
MsgBox "This SSN already exists."
strSSN = Me![SSN]
Cancel = True
Me.Undo

With Me.RecordSetClone
.FindFirst "SSN = '" & strSSN & "'"
If Not .NoMatch Then
Me.Bookmark = Me.RecordSetClone.Bookmark
End If
End With
End If
End Sub

PaulF

 

[mommom]

Thank you Paul that works.

Thank you so much for helping me with this.