Copy record to new record

[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 ;-(

 

[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...

 

[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.

 

[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)

 

[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

 

[PlymouthSid]

"Thank You" to all that have suggested lines to take . . . . . I have had to leave this particular problem for a short while as I have been asked to "sort ouT" a "Book of Remembrance" for church. (In Access/Db terms it is just a little bit more than a basic address book ~ one form, Two Reoprts ~ An Index and The "Book" itself) . . . . Cleaning the data is the time consuming bit of course).

Thank you again,
Sid