Recordset & Bookmark Issues

[padlocked17]

I've got the following code and I'm running into an issue with moving to the record. For some reason it isn't selecting and going to the correct record.

Any suggestions on how to use the bookmark here?

Option Compare Database

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
    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
    End If
    'Set the focus on the combo box so the focus isn't on a hidden field
    Me.cboSelectStudent.SetFocus
End Sub

Private Sub cboSelectStudent_AfterUpdate()
    'When a selection is made in the combo box, make fields visible
    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.SemsPro.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.SemsPro.Visible = True
    End If
End Sub

Private Sub cboSelectStudent_Change()
Dim class As Integer
    class = Me!cboSelectStudent
    DoCmd.ShowAllRecords
    Me!txtStudentID.SetFocus
    DoCmd.FindRecord class
    

End Sub

'******************************************
'
'I need to get the Bookmark to pull up the record
'
'
'******************************************

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

 

[NickH1]

Using LostFocus to do the update makes the flow of code here difficult to work out. Are you sure it will lose focus? Why not use AfterUpdate and then debug your code looking at the bookmark value. Showallrecords will update the recordset and invalidate bookmarks.

 

[ajetrumpet]

For some reason it isn't selecting and going to the correct record.

I am wondering where you think the problem lies within the module? Can you point to a few specific lines that you are questioning??

 

[NickH1]

What are you trying to do here? It looks like you are trying to display a record after selecting from a combo but also trying to add a new record if the entry you are looking fro does not exist? Is that right?

 

[padlocked17]

What are you trying to do here? It looks like you are trying to display a record after selecting from a combo but also trying to add a new record if the entry you are looking fro does not exist? Is that right?

Correct. All fields are hidden on form open. Once a name is selected from the combo box OR the "Add New Student" button is clicked, all fields are shown and once a SSN is entered I don't want the user to be able to fill out the rest of the fields if there is already a duplicate.

 

[NickH1]

Correct. All fields are hidden on form open. Once a name is selected from the combo box OR the "Add New Student" button is clicked, all fields are shown and once a SSN is entered I don't want the user to be able to fill out the rest of the fields if there is already a duplicate.

I would do this by making the combo-box where the Student-id was selected 'unbound', but make its source the same list of students. In the AfterUpdate of the combo Find the matching record using a recordsetclone and then set the form bookmark to the clone bookmark, then display the fields. Use either the NotInList event to let the user know you can't find it or the failure of the Find (FindFirst). If they say OK then goto a new record to add.