After Update Copy Record (1 Viewer)

kitty77

Registered User.
Local time
Today, 10:57
Joined
May 27, 2019
Messages
320
I using the following in a command button...

Private Sub Command10_Click()
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend
[Field2] = ""
Me.[Field2].SetFocus
End Sub

It works fine. I would like to make this work on after update in field2. When I do that, it seems to run a loop and add 80 records?

What is going on or I'm I doing wrong?
 

arnelgp

error reading drive A:
Local time
Today, 23:57
Joined
May 7, 2009
Messages
11,127
what are you copying/pasting?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:57
Joined
Feb 28, 2001
Messages
18,708
What bothers me is "why 80 records?" In a loop, a definite number of records occur when you reach some end condition (i.e. a reason to end the loop). There is no "automatic" number of 80 for ending loops. So did the number 80 come as the result of having 80 of some record (so that you are copying 80 records one at a time)? Did you stop it and it just happened to stop at 80? Loops without explicit end criteria don' t stop. So why did this one?

Where this rumination is going is that we don't have full information on this so will have one heck of a time figuring this out from this description.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:57
Joined
Feb 19, 2002
Messages
30,574
Access is a relational database. It is not a spreadsheet. It makes no sense to add a completely duplicated record.

If your app requires copying existing records to make new ones, that is a symptom that the table is not properly normalized. You probably have data in a single table that belongs in two tables.

In the real world, there are situations where you would copy records but in ALL CASES, something in the parent record changes. For example, you might have a situation where you have a standing order. Every Monday, the client wants the same 5 items delivered. To make this work, you create code that copies the standing order every Monday and reissues it for NEXT Monday.
 

kitty77

Registered User.
Local time
Today, 10:57
Joined
May 27, 2019
Messages
320
What I'm trying to accomplish is copy the record I'm on, create a new record, enter in one field (by barcode) a number, repeat. It works with the command button but not with after update. When I put that code in after update, it produces 80 records. I only want one record at a time. I have to close Access in order to get out of the loop or the 80. Clearly Access does not like it.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:57
Joined
Feb 19, 2002
Messages
30,574
The common data in the record probably belongs in a different table. That way, you would enter it once and then create as many child records as you need, one at a time. When the parent data changes, you add a new parent record and then add the child records using the bar code reader. When the relationships are properly defined and the master/child links are defined for the form/subform, Access will automatically add the proper foreign key when a new child record is added.

I would not use the AfterUpdate event of the control or the record. This will ALWAYS result in one, last "empty" record being added. What happens to that record?
 

Users who are viewing this thread

Top Bottom