469,266 Members | 1,679 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,266 developers. It's quick & easy.

access message box/ VB code.

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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Add_Nomination_Click()
  3. On Error GoTo Add_Nomination_Click_Err
  4.  
  5.     Beep
  6.     MsgBox "Your nomination has been submitted. Thank you!", vbInformation, "Record Added"
  7.     DoCmd.Close acForm, "Nomination Form"
  8.  
  9. Add_Nomination_Click_Exit:
  10.     Exit Sub
  11.  
  12. Add_Nomination_Click_Err:
  13.     MsgBox Error$
  14.     Resume Add_Nomination_Click_Exit
  15.  
  16. End Sub
  17.  


Thank you for your help!!!
Feb 15 '07 #1
7 21091
MMcCarthy
14,534 Expert Mod 8TB
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Add_Nomination_Click()
  3. On Error GoTo Add_Nomination_Click_Err
  4.  
  5.     Beep
  6.     MsgBox "Your nomination has been submitted. Thank you!", vbInformation, "Record Added"
  7.     DoCmd.Close acForm, "Nomination Form"
  8.  
  9. Add_Nomination_Click_Exit:
  10.     Exit Sub
  11.  
  12. Add_Nomination_Click_Err:
  13.     MsgBox Error$
  14.     Resume Add_Nomination_Click_Exit
  15.  
  16. End Sub
  17.  


Thank you for your help!!!
Do you want to retrieve a unique ID or the count of records?
Feb 15 '07 #2
NeoPa
32,171 Expert Mod 16PB
Please include the MetaData for all relevant datasets. Here is an example of how to post table MetaData :
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblStudent
  2. Field; Type; IndexInfo
  3. StudentID; Autonumber; PK
  4. Family; String; FK
  5. Name; String
  6. University; String; FK
  7. Mark; Numeric
  8. LastLesson; Date/Time
MODERATOR
Feb 16 '07 #3
MetaData:
Expand|Select|Wrap|Line Numbers
  1. TableName = Nominations
  2. NominationID; Autonumber; PK
  3. Date of Nomination; Date/Time
  4. Nominated Employee; Text; FK
  5. Nominator; Text; FK
  6. FishPrinciple; Text; FK
  7. 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.
Feb 16 '07 #4
NeoPa
32,171 Expert Mod 16PB
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Add_Nomination_Click()
  2. On Error GoTo Add_Nomination_Click_Err
  3.     Dim strMsg As String
  4.  
  5.     'Code to add the record here
  6.  
  7.     'strMsg = DLast([NominationID]", "[Nominations]")
  8.     strMsg = Mid(DMax("Format([Date of Nomination]," & _
  9.                       "'yyyymmdd') & [NominationID]", _
  10.                       "[Nominations]"), 9)
  11.     strMsg = Replace("Your nomination ([NominatinID]=%N) " & _
  12.                      "has been submitted. Thank you!", _
  13.                      "%N", strMsg)
  14.     Beep
  15.     Call MsgBox(strMsg, vbInformation, "Record Added")
  16.     Call DoCmd.Close(acForm, Me.Name)
  17.  
  18. Add_Nomination_Click_Exit:
  19.     Exit Sub
  20.  
  21. Add_Nomination_Click_Err:
  22.     MsgBox Error$
  23.     Resume Add_Nomination_Click_Exit
  24.  
  25. End Sub
Feb 17 '07 #5
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!
Mar 23 '07 #6
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Add_Nomination_Click()
  2. On Error GoTo Add_Nomination_Click_Err
  3.     Dim strMsg As String
  4.  
  5.     'Code to add the record here
  6.  
  7.     'strMsg = DLast([NominationID]", "[Nominations]")
  8.     strMsg = Mid(DMax("Format([Date of Nomination]," & _
  9.                       "'yyyymmdd') & [NominationID]", _
  10.                       "[Nominations]"), 9)
  11.     strMsg = Replace("Your nomination ([NominatinID]=%N) " & _
  12.                      "has been submitted. Thank you!", _
  13.                      "%N", strMsg)
  14.     Beep
  15.     Call MsgBox(strMsg, vbInformation, "Record Added")
  16.     Call DoCmd.Close(acForm, Me.Name)
  17.  
  18. Add_Nomination_Click_Exit:
  19.     Exit Sub
  20.  
  21. Add_Nomination_Click_Err:
  22.     MsgBox Error$
  23.     Resume Add_Nomination_Click_Exit
  24.  
  25. End Sub
Mar 23 '07 #7
NeoPa
32,171 Expert Mod 16PB
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.
Mar 24 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Sergio del Amo | last post: by
1 post views Thread by Mike Ridley | last post: by
26 posts views Thread by jamesbeswick | last post: by
2 posts views Thread by Stephen E. Weber | last post: by
5 posts views Thread by Owen Jenkins | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.