How do I copy a record via a macro?

  • Thread starter Thread starter Frank Martin
  • Start date Start date
F

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

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

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
done.

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.AddNew
rstRecs!Account = "h400"
rstRecs!Pamount = me!Pamount
rstRecs!TransDate = me!TransDate
lngId = rstRecs!ID
rstRecs.Update

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.requery
me.recordsetclone.FindFirst "id = " & lngId
me.BookMark = me.recordsetclone.BookMark
 
Back
Top