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