duplicate record button, excluding 1 field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a data entry form that I would like to add a "Copy Record" button
that uses the same data as the last record excluding one field "LoadID" since
copying that value will give me a duplicate value error for that record.
Is there any code that will allow me to do so?

Thank you,
Jae
 
It depends on whether you want to OPEN the new record after you create it. To
simply create it, just make an append query where each Field entry is like
this:

[Forms]![thisForm]![controlName]

If LoadID is an autonumber field, just omit it, and it will be populated
automatically. Run this query from the Click event of your button:
DoCmd.OpenQuery "myQueryName"

The above copies the record but leaves you on the old one. To go to the new
one, try this instead:

Create a set of variables in the header of the form's module - one for each
field on the form. When you click the button, do this:

ctrl1 = control1
ctrl2 = control2
'etc. for each control, where ctrl1 & 2 are variables & control1/control2
are control names
DoCmd.GoToRecord acForm, Me.Name, acNewRec 'goes to new record
control1 = ctrl1
control2 = ctrl2
etc.

This copies the current fields to the variables, goes to a new record, and
populates the fields with the copied values. This leaves you on a new record
in edit mode (the record is not yet saved.)
 
Oops. Let me amend the first part of my first response. I wrote it up for
unbound controls, and yours are probably bound. Assuming the controlsare all
bound, the append query needs to append all fields EXCEPT LoadID to the table
but with a criteria for LoadID of:

=[Forms]![myForm]![LoadID]
 
Create a set of variables in the header of the form's module - one for each
field on the form. When you click the button, do this:
Click to expand...
I assume you mean in VBA, under the "Option Compare Database" line of my
form's code.
If so, it won't compile.
I hope I'm wrong.

Thanks,
Jae
 
Yes, that is correct, but only the variable declarations go there. The rest
of my example was the code for Button1_Click. The declarations will look like
this:


Private ctrl1 as String 'string example
Private ctrl2 as Long 'long integer example

The Button_Click code will be the other part:


ctrl1 = control1
ctrl2 = control2
'etc. for each control, where ctrl1 & 2 are the previously-declared
variables & control1/control2 are control (text/combo boxes, etc) names
DoCmd.GoToRecord acForm, Me.Name, acNewRec 'goes to new record
control1 = ctrl1
control2 = ctrl2
etc.

It should compile now with the correct syntax for the variable declarations.
Sorry about the confusion on what goes where.
 
Thanks for clarifying everything for me.
The code looks like this now:
Option Compare Database
Private ctrl1 As String 'string example
Private ctrl2 As String 'string example

Private Sub nextload_Click()

ctrl1 = LoadID
ctrl2 = VesselVoyage
DoCmd.GoToRecord acForm, Me.Name, acNewRec 'goes to new record
LoadID = ctrl1
VesselVoyage = ctrl2

End Sub

When I click the button, it says "The object 'LoadDetails'(form which button
is on) isn't open.

Sorry for making this harder then it should be...
 
No problem. There are always little obstacles to overcome. What version of
Access are you using?

In the meantime, try switching the Me.Name to the name of your form, in
quotes: "LoadDetails" (the full Sub is below). Just make sure it is the form
name and not the table name, if they are different.

Private Sub nextload_Click()

ctrl1 = LoadID
ctrl2 = VesselVoyage
DoCmd.GoToRecord acForm, "LoadDetails", acNewRec 'goes to new record
LoadID = ctrl1
VesselVoyage = ctrl2

End Sub

Also, is LoadID the primary key for the table, and is it an autonumber
field? If so, leave it out of the statements above, because it will not be
the same for the new record as for the old record, and the system will create
it automatically.
 
Hi,
I used this as my DoCmd. line:
DoCmd.GoToRecord , , acNewRec

It works beautifully, I even have it autonumbering the LoadID using ctrl1 + 1.

Thank you so much Brian!
 
Hi

I have read through this discussion but am still struggling. I want to do
exactly the same thing as I have a “Record†field as the autonumber (not
LoadID). I have started a new query and for each record in the field row I
have typed [Forms]![Main input]![controlName]. The “Main input†is the name
of the form I have created the copy record button forâ€. And controlName is
the name of the field – Is this correct???

I have tried to run this on the “Main input†form but it displays a table
with the information on the form. Please could you help and run me through
step by step the stages involved – in basic language, as I am still new to
Access. Help would really be appreciated!

--
Thank you for your help


Brian said:
Oops. Let me amend the first part of my first response. I wrote it up for
unbound controls, and yours are probably bound. Assuming the controlsare all
bound, the append query needs to append all fields EXCEPT LoadID to the table
but with a criteria for LoadID of:

=[Forms]![myForm]![LoadID]

Jae Hood said:
Hi,
I have a data entry form that I would like to add a "Copy Record" button
that uses the same data as the last record excluding one field "LoadID" since
copying that value will give me a duplicate value error for that record.
Is there any code that will allow me to do so?

Thank you,
Jae
Click to expand...
Click to expand...
 
Back
Top