Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-04-2019, 01:09 PM   #1
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 31
Thanks: 4
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Copy previous record fields into new record form fields

I want to type information into a form and then when I save the record, the input data is used to pre-populate fields on the next record/form.

Goal: The database captures shipping and configuration information for multiple systems going out to one location on the specified date and tracking number. Fed-Ex tracking number, date shipped, PO number, and customer information remain the same for the group of records associated to the shipment. However, multiple systems mat be sent out on each shipment, each needing it's own record. Each system contains multiple serialized components that need to be logged for warranty purposes. Currently each field on the record needs to be completed for every serial number. Ideally, I would like a set group of fields to pre-populate the next record to reduce data entry time. I am fairly new to Access and not familiar with VBA so if you provide code, please tell me exactly where and how to enter it.

A couple of field names that are repeated from record to record include: "Customer", "FedEx Tracking", "Customer PO".
Field names that may have differing information for each record include: "900337-SN", "3Dbarcode", "Software Config".

Thanks in advance for the help.

PuzzledNH is offline   Reply With Quote
Old 11-04-2019, 01:15 PM   #2
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,436
Thanks: 58
Thanked 1,411 Times in 1,392 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Copy previous record fields into new record form fields

Hi. Welcome to AWF! One approach is to set the Default Value of each field to the same value as the one the user just entered. You can use the AfterUpdate event of each control to do this.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-04-2019, 01:25 PM   #3
missinglinq
AWF VIP
 
missinglinq's Avatar
 
Join Date: Jun 2003
Location: Richmond (Virginia that is!)
Posts: 6,319
Thanks: 11
Thanked 721 Times in 671 Posts
missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light
Re: Copy previous record fields into new record form fields

As theDBguy said, you can use the AfterUpdate event of the Control holding your data to set the DefaultValue for the Field. From that time forward, until you either manually change the data or close your form, the data will be entered automatically in each New Record.

Code:
Private Sub YourControlName_AfterUpdate()
   Me.YourControlName.DefaultValue = """" & Me.YourControlName.Value & """"
End Sub

This syntax is valid for Text, Number, DateTime and Boolean Datatypes.

Youll need to do this for each Control that you want to carry forward.

Linq ;0)>

__________________
The Devil's in the Details!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


(All code solutions tested in Access 2003/2007, before posting, unless otherwise noted.)
missinglinq is offline   Reply With Quote
Old 11-04-2019, 03:00 PM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,298
Thanks: 15
Thanked 1,597 Times in 1,517 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Copy previous record fields into new record form fields

Based on the example fields you mentioned, your problem is with table design. This looks like it should be two tables. tblPO and tblPODetail. That way the data you are asking to "duplicate" occurs once in tblPO and the item detail has a row for each item on the PO.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 11-05-2019, 04:49 AM   #5
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 31
Thanks: 4
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Re: Copy previous record fields into new record form fields

Code:
Private Sub YourControlName_AfterUpdate()
   Me.YourControlName.DefaultValue = """" & Me.YourControlName.Value & """"
End Sub


Hi missinglinq,

Thank you. This sounds like what I want to do. However, could you explain this a bit more? When I type this code into the After Update line on the Event tab of the property sheet, I get an error message popup "Expression contains invalid syntax."

For the Product Line form field I typed the following:

= Private Sub Product Line_AfterUpdate()
Me.Product Line.DefaultValue = """" & Me.Product Line.Value & """"
End Sub

I sincerely appreciate this help.

Last edited by PuzzledNH; 11-05-2019 at 05:29 AM.
PuzzledNH is offline   Reply With Quote
Old 11-05-2019, 05:16 AM   #6
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 31
Thanks: 4
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Re: Copy previous record fields into new record form fields

Quote:
Originally Posted by Pat Hartman View Post
Based on the example fields you mentioned, your problem is with table design. This looks like it should be two tables. tblPO and tblPODetail. That way the data you are asking to "duplicate" occurs once in tblPO and the item detail has a row for each item on the PO.

