VBA‎ > ‎

Refresh issue with an Access continuous form with new record

We had an Access continuous form in an Access project (.adp file where Access is a front end to a SQL Server database).   Whenever a user added a new record, we needed to know the autonumber ID that had been assigned to the new record so that we could execute some VBA code using that autonumber ID.
Problem 2:
You normally can't retrieve the autonumber ID on a new record until you execute the me.refresh command.  When you execute the me.refresh command in an Access continuous form, the cursor (focus of the form) automatically switched away from the new record and instead the focus of the cursor when to the first record.
VBA code solution:
If Me.NewRecord Then
    'Run this so you can get assigned an autonumber by the database
    'Now run this code to retrieve the autonumber you were just given and store that ID in a variable
    Dim intPDFCheckingID As Integer
    Dim objRS4 As ADODB.Recordset
    Set objRS4 = CreateObject("ADODB.Recordset")
    Dim strSQL4 As String
    strSQL4 = " SELECT max(pdfcheckingid) as ID from tbltable where jobid = " & Forms!frmJobs!cbJob
    objRS4.Open strSQL4, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    If Not (objRS4.EOF And objRS4.BOF) Then
        Do While Not objRS4.EOF 'Start looping through the records
            intPDFCheckingID = objRS4!Id
    End If
    Set objRS4 = Nothing
    'Now navigate to it - this overcomes problem 2 described above
        Dim rst2 As Recordset
        Set rst2 = Me.RecordsetClone
        rst2.Find "Pdfcheckingid = " & intPDFCheckingID
        Me.Bookmark = rst2.Bookmark
        'Now set focus on the field the user wants it to be on
        DoCmd.GoToControl "ScheduleBeginDate"
End If