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

[banderson]

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

I have a form frmOutreachRefe rral 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 tblOutreachAdmi n with its primary key (autonumber) field called OutreachID. If a referral is needed, you click on a button to open frmOutreachrefe rral.

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

frmOutreachRefe rral (and tlnkOutreachRef erral) 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 "btnOutrchA dd" with the code below on frmOutreachRefe rral, I open the tblOutreachAdmi n recordset, add a new record, and copy info from frmOutreachRefe rral into the new record. What I then need to do is copy the new OutreachID created by the new record in tblOutreachAdmi n and put it into the txtOutreachID field in the frmOutreachRefe rral.

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

Private Sub btnOutrchAdd_Click()
On Error GoTo Err_btnOutrchAdd_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSite As String
Dim dtRqst As Date
Dim strScope As String
Dim lngOutreachID As Long

Set db = CurrentDb
Set rs = db.OpenRecordset("tblOutrchAdmin")

  strSite = Forms!frmSV.txtSiteID
  dtRqst = Me.txtDateReferred
  strScope = Me.txtReferralScope

rs.AddNew
rs("SiteID") = strSite
rs("DateRqstRcd") = dtRqst
rs("ScopeActivity") = strScope
rs.Update

'Here is my failed attempt to copy the OutreachID value from teh new record.
lngOutreachID = rs("OutreachID").Value

rs.Close

Me.txtOutreachID = lngOutreachID

MsgBox "An Outreach Ticket has been opened for this followup activity."

Exit_btnOutrchAdd_Click:
    Exit Sub

Err_btnOutrchAdd_Click:
    MsgBox Err.Description
    Resume Exit_btnOutrchAdd_Click
End Sub

[TheSmileyCoder]

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:

myRS.Update
myRS.MoveLast
msgBox myRS!ID_Field

[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

Private Sub btnOutrchAdd_Click() 
On Error GoTo Err_btnOutrchAdd_Click 
  
Dim db As DAO.Database 
Dim rs As DAO.Recordset 
Dim strSite As String 
Dim dtRqst As Date 
Dim strScope As String 
Dim lngOutreachID As Long 
  
Set db = CurrentDb 
Set rs = db.OpenRecordset("tblOutrchAdmin") 
  
  strSite = Forms!frmSV.txtSiteID 
  dtRqst = Me.txtDateReferred 
  strScope = Me.txtReferralScope 
  
rs.AddNew 
rs("SiteID") = strSite 
rs("DateRqstRcd") = dtRqst 
rs("ScopeActivity") = strScope 
rs.Update 
rs.MoveLast

lngOutreachID = rs!OutreachID
  
rs.Close 
  
Me.txtOutreachID = lngOutreachID 
  
MsgBox "An Outreach Ticket has been opened for this followup activity." 
  
Exit_btnOutrchAdd_Click: 
    Exit Sub 
  
Err_btnOutrchAdd_Click: 
    MsgBox Err.Description 
    Resume Exit_btnOutrchAdd_Click 
End Sub