Copy record to new record

[PlymouthSid]

When you have a set of data where addresses are frequently duplicated as with families and contacts at companies, the best solution is a separate Address table. That allows you to change the Address for a group of people at the same time. With your method, you need to change every family member's address.

Thank you for that gentlest reminder of RDB basics ;-)

In my defence I did say "I suppose I could have used a variation on the “Clients / Orders” design."

I was so pleased with getting all the other routine inputs based upon related tables (and combo boxes) that I quite failed to realise that the same logic applied to addresses even though most cases were be one-to-one not one-to-many! In truth, there are less than 200 members but the majority are "Couples") so I did miss that trick . . . . . doing things the right way always pays dividends.

It will be a simple enough task to correct that error and even better, it will keep me occupied whilst giving me a reason not to do gardening!

Somehow an advertising block has appeared in this entry!

 

[PlymouthSid]

Blast.! The solution I thought I had found now, NO LONGER works (Sorry to all concerned). Both Macro and Code versions produce an unwanted "blank" record.

I will take Pat's advice and restructure my tables because that will address (!) the actual issue I am trying to correct.

I do not have the wit to work out why the "solution" produces the "extra" record ;-(

 

[Pat Hartman]

doing things the right way always pays dividends.

One important thing to keep in mind when you switch to the Address table method is to have logic that makes the user tell you whether he is changing the address for only THIS person or also for all related people. If Johnny is moving out on his own, you don't want to change everyone else's address also.

 

[Mark_]

Special note on address tables. I've always found it more useful to have a linking or junction table that relates an individual or organization to an address. This allows me to also have a "FromDate" and "ThruDate". This way when James Jones Jr. moves out, you'll track that he USED to live with James Jones Sr. but doesn't any more. Likewise if he moves back you'll track his previous address(es) and current.

Learned to do this when dealing with owners of descramblers for satellite TV. Helped avoid "New" accounts that were really just other people living with someone delinquent on payments...

 

[Pat Hartman]

I've always found it more useful to have a linking or junction table

Makes sense if you need history. Overkill if you don't.

 

[PlymouthSid]

Thank you again.

Can you think of a reason why the "Duplicate Record" evolution generates a new record as well as actually duplicating the required one?
The Unwanted record contains all of the default data just as if a new blank record had been generated.

Makes sense if you need history. Overkill if you don't.

Thank you again

 

[Mark_]

Makes sense if you need history. Overkill if you don't.

Also makes sense when someone move from one known location to another known location. Update link rather than enter entire new address. Example is when a child moves from mom's place to dad's. OP also has "Activities", so would make sense to have the option if their "Activities" are not always at their location.

End goal is to avoid bad addresses being entered.

 

[Mark_]

When you build SqlStr2, your FROM is the same table as you are inserting into. I'd suggest making a query that returns ONLY those fields from tblProducts (qryProducts maybe) and trying with the query as your FROM.

 

[Pat Hartman]

Can you think of a reason why the "Duplicate Record" evolution generates a new record as well as actually duplicating the required one?
The Unwanted record contains all of the default data just as if a new blank record had been generated.

The normal reason this happens is because you are using a bound form and you dirtied the form so that forced Access to save the record you dirtied and the record you inserted. This is really poor design.

The solution is always - VALIDATION. Add your validation code to the form's BeforeUpdate event and never allow an "empty" record or one with missing/invalid fields to be saved - PERIOD. You can give the user an error message or simply ignore the error and don't save the record.

 

[PlymouthSid]

The normal reason this happens is because you are using a bound form and you dirtied the form so that forced Access to save the record you dirtied and the record you inserted. This is really poor design.

The solution is always - VALIDATION. Add your validation code to the form's BeforeUpdate event and never allow an "empty" record or one with missing/invalid fields to be saved - PERIOD. You can give the user an error message or simply ignore the error and don't save the record.

Thank you Pat.
I will give that some thought.

Right now, I cannot understand how copying a VALID record can cause its "pasting" to produce a "dirty" anything ~ If I manually carry out the steps whilst working directly on the table (Shrieks of Horror!) the steps work OK . . . . . .

• Select Record (that needs to be copied)
• Right click ~ Copy Record
• Select New Record (On the table, the New Record has all the appropriate defaults set)
• Right click ~ Paste

No new (blank) record is generated I just have two identical records (differing by their(AutoNumber) ID

(BTW, I am working on creating the "address" table as suggested)

Sid
(00:30 GMT! so off to bed now)

 

[Pat Hartman]

You have code behind the form that is causing the problem. Use the debugger to step through the code so you can see where it is happening.

 

[Mike Krailo]

I think that this works a little better (it avoids the generation of "blank" records) by selecting where the "copied" record is pasted (Not "PasteAppended"!):


DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste

I found it after some serious browsing but actually it follows the shape of what happens if you use the Command Button Wizard! which generates a Macro which has the above steps!

Once again thank you ~ Your response saved me hours.
Sid

Why did I need to do this?​

The requirement was generated by the need (in a “club membership” database) not to have to retype the details of the address for each individual in a family at the same address. The fields that change are very easily edited on the simple form by the use of various drop down boxes (qualification, membership Status, male/female etc.

Quite often the “Forename” is all that needs to change. Without the ability to use a copy of another record, the data input load is onerous ~ the address would have to be typed each time and setting up a separate table for each address for use in a one-to-many relationship seems to be quite an overkill). I suppose I could have used a variation on the “Clients / Orders” design.

Going back to the simple solution. Why not simply do what LinQ suggested originally, that is super simple. You can add a few extra lines of code to blank out the fields you normally edit and set focus to the first one to edit.

Private Sub CopyRecordBtn_Click()
   DoCmd.RunCommand acCmdSelectRecord
   DoCmd.RunCommand acCmdCopy
   DoCmd.RunCommand acCmdPasteAppend
   Me.DDescription = vbNullString
   Me.DDescription.SetFocus
End Sub