Finding Existing Record and Moving to it

[padlocked17]

What is the best way to locate a record and then move to that record on a form? I have a field that is checking the SSN of a person and I need to if a record already exists with that SSN it will display a MsgBox and then take the user to the record of the person already entered in there with that SSN.

Currently I had a recordset that is setting a bookmark and then using :

Me.Bookmark = rsc.bookmark

I've got it detecting if there is an existing entry just fine, I just can't get it to navigate to that record.

Thanks!

 

[Oldsoftboss]

Access wizard creates code when you add a listbox or combobox and select the 3rd option "Find a record on my form"

The code would go something like this:

Dim rs as Object
Set rs = Me.Recordset.Clone
rs.FindFirst "SSN = " & Me.SSN
Me.Bookmark = rs.Bookmark

Just substitue the fields in the "FindFirst" line with your fields

Dave

 

[Oldsoftboss]

I have a field that is checking the SSN of a person and I need to if a record already exists with that SSN it will display a MsgBox and then take the user to the record of the person already entered in there with that SSN.

Opps, I think I should have read the question.

I use a DCount to find existing records

Put it in the Before Update event of the field...

Dim Counter As Long

Counter = DCount("*", "tblContractors", "ContractorName = Forms![frmAddContractor]!ContractorName")

If Counter > 0 Then
MsgBox "This Contractor already exists, Please enter a different Contractor Name. ", vbExclamation
Me.Undo
Exit Sub
End If

You could use a Yes/No MsgBox and and If statement to go to the existing record.

 

[Oldsoftboss]

Dim Counter As Long
Dim rs as Object

Counter = DCount("*", "tblContractors", "ContractorName = Forms![frmAddContractor]!ContractorName")

If Counter > 0 Then
If MsgBox ("This Contractor already exists, Do you want to view details. ", vbExclamation + vbYesNo) = vbYes then
Set rs = Me.Recordset.Clone
rs.FindFirst "SSN = " & Me.SSN
Me.Bookmark = rs.Bookmark
else
Me.Undo
Exit Sub
End If
End If

 

[padlocked17]

I'll give that a shot. Right now what I was trying to work with was below.

Private Sub SSN_LostFocus()
    Dim SSN As String
    Dim ssnLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SSN = Me.SSN.Value
    ssnLinkCriteria = "[SSN]=" & "'" & SSN & "'"

    'Check tblstudents table for duplicate SSN
    If DCount("SSN", "tblStudents", _
              ssnLinkCriteria) > 0 Then
        
        'Undo the duplicate entry
        Me.Undo
        
        'Message box warning of duplication
        MsgBox "Warning Student Number " _
             & SSN & " has already been entered." _
             & vbCr & vbCr & "You will now been taken to the record.", _
               vbInformation, "Duplicate Information"
        
        'Make the combo box visible
        Me.cboSelectStudent.Visible = True
        
        'Go to record of original Student Number
        rsc.FindFirst ssnLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If

    Set rsc = Nothing
End Sub

 

[padlocked17]

Turns out the following code DOES affect the record from pulling up correctly. Any ideas on how to correct this?

Basically I want to have the form fields hidden when the form loads and then have them shopw when a duplicate record is found. It appears the bookmark attribute actually requeries the form so that the Form_Current is run again.

Private Sub Form_Current()
    'When the form loads, hide everything except the combo box
    If Me.cboSelectStudent & "" = "" Then
        Me.SSN.Visible = False
        Me.LastName.Visible = False
        Me.FirstName.Visible = False
        Me.MI.Visible = False
        Me.cboRankID.Visible = False
        Me.cboUnitID.Visible = False
        Me.StatusID.Visible = False
        Me.Status.Visible = False
        Me.SemsPro.Visible = False
        Me.lblUndo.Visible = False
        Me.cmdUndo.Visible = False
    Else
        Me.SSN.Visible = True
        Me.LastName.Visible = True
        Me.FirstName.Visible = True
        Me.MI.Visible = True
        Me.cboRankID.Visible = True
        Me.cboUnitID.Visible = True
        Me.StatusID.Visible = True
        Me.Status.Visible = True
        Me.SemsPro.Visible = True
        Me.lblUndo.Visible = True
        Me.cmdUndo.Visible = True
    End If
    'Set the focus on the combo box so the focus isn't on a hidden field
    Me.cboSelectStudent.SetFocus
End Sub

 

[Oldsoftboss]

If Me.cboSelectStudent & "" = "" Then

Dont understand & "" = ""