Hi Pat,
Table design is another struggle I am having. I suspect I may need more than 2 tables. Currently, this database has one table with 1200 records. Each record contains multiple fields of data and appears to have originated from an Excel spreadsheet. Each System (or product line) had the shipment details and component serial numbers entered on one line of the spreadsheet. I have the following chunks of data I need to work with:
  • A customer may have multiple Purchase orders.
  • A purchase order may have multiple delivery addresses.
  • A purchase order may have one or multiple product lines.
  • A shipment may have one or more systems containing multiple serialized components, -and/or- individual components going to one address.
  • Each product line will have multiple serialized components and other info (revision, software version, etc.) to track.
  • Each component will have an independent serial number (and/or other info to track).
  • Each shipment will have an independent serial number (and/or other info to track).
  • A component or a system may be shipped to multiple customers (or ordered on multiple purchase orders.

Any suggestions on table layout are very welcome. Thank you.
PuzzledNH is offline   Reply With Quote
Old 11-05-2019, 05:40 AM   #7
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 462
Thanks: 21
Thanked 92 Times in 91 Posts
mike60smart will become famous soon enough
Re: Copy previous record fields into new record form fields

Hi

If you upload a zipped copy of the database we can take a look at your current structure.

mike60smart is offline   Reply With Quote
Old 11-05-2019, 05:49 AM   #8
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,273
Thanks: 96
Thanked 2,030 Times in 1,977 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Copy previous record fields into new record form fields

PuzzledNH,

There are a number of free data models at Barry Williams' site.
You can review, adjust, add to, delete from, combine as needed to model your requirement and get your tbles and relationships set up and tested.

Here are a few that may be useful.
Customers and Orders
Partial Deliveries
Products, Orders, Deliveries

You may also find this useful as you refine and test your model. The model gets you a blueprint for your physical database structure.
Good luck.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 11-05-2019, 07:41 AM   #9
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 31
Thanks: 4
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Re: Copy previous record fields into new record form fields

Quote:
Originally Posted by mike60smart View Post
Hi
If you upload a zipped copy of the database we can take a look at your current structure.
A zipped file is attached.
A couple notes about the file:
  • Table 1 currently contains all the data. Some of the data records were truncated to reduce file size.
  • Table-60cm Dish is a sample list of the different system components of the 60cm product line. This table is not currently in use and is most likely not formatted for data capture.
  • Table-Product Line is a sample list of some of the different product Lines.This table is not currently in use.
  • There are a number of fields in Table1 starting with 60cm-xxxx. These are specific to one product line (60cm Dish). I will need a list of similar fields for each of the other product lines.

Again, I sincerely appreciate any input that can be provided.
Attached Files
File Type: zip Shipment Warranty-v.4.2.zip (272.4 KB, 8 views)
PuzzledNH is offline   Reply With Quote
Old 11-05-2019, 01:25 PM   #10
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,298
Thanks: 15
Thanked 1,597 Times in 1,517 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Copy previous record fields into new record form fields

Yup. Table1 sure is a spreadsheet. You starting organizing it with the form-allFields. Tha looks like at least four tables.

So the question becomes, is the conversion from excel a one time only event and from that forward, you will be using Access to manage the data? If it is, then the process is straight forward. and I can give you an approach to use. Conversion is always iterative. It generally takes a number of queries and since people make mistakes, it usually takes us a few executions to work out all the details. So as you build the necessary append queries, you will also be building a VBA function that runs the queries in the correct order. The function will start by deleting the contents of all the destination tables. It then runs each query and possibly runs counts to make sure that every expected row got added. Once everything works correctly, you do a final conversion and roll out for production but there's a lot of testing of the conversion and the new FE along the way.

It is too early to be building forms or worrying about writing code. Let's concentrate on building tables and getting the conversion underway.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 11-05-2019, 02:06 PM   #11
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 31
Thanks: 4
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Re: Copy previous record fields into new record form fields

Quote:
Originally Posted by Pat Hartman View Post
So the question becomes, is the conversion from excel a one time only event and from that forward, you will be using Access to manage the data? If it is, then the process is straight forward. and I can give you an approach to use.
Hi Pat,
I was provided the database (with inadequate limited data already dumped in by a predecessor) and told to make it work. The end goal is to include all product lines and their component details. Thus, this is a one time conversion that I want to ensure will work properly.

My initial thoughts were to have each product line as a separate table since the components stay the same and only a few fields for each component (serial number, revision, software version, notes, etc.) might vary from shipment to shipment. If I know how to format one product line (such as the 60cm dish in the provided sample) I am sure adding another product line will be fairly straight forward.
Other tables I was considering include one to capture the tracking info, ship date, contract shipped under, and destination. A Customer table is probably warranted (address, phone, contract/PO number, notes, etc.). Right now I am just trying to keep data capture going (record updates) as I figure all this out.

Your help is sincerely appreciated.
PuzzledNH is offline   Reply With Quote
Old 11-05-2019, 03:17 PM   #12
missinglinq
AWF VIP
 
missinglinq's Avatar
 
Join Date: Jun 2003
Location: Richmond (Virginia that is!)
Posts: 6,319
Thanks: 11
Thanked 721 Times in 671 Posts
missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light
Re: Copy previous record fields into new record form fields

Quote:
Originally Posted by PuzzledNH View Post

...When I type this code into the After Update line on the Event tab of the property sheet, I get an error message popup "Expression contains invalid syntax"...
You can't enter this in the AfterUpdate event on the Event Tab in the Properties box...it has to be entered as VBA code, in the Form's code module...as simply

Code:
Private Sub Product Line_AfterUpdate()
   Me.Product Line.DefaultValue = """" & Me.Product Line.Value & """"
End Sub

as I showed.

Linq ;0)>
__________________
The Devil's in the Details!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


