Foxpro SCATTER/GATHER equivalent

[DavidAtCaspian]

Does anyone know if there is a VBA method, or a third party tool that give
VBA (with ACCESS) an equivalent to Foxpro's SCATTER/ GATHER commands.

SCATTER will take the current record, and generate and populate variables
for every field in the records, by adding "m." in front.
So if you have fields A, B and C with values 1,2, and 3.
Scatter gives you the equivalent of
Dim m.a,m.b, m.c
m.a = 1
m.b = 2
m.c = 3.
GATHER writes them all back again to the current record.

I have found this very useful and timesaving, but cannot see how to generate
a variable, whose name is soft coded.
Thanks

 

[Douglas J. Steele]

I can't think of anything equivalent in Access

 

[Brendan Reynolds]

You could do it with an array or a collection. I can't say I've ever need to
do this, though. As you'll see in the example, I get the data into the
collection by referring to the form's RecordsetClone property. But in most
circumstances, I would just refer to the RecordsetClone property directly,
without need of the collection.

Private Sub Command34_Click()

Dim col As Collection
Dim lngFields As Long
Dim rst As DAO.Recordset
Dim fld As DAO.Field

Set col = New Collection
Set rst = Me.RecordsetClone
For Each fld In rst.Fields
col.Add fld.Value, fld.Name
Next fld

For lngFields = 1 To col.Count
Debug.Print col(lngFields)
Next lngFields

End Sub

 

[DavidAtCaspian]

Thanks for this Brendan:
In conjunction with an answer I posted myself earlier, I think I have an
idea on how to do this now.

So although I still don't have a soft coded variable name, I do now see how
I can have a softcoded method of saving all the individual field values in
'this record', of changing only the ones I want, (which could be several
passes through some complex validation processes) and then when finished of
writing the entire new record back again with a single update command. A bit
like this:

rs.edit
call gather_ sub_ routine (rs)
rs.update

Thanks.

 

[DavidAtCaspian]

Brendan, I found that a scripting dictionary was exectly the right object for
this.

The scatter code is i this:

Sub Scatter(rs As DAO.Recordset)

Dim fld As DAO.Field
Set ScatterDict = New Scripting.Dictionary

Dim r As Integer
For Each fld In rs.Fields
ScatterDict.Add fld.Name, fld.Value
Next fld

End Sub

and the gather even simpler

Sub Gather(rs As DAO.Recordset)
Dim fld As DAO.Field
rs.Edit
For Each fld In rs.Fields
rs.Fields(fld.Name) = ScatterDict(fld.Name) '
Next fld
rs.Update

End Sub

Of course, now I've done that, it turns out to be not what I wanted in this
instance.
Thanks for the tip.

David

 

[Michel Walsh]

Hi,


You can use a recordset and its fields? There are 2 syntaxes: rst!FieldName
and rst.Fields("FieldName"). The second syntax allows variable field name to
be reached, through a string of their name.

I don't think there is a way to dynamically create a variable on the stack,
in VBA, like the DIM statement in your example does, so your variables would
have to exist on the heap, maybe regrouped in a collection, but that is
already work done through the Fields collection, so back to why not using a
recordset and its Fields.


Hoping it may help,
Vanderghast, Access MVP

 

[Albert D.Kallal]

Why bother?

The form has a reordset, and this is a pre-made collection of fields that
you can use as if they were variables anyway.....

You can always go

me!SomeFieldName....

or, stuff a value into this variable

me!SomeFieldNAme = "hello"

To write out the record set to disk, simple go

me.Refresh


If you want to dump the write to disk, simply go

me.Undo

I don't at this point in time see ANY advantage to trying to pull the data
to a set of memory variables, when you have a ready made built collection
object with all of the fields.

Further, you can reference any value by a string if you want.

strWhatField = "LastNAme"

msgbox "the value of last name is " + me(strWhatField)

So, we don't have macro substitution like the old dbase language, but you
can use a string to reference virtually any object, be that a form, or
fields for that form at runtime through the use of a string....

What is the big deal being

m_myfieldname

or

me!MyFieldName

What advantage do you gain by using scatter/gather? Just use me.undo to dump
the disk write...or me.Refresh to write out the data to disk.

I don't see ANY advantage here to trying to build a set of memory variables
when a form already has such a collection of values available?

 

[DavidAtCaspian]

Albert:
Many thanks for the input. If I was working with a form, yes it would be
much easier, but I'm not.
I am working with two similarly structrured recordsets (DAO or ADO). I look
at a record in Recordset 1, perform several validations/changes to it and
then write all the new values to Recordset2. There are approximately 30
fields, and I wanted a method that didn't involve pages of individual field
assignments, and edits/updates all over the place. Recordset2 has additional
fields over and above those in recordset1.
The Dictionary is doing the job just fine, and fast.