By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,040 Members | 1,815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,040 IT Pros & Developers. It's quick & easy.

how to copy a value from an active record in an open recordset

P: 59
Hello Bytes,
I am having trouble copying a value from an open record in a recordset into a form.

I have a form frmOutreachReferral that is filled in after a Site Visit has been performed and it is determined that the site needs a follow up visit.

A Site Visit is entered into frmSV and is based on tblOutreachAdmin with its primary key (autonumber) field called OutreachID. If a referral is needed, you click on a button to open frmOutreachreferral.

frmOutreachReferral is based on tlnkOutreachReferral. This table has a 1:M relationship with tblOutreachAdmin because there could be multiple referrals for a single site visit. When you enter a record in frmOutreachReferral, you "open a new site visit ticket" by also adding a new record into tblOutreachAdmin based on the info you entered into frmOutreachReferral.

frmOutreachReferral (and tlnkOutreachReferral) includes the fields
ReferralID (unique id - autonumber)
ParentID - the OutreachID of the original Site Visit that needed a referral
DateReferred - the date of the referral
ReferralScope - a memo field describing the follow up needed
OutreachID - the ID for the new Site Visit "ticket" that is opened.

Using the button "btnOutrchAdd" with the code below on frmOutreachReferral, I open the tblOutreachAdmin recordset, add a new record, and copy info from frmOutreachReferral into the new record. What I then need to do is copy the new OutreachID created by the new record in tblOutreachAdmin and put it into the txtOutreachID field in the frmOutreachReferral.

I figure that because the new record is already open, it should be a relatively simple thing to copy the value in the OutreachID field of the new record so that it can be used elsewhere, but I have not yet figured out how to do this. Below in the code is my latest (failed) attempt.

Any advice would be greatly appreciated!!!
Banderson


Expand|Select|Wrap|Line Numbers
  1. Private Sub btnOutrchAdd_Click()
  2. On Error GoTo Err_btnOutrchAdd_Click
  3.  
  4. Dim db As DAO.Database
  5. Dim rs As DAO.Recordset
  6. Dim strSite As String
  7. Dim dtRqst As Date
  8. Dim strScope As String
  9. Dim lngOutreachID As Long
  10.  
  11. Set db = CurrentDb
  12. Set rs = db.OpenRecordset("tblOutrchAdmin")
  13.  
  14.   strSite = Forms!frmSV.txtSiteID
  15.   dtRqst = Me.txtDateReferred
  16.   strScope = Me.txtReferralScope
  17.  
  18. rs.AddNew
  19. rs("SiteID") = strSite
  20. rs("DateRqstRcd") = dtRqst
  21. rs("ScopeActivity") = strScope
  22. rs.Update
  23.  
  24. 'Here is my failed attempt to copy the OutreachID value from teh new record.
  25. lngOutreachID = rs("OutreachID").Value
  26.  
  27. rs.Close
  28.  
  29. Me.txtOutreachID = lngOutreachID
  30.  
  31. MsgBox "An Outreach Ticket has been opened for this followup activity."
  32.  
  33. Exit_btnOutrchAdd_Click:
  34.     Exit Sub
  35.  
  36. Err_btnOutrchAdd_Click:
  37.     MsgBox Err.Description
  38.     Resume Exit_btnOutrchAdd_Click
  39. End Sub
  40.  
Mar 4 '10 #1

✓ answered by banderson

The "MoveLast" did the trick!
Thanks, as always, theSmileyOne!

And I'll just reiterate, generally, how helpful the Bytes forum has been for me!!!

Below is the working code that
- opens a new record in a recordset,
- copies values from a form into the fields of the new recordset
- moves to the last (newly added) record in the recordset
- copies the ID field from this record
- pastes it into a control on the form.

Banderson

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnOutrchAdd_Click() 
  2. On Error GoTo Err_btnOutrchAdd_Click 
  3.  
  4. Dim db As DAO.Database 
  5. Dim rs As DAO.Recordset 
  6. Dim strSite As String 
  7. Dim dtRqst As Date 
  8. Dim strScope As String 
  9. Dim lngOutreachID As Long 
  10.  
  11. Set db = CurrentDb 
  12. Set rs = db.OpenRecordset("tblOutrchAdmin") 
  13.  
  14.   strSite = Forms!frmSV.txtSiteID 
  15.   dtRqst = Me.txtDateReferred 
  16.   strScope = Me.txtReferralScope 
  17.  
  18. rs.AddNew 
  19. rs("SiteID") = strSite 
  20. rs("DateRqstRcd") = dtRqst 
  21. rs("ScopeActivity") = strScope 
  22. rs.Update 
  23. rs.MoveLast
  24.  
  25. lngOutreachID = rs!OutreachID
  26.  
  27. rs.Close 
  28.  
  29. Me.txtOutreachID = lngOutreachID 
  30.  
  31. MsgBox "An Outreach Ticket has been opened for this followup activity." 
  32.  
  33. Exit_btnOutrchAdd_Click: 
  34.     Exit Sub 
  35.  
  36. Err_btnOutrchAdd_Click: 
  37.     MsgBox Err.Description 
  38.     Resume Exit_btnOutrchAdd_Click 
  39. End Sub 
  40.  
  41.  

Share this Question
Share on Google+
2 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
I noticed while trying to replicate your attempts, that the RS would still be at my first existing record. I would have thought that when you do the rs.New and rs.Update that the recordset cursor/bookmark would be at the location of the new record.

To get the newest added record i simply did:
Expand|Select|Wrap|Line Numbers
  1. myRS.Update
  2. myRS.MoveLast
  3. msgBox myRS!ID_Field
Mar 4 '10 #2

P: 59
The "MoveLast" did the trick!
Thanks, as always, theSmileyOne!

And I'll just reiterate, generally, how helpful the Bytes forum has been for me!!!

Below is the working code that
- opens a new record in a recordset,
- copies values from a form into the fields of the new recordset
- moves to the last (newly added) record in the recordset
- copies the ID field from this record
- pastes it into a control on the form.

Banderson

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnOutrchAdd_Click() 
  2. On Error GoTo Err_btnOutrchAdd_Click 
  3.  
  4. Dim db As DAO.Database 
  5. Dim rs As DAO.Recordset 
  6. Dim strSite As String 
  7. Dim dtRqst As Date 
  8. Dim strScope As String 
  9. Dim lngOutreachID As Long 
  10.  
  11. Set db = CurrentDb 
  12. Set rs = db.OpenRecordset("tblOutrchAdmin") 
  13.  
  14.   strSite = Forms!frmSV.txtSiteID 
  15.   dtRqst = Me.txtDateReferred 
  16.   strScope = Me.txtReferralScope 
  17.  
  18. rs.AddNew 
  19. rs("SiteID") = strSite 
  20. rs("DateRqstRcd") = dtRqst 
  21. rs("ScopeActivity") = strScope 
  22. rs.Update 
  23. rs.MoveLast
  24.  
  25. lngOutreachID = rs!OutreachID
  26.  
  27. rs.Close 
  28.  
  29. Me.txtOutreachID = lngOutreachID 
  30.  
  31. MsgBox "An Outreach Ticket has been opened for this followup activity." 
  32.  
  33. Exit_btnOutrchAdd_Click: 
  34.     Exit Sub 
  35.  
  36. Err_btnOutrchAdd_Click: 
  37.     MsgBox Err.Description 
  38.     Resume Exit_btnOutrchAdd_Click 
  39. End Sub 
  40.  
  41.  
Mar 4 '10 #3

Post your reply

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