access message box/ VB code.

[tania017]

Hello all,

I am trying to code a message box to display the record number of the record the user just added as a confirmation number. The user clicks on the message box to add the record and close out of the form. How do I retrieve the record number just added and display it?

Please e-mail me at: [email removed]

Here is the code for the button:

 
Private Sub Add_Nomination_Click()
On Error GoTo Add_Nomination_Click_Err

    Beep
    MsgBox "Your nomination has been submitted. Thank you!", vbInformation, "Record Added"
    DoCmd.Close acForm, "Nomination Form"

Add_Nomination_Click_Exit:
    Exit Sub

Add_Nomination_Click_Err:
    MsgBox Error$
    Resume Add_Nomination_Click_Exit

End Sub

Thank you for your help!!!

[MMcCarthy]

Hello all,

I am trying to code a message box to display the record number of the record the user just added as a confirmation number. The user clicks on the message box to add the record and close out of the form. How do I retrieve the record number just added and display it?

Please e-mail me at: [email removed]

Here is the code for the button:

 
Private Sub Add_Nomination_Click()
On Error GoTo Add_Nomination_Click_Err

    Beep
    MsgBox "Your nomination has been submitted. Thank you!", vbInformation, "Record Added"
    DoCmd.Close acForm, "Nomination Form"

Add_Nomination_Click_Exit:
    Exit Sub

Add_Nomination_Click_Err:
    MsgBox Error$
    Resume Add_Nomination_Click_Exit

End Sub

Thank you for your help!!!

Do you want to retrieve a unique ID or the count of records?

[NeoPa]

Please include the MetaData for all relevant datasets. Here is an example of how to post table MetaData :

[b]Table Name=tblStudent[/b]
[i]Field; Type; IndexInfo[/i]
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastLesson; Date/Time

MODERATOR

[tania017]

MetaData:

[B]TableName = Nominations[/B]
NominationID; Autonumber; PK
Date of Nomination; Date/Time
Nominated Employee; Text; FK
Nominator; Text; FK
FishPrinciple; Text; FK
Reason For Recognition; Memo

This is a database for an employee recognition program... When a user submits a nomination, I would like them to receive the nominationID # (primary key) through the message box. I am going to create an additional form where users can go in, type in the primary key, and I will write a query to retrieve whether or not the reward has been given out which I will store in a separate table with NominationID as a foreign key. I am comfortable doing the rest - but I have no idea how to write the code to give the user the nomination ID for the record they just added!

Thanks so much.

[NeoPa]

Nicely done with the MetaData Tania. You get the prize for being the first person, so far, to follow the format accurately :D
Funnily enough, there's no really accurate (direct) way of doing this unless you know something about how it works that would help to identify the record just entered.
I will assume, unless told otherwise, that the last record entered is the one required. DLast() may work in place of DMax() but it's hard for me to test that it will in all circumstances so it would be a good idea for you to test that to your satisfaction first. I include the code for using DLast() but commented out. Only use one of the lines in your final version.

Private Sub Add_Nomination_Click()
On Error GoTo Add_Nomination_Click_Err
    Dim strMsg As String

    'Code to add the record here

    'strMsg = DLast([NominationID]", "[Nominations]")
    strMsg = Mid(DMax("Format([Date of Nomination]," & _
                      "'yyyymmdd') & [NominationID]", _
                      "[Nominations]"), 9)
    strMsg = Replace("Your nomination ([NominatinID]=%N) " & _
                     "has been submitted. Thank you!", _
                     "%N", strMsg)
    Beep
    Call MsgBox(strMsg, vbInformation, "Record Added")
    Call DoCmd.Close(acForm, Me.Name)

Add_Nomination_Click_Exit:
    Exit Sub

Add_Nomination_Click_Err:
    MsgBox Error$
    Resume Add_Nomination_Click_Exit

End Sub

[tania017]

Thank you! I took a database programming course in college and remember metadata vaguely! =)

Sorry for the extreme delay in my response - I will give the code a shot. Thanks soo much!

[tania017]

This works great!!!! I have to add 1 to the nomination ID the code pulls in, but this is just what I was looking for. Thank you so much! =)

Nicely done with the MetaData Tania. You get the prize for being the first person, so far, to follow the format accurately :D
Funnily enough, there's no really accurate (direct) way of doing this unless you know something about how it works that would help to identify the record just entered.
I will assume, unless told otherwise, that the last record entered is the one required. DLast() may work in place of DMax() but it's hard for me to test that it will in all circumstances so it would be a good idea for you to test that to your satisfaction first. I include the code for using DLast() but commented out. Only use one of the lines in your final version.

Private Sub Add_Nomination_Click()
On Error GoTo Add_Nomination_Click_Err
    Dim strMsg As String

    'Code to add the record here

    'strMsg = DLast([NominationID]", "[Nominations]")
    strMsg = Mid(DMax("Format([Date of Nomination]," & _
                      "'yyyymmdd') & [NominationID]", _
                      "[Nominations]"), 9)
    strMsg = Replace("Your nomination ([NominatinID]=%N) " & _
                     "has been submitted. Thank you!", _
                     "%N", strMsg)
    Beep
    Call MsgBox(strMsg, vbInformation, "Record Added")
    Call DoCmd.Close(acForm, Me.Name)

Add_Nomination_Click_Exit:
    Exit Sub

Add_Nomination_Click_Err:
    MsgBox Error$
    Resume Add_Nomination_Click_Exit

End Sub

[NeoPa]

You're very welcome Tania.
I can't believe I missed out adding one to the ID, but I'm glad you caught it anyway.