Copy previous record fields into new record form fields (1 Viewer)

PuzzledNH

Registered User.
Local time
Today, 08:46
Joined
Nov 4, 2019
Messages
36
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:46
Joined
Oct 29, 2018
Messages
21,357
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.
 

missinglinq

AWF VIP
Local time
Today, 08:46
Joined
Jun 20, 2003
Messages
6,423
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.

You’ll need to do this for each Control that you want to ‘carry forward.’

Linq ;0)>
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:46
Joined
Feb 19, 2002
Messages
42,970
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.
 

PuzzledNH

Registered User.
Local time
Today, 08:46
Joined
Nov 4, 2019
Messages
36
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:

PuzzledNH

Registered User.
Local time
Today, 08:46
Joined
Nov 4, 2019
Messages
36
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.
 

mike60smart

Registered User.
Local time
Today, 12:46
Joined
Aug 6, 2017
Messages
1,899
Hi

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

jdraw

Super Moderator
Staff member
Local time
Today, 08:46
Joined
Jan 23, 2006
Messages
15,361

PuzzledNH

Registered User.
Local time
Today, 08:46
Joined
Nov 4, 2019
Messages
36
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.
 

Attachments

  • Shipment Warranty-v.4.2.zip
    272.4 KB · Views: 154

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:46
Joined
Feb 19, 2002
Messages
42,970
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.
 

PuzzledNH

Registered User.
Local time
Today, 08:46
Joined
Nov 4, 2019
Messages
36
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.
 

missinglinq

AWF VIP
Local time
Today, 08:46
Joined
Jun 20, 2003
Messages
6,423
...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)>
 

isladogs

MVP / VIP
Local time
Today, 12:46
Joined
Jan 14, 2017
Messages
18,186
If Product Line has a space then it must be enclosed in []
Code:
Private Sub Product Line_AfterUpdate()
   Me.[Product Line].DefaultValue = "[COLOR="Red"][B]'[/B][/COLOR]" & Me.[Product Line] & "[B][COLOR="red"]'[/COLOR][/B]"
End Sub

NOTE:
.Value can be omitted as its the default property
I've used single quotes (in RED) instead of double double quotes
 

PuzzledNH

Registered User.
Local time
Today, 08:46
Joined
Nov 4, 2019
Messages
36
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.
 

isladogs

MVP / VIP
Local time
Today, 12:46
Joined
Jan 14, 2017
Messages
18,186
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
 

PuzzledNH

Registered User.
Local time
Today, 08:46
Joined
Nov 4, 2019
Messages
36
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

Hi isladogs,
Thank you for responding.
When I open the code builder I see the following:

Option Compare Database
Private Sub Product_Line_BeforeUpdate(Cancel As Integer)
End Sub
(There is a straight line below Option Compare Database. Not sure if this indicates a module title.)

Am I deleting this code and adding the following in it's place?

Private Sub Product Line_AfterUpdate()
Me.[Product Line].DefaultValue = "'" & Me.[Product Line] & "'"
End Sub

I appreciate the help.
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:46
Joined
Jan 23, 2006
Messages
15,361
Put this line immediately below your
Option Compare Database
Code:
[COLOR="Blue"]Option Explicit[/COLOR]
 

isladogs

MVP / VIP
Local time
Today, 12:46
Joined
Jan 14, 2017
Messages
18,186
You selected the before update event by mistake.
Scrap that code and use the after update event code instead.
And follow the guidance draw provided
 

PuzzledNH

Registered User.
Local time
Today, 08:46
Joined
Nov 4, 2019
Messages
36
Put this line immediately below your
Option Compare Database
Code:
[COLOR="Blue"]Option Explicit[/COLOR]

So the new code would look like the following?

Option Compare Database
Option Explicit
Private Sub Product Line_AfterUpdate()
Me.[Product Line].DefaultValue = "'" & Me.[Product Line] & "'"
End Sub
Private Sub Product_Line_BeforeUpdate(Cancel As Integer)
End Sub

I understand Private sub (specific to this module only) and [Product Line] (field in question). Other than than, I am not familiar with this code. Can you give me an idea on what this code is actually doing?

Thank you for the help.
 

isladogs

MVP / VIP
Local time
Today, 12:46
Joined
Jan 14, 2017
Messages
18,186
No. I said to scrap the before update event entered by mistake

Code:
Option Compare Database
Option Explicit

Private Sub Product Line_AfterUpdate()
Me.[Product Line].DefaultValue = "'" & Me.[Product Line] & "'"
End Sub

When you enter a value in the Product Line field the after update event is triggered
It then sets the default value of that field to the current value
When you enter a new record, that value is pre-entered for you but can be overwritten

Me. syntax is shorthand for a control on this form
Option Explicit ensures any missing variable definitions are detected by Access

The Option Compare statement specifies the string comparison method (Binary, Text, or Database) for a module. If a module doesn't include an Option Compare statement, the default text comparison method is Binary.
 
Last edited:

Users who are viewing this thread

Top Bottom