Copy record information to another record

D

digger27

Have previously posted with no reply. Really need some help.

I have form that pulls from a query that touches several tables.
This form is for entering different attributes of jobs that my company is
currently working on. Many times several related jobs have data that is
identical. I would like to create 2 buttons that copy and paste info from
one job to another. I have tried the code that is displayed elsewhere in the
forums, but they all create a new record. The records in my form are created
by another user earlier in the process by just entering base data (job code,
customer name, address, etc.). Once all of the preferences for the job come
to us we enter the data for each job into the form. The form has quite a few
fields and we don't want to waste time entering them one at a time. Just
want a way to copy/paste from one record to another.

Any help here would be greatly appreciated.
 
O

Ofer

you can try that, few stages
1. create varible in the form declaration
dim MyField1 as string,MyField2 as number, MyField3 as string .... (as many
that you need)
2. on the copy button assign the values from the fields in the form to the
variable you created

MyField1=me.Field1
MyField2=me.Field2
MyField3=me.Field3

you can copy only the records that are relevant

3. on the paste button enter the code.

DoCmd.GoToRecord , , acNewRec
me.Field1=MyField1
me.Field2=MyField2
me.Field3=MyField3

that way you can repeat the paste as many time that you would like with the
same values.
 
D

digger27

Ofer,
I tried your suggestion. However, this code creates a new record to copy
the data to, which is exactly what I don't want. Just to make sure I didn't
mess up, please see my comments on each step below:


Ofer said:
you can try that, few stages
1. create varible in the form declaration
dim MyField1 as string,MyField2 as number, MyField3 as string .... (as many
that you need)
I did this for 4 fields to start with. I did this by opening the form in
Design View, opened the property window, selected Form, and then went to On
Current on the event tab. Should this have been done on another event?
The code looked like this:
Private Sub Form_Current()
Dim MyField1 As String
Dim MyField2 As String
Dim MyField3 As String
Dim MyField4 As String
End Sub
2. on the copy button assign the values from the fields in the form to the
variable you created

MyField1=me.Field1
MyField2=me.Field2
MyField3=me.Field3

you can copy only the records that are relevant
For this part I created a button, and created code under the On Click event.
The code looks like this:
Private Sub btnCopy_Click()
MyField1 = Me.txtFullBathDownstairs
MyField2 = Me.txtHalfBathDownstairs
MyField3 = Me.txtFullBathUpstairs
MyField4 = Me.txtHalfBathUpstairs
End Sub
3. on the paste button enter the code.

DoCmd.GoToRecord , , acNewRec
me.Field1=MyField1
me.Field2=MyField2
me.Field3=MyField3

that way you can repeat the paste as many time that you would like with the
same values.
I did the same here as for the Copy button. This is where it looks like I
am telling it to create a new record. I changed this to acNext, but that
only moved me to the next record, but did not paste. The code looks like
this:
Private Sub btnPaste_Click()
DoCmd.GoToRecord , , acNext
Me.txtFullBathDownstairs = MyField1
Me.txtHalfBathDownstairs = MyField2
Me.txtFullBathUpstairs = MyField3
Me.txtHalfBathUpstairs = MyField4
End Sub

Hopefully I just missed something small.
Thanks for the first reply and let me know if you know where the problem is.
Thanks again.
 
O

Ofer

The declaration of the variant should be in the form declaration just under
the line "Option Compare Database"

the rest look fine
now if you dont want a new record to be added only to update the current
record then remove the line
"DoCmd.GoToRecord , , acNext "
so next time when you press the paste button it will update the fields you
are currently at
 
D

digger27

Ofer,
Your last suggestion worked like a charm. You're a life saver. I just have
one last problem. How do I deal with the Null values? Not all of my fields
(textboxes, combo boxes, checkboxes, etc.) will always have a value. I am
guaranteed to have some blanks just due to the design of the form and the
nature of the projects. If I press the copy button and the record has blanks
I get an error saying :
Run-time error '94':
Invalid use of Null

This should be the last problem I have to over come.

Thanks again for all your help.
 
O

Ofer

You can use the NZ function to replace the null with another value
e.g
MyField3 = NZ(Me.txtFullBathUpstairs,"")
will replace the null value with empty.
or for number
MyField3 = NZ(Me.txtFullBathUpstairs,0)
 
D

digger27

Ofer,
This is perfect. My boss will be so happy that this is fixed.
This form has about 200 fields, half of which are almost always filled in.
This will save hours of work.

Thanks again.
 
O

Ofer

any time

digger27 said:
Ofer,
This is perfect. My boss will be so happy that this is fixed.
This form has about 200 fields, half of which are almost always filled in.
This will save hours of work.

Thanks again.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top