(All code solutions tested in Access 2003/2007, before posting, unless otherwise noted.)
missinglinq is offline   Reply With Quote
Old 11-05-2019, 03:32 PM   #13
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,238
Thanks: 115
Thanked 3,074 Times in 2,792 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Copy previous record fields into new record form fields

If Product Line has a space then it must be enclosed in []
Code:
Private Sub Product Line_AfterUpdate()
   Me.[Product Line].DefaultValue = "'" & Me.[Product Line] & "'"
End Sub
NOTE:
.Value can be omitted as its the default property
I've used single quotes (in RED) instead of double double quotes
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 11-07-2019, 04:12 AM   #14
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 31
Thanks: 4
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Re: Copy previous record fields into new record form fields

Quote:
Originally Posted by missinglinq View Post
You can't enter this in the AfterUpdate event on the Event Tab in the Properties box...it has to be entered as VBA code, in the Form's code module...as simply
Linq ;0)>

Hi Linq,
Here is where my ignorance shows.
How do I find the code module for the form?
Thank you.
PuzzledNH is offline   Reply With Quote
Old 11-07-2019, 04:33 AM   #15
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,238
Thanks: 115
Thanked 3,074 Times in 2,792 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Copy previous record fields into new record form fields

As Linq appears to be offline, I'll try and answer

1. In design view, click the product line control. Go to the Event tab on the property sheet, select After Update then click the small ellipsis () button and select code builder.
The first and last lines are added by Access. Type in the middle line of code

OR
2. Open the VBE. Select your form and control then select after update. Finally add the code as above

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Form to display data based on previous record but not all fields gocoder Forms 1 01-22-2015 06:26 PM
How to auto copy fields from one record, and paste to current record? I_M_Desperate Macros 9 01-26-2013 09:26 AM
Copy Some Fields from Current Record only to new record in the same form Sandi09 Forms 5 11-02-2012 09:58 PM
Copy button in a Form to copy fields to other fields in the same record Quinten Forms 3 09-24-2005 10:39 AM
Problem using MS's code to fill fields from previous record's fields yuhui Forms 3 10-15-2003 12:22 PM




All times are GMT -8. The time now is 03:38 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World