How do I copy a record via a macro?


Frank Martin

We have a double-entry general ledger and we need a push-button to copy and
paste the previous record into the relevant second general-ledger account.
For example:

A100: Cash at Bank
H400: Manufacturing supplies:

The first step is to enter the data into the A100 account, and hopefully by
pressing the push-button, via the macro, to copy the same details into the
H400 account. We would then reverse the Db/Cr details.

We have tried the macro method with the following successful start:
OpenForm: (The GL transaction subform)
GotoRecord: (The last record entered, which is the one required.)

But we do not know how to get this last record back into the ledger H400
account; the macro does not seem to do the basic copy/paste operation.
Please help, Frank

Albert D. Kallal

I will say that when you start developing some data processing stuff, you
will not use the interface, or jump the forms around to accomplish your
goals. You have a database engine, and can write code and use some sql to
accomplish you tasks.

You don't see a instant teller machine jump around as it is processing your
transactions. I just want to stress that data processing does not work that
way. Also, having a form jump around like that is going to be very
un-reliable. Further, what happens if two people are using the application?

So, here is what we want to do:

save current data to the database
copy this data to a new record with the correct details.
pressing the push-button, via the macro, to copy the same details into the
H400 account. We would then reverse the Db/Cr details.

Ah, ok, at what point in time are you going to select, or choose the h400

Were you planning to just have the user update the h400 account? I mean, why
not let the user select both accounts and then at the click of a button you
don't even have to edit the 2nd record? Or, after you hit the button, do you
still want the user to view/edit the h400 record? It is not 100% clear what
you want to happen here.

You see, the real trick with this computer stuff is to sit down and figure
out how this should work *before* you start writing some code. By adding the
means to enter the 2nd account, then pressing the one button means we are

The code to just copy the record could be:

dim rstRecs as dao.recordset
dim lngId as long ' id of new reocrd

set rstRecs = currentdb.OpenRecordset("your table")

' new roecrd ' current reocrd fields
rstRecs!Account = "h400"
rstRecs!Pamount = me!Pamount
rstRecs!TransDate = me!TransDate
lngId = rstRecs!ID

The above will add a new record. The fields you want to copy on the right
side are those fields that you need. (hence, you can reverse them).

As mentioned, the above has the account hard coded, and you could provide a
means for the user to enter the new 2nd account on the one form, and then
make this a one button click.

I also grab the id of the new record in the above example. This is in case
you want the form to be moved to this newly added record. On the other hand,
The code to move to the new record could be

me.recordsetclone.FindFirst "id = " & lngId
me.BookMark = me.recordsetclone.BookMark

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