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
- 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