473,324 Members | 2,473 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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

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.  

2 2809
TheSmileyCoder
2,322 Expert Mod 2GB
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
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

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

Similar topics

9
by: Roger Withnell | last post by:
I'm inserting a new record into an MS SQL database table and I want to obtain the new records autonumber immediately afterwards, as follows: MadminRS.CursorLocation = adUseServer...
4
by: Jack | last post by:
Hi, I have a checkbox the value which goes to a database via a asp page that builds the sql string. In the front end asp page, the checkbox code is written as follows: <i><input...
4
by: Phillip J. Allen | last post by:
Hi all, I have a table with an “autonumber” primary key field that also acts as a foreign key in 2 other tables. I would like to programmatically add a new record to the first table and the...
5
by: Mads Petersen | last post by:
Hi, and thanks for previous help. I use following code to export from excel to access. It is executed in excel. I have an excel spreadsheet with one sheet pr. week. this code is therefore...
3
by: david | last post by:
Hi, I've been reading tons of posts on how to copy records, but to no avail....i'm still stuck. There are three tables: Main, Sub-Form1 & Sub-Form2 I have a form which displays some data....
2
by: josephm | last post by:
Hello Group: My first post on the group.Hope I get a response. I have a modest Fire Insurance ACCES Db.Thanks to this group - for the code. A "wanna be programmer"... "LEARNS" The code...
7
by: Eric | last post by:
I want to open up 2 record sets. Then, copy everything from one record set to another (excluding a few fields). Is this possible? I need to exclude my primary key, and fields starting with...
3
by: Richnep | last post by:
Hi all, I have tabbed subforms where I need to copy one field value from one subform over to another subform. Although I can run an update query to accomplish this I would like to do it through...
3
by: Jiwei06xie | last post by:
Dear expert, I got two forms. form1: Combox form Form_rptStock (key field: serial number), with one command button. form2: Entryform Entryform_stock, with a series of command buttons. I use...
8
by: OldBirdman | last post by:
I want to copy ALL fields of a record (except Key) to another record. Dim rstSource As DAO.Recordset Dim rstTarget As DAO.Recordset Dim fld As Field Dim iiSourceKey As Long Dim iiTargetKey As...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.