Copy Some Fields from Current Record only to new record in the same form

[Sandi09]

As the title says I like to know in more detail how to copy certain fields of only a current record to a new record in a form.

I work on MS Access03 and have a database of client information.

Because I come accross many family members, storing one member's personal info and then again at the same time having to enter another member's personal info from the same family, there can be alot of similarities ofcourse with fields like Address, phones number, email etc. which is repetitive and could be avoided but I have very little knowledge of access.

So I dont know what code or event to use in my form to achieve this.

Any assistance please?

Thanks in advance

 

[John Big Booty]

Given that you are dealing with a family who, would presumably live at a common address. I would probably consider storing the address details in a master table and then all the residents in a subtable, something along the lines of;

TBL_Address
AddressID (PK)
AddressL1
AddressL2
Suburb
Zip
State

TBL_Residnets
ResidentID (PK)
AddressID (FK)
GivenName
FamilyName

Now in your form, which will be a main form with Address Details and a SubForm with resident details, in the FamilyName field's After Update event, you could put some code along the lines of;

Me.FamilyName.DefaultValue = "'" & Me.FamilyName & "'"

In your main forms On Current event;

Me!YourSubformName.Form!FamilyName.DefaultValue = ""

 

[Sandi09]

Thanks for reply. I considered using a table but its not just the address that can be common, I would like to copy the address, phone-1 & 2, Email Address, Next of Kin. And that would be a few tables which seems abit to do. And some family members can be just 2 or 3 or more joining at a time so I prefer simply to use the copy form function where once a family member is entered into the database, i can create a new record with the copied fields of the current form. And have the convenience of changing the copied details if needed before saving. A command button is no problem just dont know how to go about it.

Hmmm...

 

[John Big Booty]

One of the by words of good DB design is Normalisation and your current approach would seem to fly in the face of that concept.

Have a look at a tutorial on the subject and perhaps see if you can adapt one of the data models presented here.

In any event the code presented in my previous post should be amenable to your current situation.

 

[Sandi09]

Thanks for replying. I have a fair idea of the importance of normalisation and I had a brief look of the tutorial in the link. I think for businesses in products/manufacturing/frieght etc. normalisation should be applied strongly. But in my case, its a home fitness service business, so I don't store or deal with products only with personal customer information. Customer details slightly differ to business details, as business details like address and phone number dont change very often so its ideal to store in separate tables. So I simply just decided all I needed to do to avoid the headache of entering certain details of a new customer again when I had already an existing customer with the same details was to just do a copy certain fields command. And because they are everyday people, their details may change and when they do I dont mind going and changing them rather than storing in tables fields like address and within a month or two again enter another address when changed, and another step going and changing the PK/FK in both the customer forms etc.
BUT
I have found the solution-AND it works finally YAY!
I am using a code something like this-this is just an example hence used the first name, but really will be using common details like address, phone etc.

Private Sub cmdCopyRecord_Click()
On Error GoTo Err_cmdCopyRecord_Click
Dim v1 As Variant
v1 = Me.First_Name.Value
DoCmd.GoToRecord , , acNewRec
Me.First_Name = v1
Exit_cmdCopyRecord_Click:
Exit Sub
Err_cmdCopyRecord_Click:
MsgBox Err.Description
Resume Exit_cmdCopyRecord_Click
End Sub

Thanks for input

 

[John Big Booty]

It doesn't really matter in what environment the DB is to be used. Normalisation is the bed rock on which you will build a successful application. For simplicity you might choose to take shortcuts with your table structure, to save you time and effort, in the short term. However whose to say that in six months or a year, once you have a substantial amount of data in your DB, you wont discover that you need to change the way you deal with that data. At this point your short cut is liable to jump up and bite you in butt

I know this from bitter experience. A work college asked me to build him a DB to track client shipments. When we sat down to discuss what he wanted, he assured me that it would only ever be used for the one client, despite my repeated questioning on the subject. Four weeks after I'd completed the project he sheepishly approached me and said it was so useful, he want to use it for other clients

Luckily for me this wasn't such a huge thing to implement, as I had had a seeking suspicion that we'd be having that conversation, so had made contingencies in my design.