Writing a macro to duplicate some records.

[GregoryWest]

I have a table it contains a list of opening and closing values along with a date and a couple control fields. What I would like to do is duplicate a few fields from records that meet a criteria onto the same table. After that is done there will be a few updates on the new records. Looking at it, it would be:
'duplicate' where field2 <> 0 and field5 = No and field6 = Yes. All other fields on the newly created records would be their default values.

If this even possible?

 

[Steve R.]

One method, use global variables. When on the record you wish to duplicate, assign the field values to a global variables. When creating the new record, assign the value of the global variable to the (empty) field that you want. Both operations can be performed by pressing a command button. Recommend using a DAO recordset.

 

[GregoryWest]

Have written a few VBA proceedures. Still very green tho, most did programming in Fortran, Visual Foxpro. Not overly familular with manipulating Access tables in VBA.

Is there a chunk of code I could work off of to get syntax correct.

Thank in advance.

 

[Steve R.]

It will have to be a learning experience for you. I took a look at what I had and it is quite complicated since I use unbound forms for data entry and a lot of data verification is also undertaken.

If the form that you wish to duplicate is open you can assign a global variable this way through a command button that stores the value, closes the form and opens your new form.

gblAddress =Me.AddressLocation01

The "new" form, on the openform event, will then load the data.

Me.AddressLocation01=gblAddress

--------------------------------------------------------------------

Another approach, open the "new" form through a command button on the "old" form and leave the "old" form loaded. On the openform event of the "new" form read the data from the "old" form.

Me.AddressLocation01=Forms("OldFormName").Controls("AddressLocation01")

 

[GregoryWest]

Ah, I see where you are going. Think what I am doing is easier (I hope) there are no forms in play. Just one table. What exactly is going on is: There is a table of Opening/Closing ballances. There are two logical fields ACTIVE and EXPORTED. When a record is created ACTIVE is True and Exported is False. I flip all the ACTIVE to False then start processing. Next I need to 'duplicate' or 'create' records. For every False Active/Exported I want to create a new record with the Open amount being the same as the Closed amount of the current record. A few other fields have to be filled in, but already have that working. The Closed amount should be zero on the new record, and Active=True, and Exported=False. More processing happens to get the records in the False/False status exported and then set to False/True.

This sort of make scense.

 

[Steve R.]

One table makes things easier. Create a DAO recordset. I have liked this website: Comparison of DAO and ADO Recordset Syntax. Create a DAO recordset. Use FindFirst to locate your record and store the field values you want to copy into their respective global variables.

After that use FindFirst again to locate the record you want to copy the data into with DAO's update feature. Editing a Recordset (DAO). Use the global variable to enter your field values.

After posting, I realized that the website I referenced above has a better example of opening a DAO recordset than Allen Browne's version. At least I think so. Opening in DAO (Workstation)

[URL="http://www.classanytime.com/mis333k/sjdaoadorecordsets.html#openDAO"]Set rs = CurrentDb.OpenRecordset("SELECT pkPeopleID, LastName FROM tblPeople", dbOpenDynaset)[/URL]

I left the reference to Allene Browne's exanple below in case you want to research it.

-----------------------------------------------------------------------------
Also Allen Browne has a very useful website, which has DAO examples. DAO Programming Code Examples. Looks like his website is a little techie, but below is one example from his website, which demonstrates creating a simple DAO recordset.

Function DAORecordsetExample()
    'Source: http://allenbrowne.com/func-DAO.html
    'Purpose:   How to open a recordset and loop through the records.
    'Note:      Requires a table named MyTable, with a field named MyField.
    Dim rs As DAO.Recordset
    Dim strSql As String
    
    strSql = "SELECT MyField FROM MyTable;"
    Set rs = DBEngine(0)(0).OpenRecordset(strSql)
    
    Do While Not rs.EOF
        Debug.Print rs!MyField
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
End Function

 

[ch5150]

gblAddress =Me.AddressLocation01

The "new" form, on the openform event, will then load the data.

Me.AddressLocation01=gblAddress

--------------------------------------------------------------------

Another approach, open the "new" form through a command button on the "old" form and leave the "old" form loaded. On the openform event of the "new" form read the data from the "old" form.

Me.AddressLocation01=Forms("OldFormName").Controls("AddressLocation01")

Steve,
This looks like it could work for the problem I'm trying to solve. I'm just not quite sure how to wield this tool. Could you expound a little further?
Respects,
Chris

 

[Steve R.]

This looks like it could work for the problem I'm trying to solve. I'm just not quite sure how to wield this tool. Could you expound a little further?Chris

You need to define a global variable in a VBA module that is not attached to any form. This keeps the variable "alive" so that it can used at any time.

Your form will need to have a "duplicate" command button. When pressed, it will assign the value of certain field to the global variables, close the form, and open a "new" form.

The "new" form has to be designed so that when the "onload event" occurs the global variables will be used to assign values to the respective fields. One of those global variable needs to be a boolean value (which triggers an if statement) which informs the "new" form that duplicate data is being supplied. If not to skip that action.

In a prior post, I referenced simply having both forms open and reading from the form that is being copied. On second thought, that may be a BAD idea. If I recall correctly, it triggers (for bound forms) a "write error" since there would be two open forms attempting to reference the same data.