Hi I am trying to display an message to the user that indicates if they want to add a new record on a command click. The message will display the new record (autonumber). If the user chooses to, will select yes/no
Getting an error for my autonumber
getnumber = Forms![frmNCR]![txtNCRNumber]
r = MsgBox("Would you like to add?" & getnumber & " NCR Number", vbYesNo + vbQuestion, "Confirm Addition")
If r = vbYes Then
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmncr"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, Me.Name
DoCmd.GoToRecord , , acNewRec
Like Paul, I also suspect you're trying to read the Form's Autonumber field before the Form is actually loaded. No can do. Like I said, the Autonumber is generated when the form becomes dirty (data is enetered into a field on the Form - even 1 character). If by chance your Form is generating a AutoNumber once it's loaded then I suppose what you can do is open the Form in Hidden mode, read the AutoNumber then close it if it's not acceptable. Do keep in mind though that chances are, a record will be created if that's the case and you will want to remove it from table since it contains no data.
You can however seek and predict what the next AutoNumber may be by simply using the DLast Function. You don't have to open the form to find out what it may be. For example:
Dim RecNum As Long
RecNum = DLast("[myTablesAutoGenRecordNumber]", "myTableName", "[myTablesAutoGenRecordNumber] > 0")
MsgBox"The next predicted AutoNumber will be: " & RecNum + 1
Self Taught In ALL Environments.....And It Shows!