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.