472,784 Members | 692 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

A "Copy/Paste" feature into a new record row.....

kcdoell
230 100+
Good morning everyone:

I created a form and set the default view as a continuous form. Basically the form is displaying records in which the user can add or edit new ones. The record source for this form is a query that I built that is based on a table.

I have been working on this for several weeks and now I have been told that many times when a user wants to create a new record, much of the information that is displayed in a preexisting record is the same. Therefore, instead of rekeying in the same information (only making changes on a couple of the fields) they would like to copy the preexisting row and paste it into the “new” record row that is being displayed (the last row that is displayed) much like one would do in Excel.

Currently I have a before update event for writing new records:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. 'When a user is creating a new record the following code inserts the MonthID, YearID and
  3. 'The LocationsID.  It does a Dlookup for the Locations ID when the control cboLocation is
  4. 'blank.
  5.  
  6. Dim frm As Form
  7. Set frm = Forms!Forecast
  8.  
  9. If IsNull(frm![Binding_Percentage]) Then
  10. MsgBox ("You need to select a Binding Percentage before you create a new record")
  11. Cancel = True
  12. Else: Cancel = False
  13. End If
  14.  
  15. If Me.NewRecord Then
  16.   'If cboLocation is Not Null, grab the value from there
  17.   If Not IsNull(frm![cboLocation]) Then
  18.     JtnLocationsID = frm!cboLocation
  19.     YearID = frm!CboYear
  20.     MonthID = frm!CboMonth
  21.   Else      'Forms!Forecast![cboLocation] is Null
  22.     'Check and see if all 3 Controls have values in them
  23.     If Not IsNull(frm![cboDivision]) And Not IsNull(frm![cboWrkReg]) And _
  24.        Not IsNull(frm![cboCreditReg]) Then       'values in all 3 Controls
  25.          JtnLocationsID = DLookup("[JtnLocationsID]", "tblLocationsMM", "[DivisionIDFK] =" & frm![cboDivision] & _
  26.                           " And [WrkRegIDFK] =" & frm![cboWrkReg] & " And [CreditRegIDFK] =" & _
  27.                           frm![cboCreditReg])
  28.          YearID = frm!CboYear
  29.          MonthID = frm!CboMonth
  30.     Else
  31.       'no value in [cboLocation], and 1 or more values are missing in [cboDivision],
  32.       '[cboWrkReg], or [cboCreditReg]
  33.  
  34.       MsgBox "1 or more values are missing in"
  35.  
  36.     End If
  37.   End If
  38. End If
  39. End Sub
  40.  
I played around and discovered that I could, using the mouse, right click in one of the rows and select “copy” and then right click in the last row (the new record line) and select “paste”. Actually this was pointed out to me by one of the people I am working with. What happens though, is that the paste action does not take and it creates a “blank” record of which I can then right click in that row (the newly created “blank” one) and paste again the info. Then it works. I say “blank” because it actually did create a record on my table with the YearID, MonthID, & JtnLocationsID via the above mentioned code. These are fields that are not in the displayed rows, hence why I created the code to write new records to the table.

Given the copy and paste mindset of my end user what would be the best approach to easily satisfy their desire to not have to rekey much of the information that is already in previously displayed rows? I am relatively new at this so don’t know what the best solution. One thing to mentioned is that this particular table does not have any fields where duplicates are not allowed. I guess that is one less issue to deal with.

Does anybody have ideas on how they would approach this?

Thanks,

Keith :-)
Attached Images
File Type: jpg SnapShot5.jpg (50.6 KB, 956 views)
Apr 8 '08 #1
7 8478
missinglinq
3,532 Expert 2GB
Here's some sample code to carry selected values from an existing record into a new record, based on a command button placed in the header, but I suppose it could be in the footer.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CopyPartialRecord2NewRecordButton_Click()
  2.  
  3. ‘Assign field values to be carried forward to variables
  4. MyFirstField = Me.FirstField
  5. MySecondField = Me.SecondField
  6. MyThirdField = Me.ThirdField
  7.  
  8. 'Go to a new record
  9. DoCmd.GoToRecord , , acNewRec
  10.  
  11. 'Plug in old values from variables to new record
  12. Me.FirstField = MyFirstField
  13. Me.SecondField  = MySecondField 
  14. Me.ThirdField = MyThirdField 
  15.  
  16. End Sub
You can modify it to check for Null values before copying, if you desire.

Linq ;0)>
Apr 8 '08 #2
kcdoell
230 100+
Linq:

In my continuous form, if I select a row a small arrow button displays in the far left. Actually, you can see this in the attached image. It does not for say highlight visually all the fields in the row. Does that arrow icon mean that it is highlighting all fields in that particular row so I can reference any of the fields via the code?

Thanks for the reply,

Keith.
Apr 8 '08 #3
kcdoell
230 100+
Here's some sample code ....................

Expand|Select|Wrap|Line Numbers
  1. Private Sub CopyPartialRecord2NewRecordButton_Click()
  2.  
  3. ‘Assign field values to be carried forward to variables
  4. MyFirstField = Me.FirstField
  5. MySecondField = Me.SecondField
  6. MyThirdField = Me.ThirdField
  7.  
  8. 'Go to a new record
  9. DoCmd.GoToRecord , , acNewRec
  10.  
  11. 'Plug in old values from variables to new record
  12. Me.FirstField = MyFirstField
  13. Me.SecondField  = MySecondField 
  14. Me.ThirdField = MyThirdField 
  15.  
  16. End Sub
