Help w/ Me Function? Or: concatenating a string for a ADO field reference?

  • Thread starter Thread starter BrainlordMesomorph
  • Start date Start date
B

BrainlordMesomorph

With the Me function (I think it's a function) I can build a string
that refers to a field on a form as in:

(Supposing I had 5 fields named Field1 through Field5)

For i = 1 to 5
Me("Field" & i) ="fnord"
Next i

Now I need that exact functionality in an ADO call:

Set RS = CurrentDb.OpenRecordset("myTable")
For i = 1 to 5
RS.("Field" & i) =" fnord"
Next i

I cannot get MSA to understand that I'm talking about a field. The
error is "cannot find field named "RS. Field1"

If I put the text "RS. Field1" in the code there it works fine.
And oddly, if I put a bad fieldname as in "RS. FieldX" The error is
"cannot find field named "FieldX" (without the RS. object)

I've tried dimensioning a variable as a field and setting that to the
string, no good. Same error. Tried using the Me function, that only
seems to work on a form.

It's just got to be a syntax thing. How the hell do I concatenate an
ADO call?
Can anyone point me to an example??

Aha tia blm
 
Try

Set RS = CurrentDb.OpenRecordset("myTable")
For i = 1 to 5
RS.Fields.Item("Field" & i) =" fnord"
Next i

In actual fact, the reason your first example worked is that Me defaults to
the Controls collection. It's more correct to use the following:

For i = 1 to 5
Me.Controls("Field" & i) ="fnord"
Next i
 
BrainlordMesomorph said:
With the Me function (I think it's a function) I can build a string
that refers to a field on a form as in:

(Supposing I had 5 fields named Field1 through Field5)

For i = 1 to 5
Me("Field" & i) ="fnord"
Next i

Now I need that exact functionality in an ADO call:

Set RS = CurrentDb.OpenRecordset("myTable")
For i = 1 to 5
RS.("Field" & i) =" fnord"
Next i

I cannot get MSA to understand that I'm talking about a field. The
error is "cannot find field named "RS. Field1"

If I put the text "RS. Field1" in the code there it works fine.
And oddly, if I put a bad fieldname as in "RS. FieldX" The error is
"cannot find field named "FieldX" (without the RS. object)

I've tried dimensioning a variable as a field and setting that to the
string, no good. Same error. Tried using the Me function, that only
seems to work on a form.

It's just got to be a syntax thing. How the hell do I concatenate an
ADO call?
Click to expand...


I don't think this has much to do with ADO, it's an issue of
understanding how the object/collection/property references
work.

Me is an object reference to the form (technically the Class
module) containing the code that is executing. A form
object's default collection (the one that is used when a
collection is not explicitly specified) is its Controls
collection.

Note that an element in a collection can be selected by
specifying the collection name followed by an exclamation
mark and the name of the element. Alternatively, you can use
either its numerical index or a string containing the
element's name in parenthesis following the collection name.

Further, a control's default property is its Value property.
Thus your reference could be:
Me!Field1
or
Me.Controls("Field1")
or, in your case
Me("Field" & i)
which is just shorthand for the full reference
Me.Controls("Field" & i).Value

A recordset object's default collection is its Fields
collection. You can reference a field in the recordset's
current record the same as any other collection:
RS!Field1
or
RS.Fields("Field1")
or just
RS("Field1")
or in your case
RS("field" & i)
with or without the .Value

Please be aware of the distinction between a control on a
form/report and a field in a table, recordset, a
form's/report's record source table/query. Your use of the
word Field in your question makes it seem like you might not
be keeping the two kinds of objects clear in your mind.
 
Back
Top