Macro

[Aless]

I am sure this is something very simple to do, but I am new to this and
would apprecite your help.

I want to write a macro which would select the record displayed in a form,
copy it and append it to the related table.

dg

 

[Aless]

Yes, I want to make an exact copy of the record in the same table (for
editing) but would like to do it via a macro.
The record is already being displayed in a form so this does not have to be
part of the macro.

dg

 

[Drew Wutka]

I am assuming this is because you are going to enter 'similar' data in a new
record, and you want the current data 'copied' over to the new record. May I
recommend a slightly different approach? Instead of 'adding' a new record,
why not set the defaults of the control to the 'current' values, then go to
a new record.

For example, put this code behind the OnClick event of the button you are
setting up for this:

On Error Resume Next
Dim ctrl As Control
For Each ctrl In Me.Controls
ctrl.DefaultValue = """" & ctrl.Value & """"
Next
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec

The only drawback to the code above, is that the default values are now set
to that particular records value. So every new record will have those
defaults. So, put another button for a 'blank' new record, and use this
code:

On Error Resume Next
Dim ctrl As Control
For Each ctrl In Me.Controls
ctrl.DefaultValue = ""
Next
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec

Now, if you are already using default values, and want to 'preserve' those,
for a 'blank' record, you can use the same method, just either hardcode the
original back into the normal new record button, or build a collection of
the original values (for the 'carbon copy' process , and drop them back in
for the blank process.

One last note. The advantage of setting the values as defaults, instead of
creating them as a new record, is that if the user backs out, nothing was
written to the database.

Drew

 

[Ken Snell]

Assuming that you always use the same append query, create an append query
that is based on the form's recordsource and that has a criterion on the
primary key of the record simliar to this:

[Forms]![FormName]![PrimaryKeyName]

Have the desired table be the target of the append query.

Then have a command button on the form whose OnClick event runs the macro.
The macro should have an OpenQuery action that runs the append query.

Should do what you want.

 

[Aless]

Thank you for the information.

I have just found an much easier way of doing this - simply let the Command
Button Wizard do it for you! Select Record Operations under Categories and
Duplicate Record under Action to perform and it done.

dg

Assuming that you always use the same append query, create an append query
that is based on the form's recordsource and that has a criterion on the
primary key of the record simliar to this:

[Forms]![FormName]![PrimaryKeyName]

Have the desired table be the target of the append query.

Then have a command button on the form whose OnClick event runs the macro.
The macro should have an OpenQuery action that runs the append query.

Should do what you want.

--
Ken Snell
<MS ACCESS MVP>

I am sure this is something very simple to do, but I am new to this and
would apprecite your help.

I want to write a macro which would select the record displayed in a form,
copy it and append it to the related table.

dg

 

[Aless]

I appreciate the time you have taken to help me out.

I have just found an much easier way of doing this - simply let the Command
Button Wizard do it for you! Select Record Operations under Categories and
Duplicate Record under Action to perform and it done.

dg

 

[Dirk Goldgar]

I am sure this is something very simple to do, but I am new to this
and would apprecite your help.

I want to write a macro which would select the record displayed in a
form, copy it and append it to the related table.

dg

I would use VBA for this, as it's more flexible and powerful. However,
a macro to do it might be like this:

RunCommand SelectRecord
RunCommand Copy
RunCommand PasteAppend