You can modify it to check for Null values before copying, if you desire.

Linq ;0)>
Linq:

So would I have to define the variables like the following:

Expand|Select|Wrap|Line Numbers
  1. MyFirstField = Me.LOB
  2. MySecondField = Me.UW
  3. MyThirdField = Me.Policy_Type
  4.  
etc......
Apr 8 '08 #4
missinglinq
3,532 Expert 2GB
This is not actually doing a Copy & Paste, but is assigning the fields to variables, going to a new record, then assigning the value of the variables back to the appropriate fields in the new record, so the fields don't have to be hilighted.

The arrow on the left indicates that particular record is the current record, and the sample code will then copy whichever fields of that record you've assigned it in the code. You simply need to replace Me.FirstField, Me.SecondField, etc. with the actual names for the textboxes you wish to copy.

Linq ;0)>
Apr 8 '08 #5
missinglinq
3,532 Expert 2GB
Sorry, we cross posted! Yes, you need to assign the variables just like it did in Post # 4. If you need more than the example showed, just make up more variable names, like MyFourthField, MyFifthField.

Linq ;0)>
Apr 8 '08 #6
kcdoell
230 100+
Thanks, I have a meeting now but I will try that solution and get back to you on my result.

Thanks a lot...

Keith
Apr 8 '08 #7
kcdoell
230 100+
Linq:

Finally got a chance to apply your idea. It worked perfectly! Below is the final solution:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdCopyPasteRec_Click()
  3.  
  4. 'When the user click on the command button it will copy and paste the highlighted record
  5.  
  6. 'Confirm that the user wants to copy the record.
  7.     If MsgBox("You are about to copy the highlighted record," & _
  8.             " Click the ok button to proceed, if not hit cancel", vbOKCancel, vbDefaultButton2) = vbOK Then
  9.  
  10. 'If yes, assign field values to be carried forward to variables
  11.  
  12.     MyField_1 = Me.Policy_Type
  13.     MyField_2 = Me.Effective_Date
  14.     MyField_3 = Me.Expiration_Date
  15.     MyField_4 = Me.Policy_Number
  16.     MyField_5 = Me.Insured_Name
  17.     MyField_6 = Me.UW
  18.     MyField_7 = Me.Broker
  19.     MyField_8 = Me.LOB
  20.     MyField_9 = Me.JtnLocationsID
  21.     MyField_10 = Me.YearID
  22.     MyField_11 = Me.MonthID
  23.  
  24. 'Go to a new record
  25.     DoCmd.GoToRecord , , acNewRec
  26.  
  27. 'Plug in old values from variables to new record
  28.  
  29.     Me.Policy_Type = MyField_1
  30.     Me.Effective_Date = MyField_2
  31.     Me.Expiration_Date = MyField_3
  32.     Me.Policy_Number = MyField_4
  33.     Me.Insured_Name = MyField_5
  34.     Me.UW = MyField_6
  35.     Me.Broker = MyField_7
  36.     Me.LOB = MyField_8
  37.     Me.JtnLocationsID = MyField_9
  38.     Me.YearID = MyField_10
  39.     Me.MonthID = MyField_11
  40.  
  41.     End If
  42. End Sub
  43.  
  44.  
Once again thanks a lot.

I also thought it would be nice if I could get the entire row that is selected to highlight. I put another post out there but no response so far. I saw that I could go to the properties of a field and set the backcolor using the get focus event. That works it highlights the entire column of that field if there are multiple rows displayed. Is there a way to get just the entire row to highlight (the one that is displaying the arrow). I am thinking that this visual is better for the end user rather than the default arrow that displays and I could have an error check to make sure that a row has been selected

Thanks,

Keith.
Apr 9 '08 #8

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

Similar topics

0
by: Kevin Schneider | last post by:
Please forgive me if this is a bit off topic, but I haven't had any takers in other forums. With ASP.NET projects, is it possible to have VS.NET automatically change the configuration options of...
0
by: Tom Clement | last post by:
Hi folks. I have a question about "Copy Local". We have quite a few projects in our solution (35) and some of them reference the very large "Microsoft.mshtml.dll". I was wondering why leave...
2
by: interuser | last post by:
I want to automate deployment that I now do using the "copy project" button. Is there a script that does this or ideas how to make one? I do not want to make a deployment project or use xcopy ,...
0
by: tom777 | last post by:
For some ASP.NET projects, after I have been working on them awhile the "Copy Project..." stops working when I try to copy the project to the web server, which is on another machine. The error...
4
by: Don Wash | last post by:
Hi There! I'm using "Copy Project" function of VS.NET to upload my ASP.NET application to my web server (not my own web server) and they have Front Page Server Extensions installed as well....
1
by: Knepper, Michelle | last post by:
Hi out there, I'm a first-time user of the "Copy ... From..." command, and I'm trying to load a table from a text flat file. http://www.postgresql.org/docs/7.4/static/sql-copy.html I don't...
2
by: Knepper, Michelle | last post by:
Hi, I'm a first-time user of the "Copy ... From..." command, and I'm trying to load a table from a text flat file. http://www.postgresql.org/docs/7.4/static/sql-copy.html I'm getting the...
3
by: ammie65 | last post by:
I have been trying to create a purchase order database in Access, and I have been successful in creating all the tables, queries and reports that I need. I have only one issue: I need to copy the...
4
by: | last post by:
Using VS.NET I am wondering what methods developers use to deploy ASP.NET website content to a remote server, either using FTP or network file copy. Ideally there would be a one-button or...
0
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth

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.