Copy record in recordset to table (1 Viewer)

[twoplustwo]

Hi guys, hopr you're all well.

Couldn't find a more elegant suggestion that what I'm currently doing.

I open a recordset and evaluate a couple of fields. If they meet the criteria I add the entire record to a new record in the table changing a few of the flags.

Current method is:

!Field1 = !Field1
...
!Field8 = !Field8
!Field9 = sUpdatedFlag

Is there a way to copy the entire record then update !Field9?

Thanks for any suggestions.

 

[Trevor G]

In the forms section when you go into design view there is a command button wizard if the wizard is swtiched on when you create a button form it, you will have an option to copy record, this will give you the facility you want.

 

[twoplustwo]

Hi Trevor, this is all in VBA - the records aren't being changed in a form.

What's the name of this action?

 

[Trevor G]

If this being done in VBA then how are you looking at the record and applying a new record?

Surely it would be easier from a form entry point?

dim db as dao.database
dim rst as dao.recordset
set db = currentdb()
set rst = db.opentable("table name")
with rst
.addnew

end with

Thats the start of this you can look in the help area for examples

 

[twoplustwo]

I am evaluating the fields in the current record.

If they fall into certain parameters I need to add the record "as is" then update the flag in the "Segment" field.

I currently have:

            .AddNew
            !GSP = !GSP
            !ProfileClass = !ProfileClass
            !MPANID = !MPANID
            !Segment = sFlag
            !UMS_Flag = !UMS_Flag
            !MarketType = !MarketType
            !OfferType = !OfferType
            !CustomerType = !CustomerType
            !Sites = !Sites
            !VolumeKWh = !VolumeKWh
            .Update

but it seems like the long way round.

 

[Trevor G]

I would personally look to use a form as the tables shouldn't be touched. you can add addtional commands on the form and apply a simple copy record then adjust the field to be edited to no value.

Look at the following example, it has a form based on your field names with a duplicate button, once clicked it creates a duplicate record and clears the content of the MarketTpye field.