Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    48

    Unanswered: Display autonumber in a message box

    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
    Code:
      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
    
    Exit_cmdAdd_Click:
        Exit Sub
    
    Err_cmdAdd_Click:
            MsgBox Err.Description
            Resume Exit_cmdAdd_Click
            End If
    Thank you

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    You have not indicated the Error you are receiving and trust me, there are LOTS of them. You also have not described the sequence of events that led to the error. Not enough information here.

    Note:
    The AutoNumber is not generated until a new record is Dirty which at that point means the record may be auto updated (saved).


    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Sep 2004
    Posts
    48
    When I have the code the way it is I get the following,
    MS Office Access can't find the form 'frmNCR' referred to in a macro expression or VB Code

    This was done on a Click Event

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Is that form open when the code starts?
    Paul

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    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:

    Code:
    Dim RecNum As Long
    RecNum = DLast("[myTablesAutoGenRecordNumber]", "myTableName", "[myTablesAutoGenRecordNumber] > 0")
    MsgBox"The next predicted AutoNumber will be:  " &  RecNum + 1
    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •