Refresh issue with an Access continuous form with new record

Problem1:

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

Me.Refresh

'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

objRS4.MoveNext

Loop

End If

objRS4.Close

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

rst2.Close

'Now set focus on the field the user wants it to be on

DoCmd.GoToControl "ScheduleBeginDate"

End If