Macro

  • Thread starter Thread starter Aless
  • Start date Start date
A

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
 
Not at all sure what you want to do. Do you want to display a record in
a form and then make an exact copy of it in the SAME table(s) where it
already exist, possible to edit it resulting in a new similar, but not equal
record?
 
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
 
You may be able to handle that with a send keys macro, but at the moment
I can's think of a way that would not give you likely problems depending on
the database design etc.

It is possible by using VBA.
 
Oh yea. You are going to want to know how. Well I did not make any
suggestion because I have never had that need, so I have not tried it or
researched it. I am hoping someone who has may read this and offer some
code.
 
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
 
One additional comment. If a large part of a record is going to be
copied into new records in a table, is it not possible that you need to
rethink your table design? Repeated data in a table usually means the table
should be split.
 
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.
 
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


Ken Snell said:
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>

Aless said:
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
Click to expand...
Click to expand...
 
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
 
Aless said:
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
Click to expand...

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
 
Back
Top