Before Update problem

[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 with the information, it opens the first record (totally different SSN). 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

 

[mhartman1]

Hello:

The command "DoCmd.GoToRecord , , acFirst"
tells it to go to the first record of the recordset:

Regards
Mark

 

[mhartman1]

Hello

You want to use the "FindFirst" method to do this.
'
'



FindFirst, FindLast, FindNext, FindPrevious Methods Example

This example uses the FindFirst, FindLast, FindNext, and FindPrevious methods to move the record pointer of a Recordset based on the supplied search string and command. The FindAny function is required for this procedure to run.

Sub FindFirstX()

Dim dbsNorthwind As Database
Dim rstCustomers As Recordset
Dim strCountry As String
Dim varBookmark As Variant
Dim strMessage As String
Dim intCommand As Integer

Set dbsNorthwind = OpenDatabase("Northwind.mdb")
Set rstCustomers = dbsNorthwind.OpenRecordset( _
"SELECT CompanyName, City, Country " & _
"FROM Customers ORDER BY CompanyName", _
dbOpenSnapshot)

Do While True
' Get user input and build search string.
strCountry = _
Trim(InputBox("Enter country for search."))
If strCountry = "" Then Exit Do
strCountry = "Country = '" & strCountry & "'"

With rstCustomers
' Populate recordset.
.MoveLast
' Find first record satisfying search string. Exit
' loop if no such record exists.
.FindFirst strCountry
If .NoMatch Then
MsgBox "No records found with " & _
strCountry & "."
Exit Do
End If

Do While True
' Store bookmark of current record.
varBookmark = .Bookmark
' Get user choice of which method to use.
strMessage = "Company: " & !CompanyName & _
vbCr & "Location: " & !City & ", " & _
!Country & vbCr & vbCr & _
strCountry & vbCr & vbCr & _
"[1 - FindFirst, 2 - FindLast, " & _
vbCr & "3 - FindNext, " & _
"4 - FindPrevious]"
intCommand = Val(Left(InputBox(strMessage), 1))
If intCommand < 1 Or intCommand > 4 Then Exit Do

' Use selected Find method. If the Find fails,
' return to the last current record.
If FindAny(intCommand, rstCustomers, _
strCountry) = False Then
.Bookmark = varBookmark
MsgBox "No match--returning to " & _
"current record."
End If

Loop

End With

Exit Do
Loop

rstCustomers.Close
dbsNorthwind.Close

End Sub

Function FindAny(intChoice As Integer, _
rstTemp As Recordset, _
strFind As String) As Boolean

' Use Find method based on user input.
Select Case intChoice
Case 1
rstTemp.FindFirst strFind
Case 2
rstTemp.FindLast strFind
Case 3
rstTemp.FindNext strFind
Case 4
rstTemp.FindPrevious strFind
End Select

' Set return value based on NoMatch property.
FindAny = IIf(rstTemp.NoMatch, False, True)

End Function