473,852 Members | 1,772 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

59 New Member
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


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
2 2839
TheSmileyCoder
2,322 Recognized Expert Moderator Top Contributor
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
banderson
59 New Member
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
19937
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 MadminRS.CursorType = adOpenKeyset MadminRS.LockType = adLockOptimistic MadminRS.Open "NavBar", objConn, , , adCmdTable MadminRS.AddNew MadminRS("Url") = Request.Form("Website") MadminRS("ParentRecNo") = 0
4
4634
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 type="checkbox" name="chk_Complete" value="<%Response.Write l_IsChecked%>"<%if cbool(l_IsChecked) then Response.Write " checked"%>> The code to captures the checkbox value in the asp page that builds the sql string is follows
4
6461
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 other 2 child tables. The reason for doing this is to copy one complete record (parent and child table records) into a new record so the user can make a few modifications. So how do I determine what will be the next auto-generated number for my...
5
7237
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 executed on every sheet, and is supposed to be it on comming sheets as well. Therefore I would very much like if I could check if the value in A98 (excel) matches a record in the access table TimeReview, field "Uge". And if it does the export should be...
3
3220
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. The PK (ProductID) for each record on the form is an AUTONUMBER. This form also has a sub-form, linked via ProductID. The subform also
2
2876
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 here-under (from the Archive) duplicates the EXPIRING records but affords the USER to give ONLY A NEW POLICY NO (NewKey),should a client opt to renew upon expiry of the YEARLY POLICY.The NEW POLICY NO is save as a NEW record along with the "old"static...
7
4995
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 "TMP_". I'm trying this, but it's saying ADODB.Recordset error '800a0c93' Operation is not allowed in this context.
3
9257
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 VBA. sub tables relationships are 1:N with the main table. So the recods display like this in the subform:
3
3072
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 this form to add new record or edit/delete a record. (it works perfect independently) What I am trying to do is when I select a serial number from the dropdown and click the command button cmdEdit in the 1st form, I wish the 2nd form will open and...
8
17420
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 Long Set rstSource = RecordsetClone rstSource.FindFirst "Key=" & iiSourceKey Set rstTarget = RecordsetClone
0
9898
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11020
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10735
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10356
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9506
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7073
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5736
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5934
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4143
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.