Referring to the contents of a variable 1

[markcrobinson]

What am I doing wrong?

Set rs1 = db.openrecordset("Select [Field 1], [Field 2], [Field 3] from data1;")

mcount=1
do while mcount < 4
mfield="[Field " & Ltrim(string(mcount))&"]"
? rs1.mfield
' I want the contents of mfield to be used so
' ? rs1.[Field 1] is output
mcount=mcount+1
loop

 

[TheAceMan1]

How are ya markcrobinson . . .

Post the rest of the code!

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[markcrobinson]

' google import csv file has a flat file, one record per order with up to 20 items
' for Quackbooks import, I need to convert it to one line per item ordered.
' first error is If rs1.mfield > "" Then
' because rs1.mfield should be evaluated as rs1.[Item 20 ID]


Set db = CurrentDb
Set rs1 = db.openrecordset("Select * from Google;")
Set rs2 = db.openrecordset("Select * from GoogleTwo;")


' process entire google import
rs1.MoveFirst
Do While Not rs1.EOF
'Each record can have up to 20 items. Make a new record in GoogleTwo for each item.
'Make the first record
rs2.AddNew
mcount = 0
Do While mcount < 26
rs2.Fields(mcount) = rs1.Fields(mcount)
mcount = mcount + 1
Loop

'move item fields if not empty
mcount = 20
Do While mcount > 0
mfield = "[Item " & LTrim(Str(mcount)) & " Quantity]"
If rs1.mfield > "" Then
mfield1 = "[Item " & LTrim(Str(mcount)) & "ID]"
mfield2 = "[Item " & LTrim(Str(mcount)) & "Name]"
mfield3 = "[Item " & LTrim(Str(mcount)) & "Description]"
mfield4 = "[Item " & LTrim(Str(mcount)) & "Price]"
mfield5 = "[Item " & LTrim(Str(mcount)) & "Quantity]"
rs2.[Item 1 ID] = rs2.mfield1
rs2.[Item 1 Name] = rs2.mfield2
rs2.[Item 1 Description] = rs2.mfield3
rs2.[Item 1 Price] = rs2.mfield4
rs2.[Item 1 Quantity] = rs2.mfield5
End If
mcount = mcount - 1
Loop
rs2.Update
' get next record in Google
rs1.MoveNext
Loop

 

[PHV]

Replace this:
rs2.mfield1
with this:
rs2(mfield1)

and so on

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[markcrobinson]

OK! OK!
Look, I am an RTFM kinda guy. Now teach me to fish.
Although I doubt anyone would believe it after seeing this dumb question I am an experienced programmer. I just forgot this...

Where could I have found this myself?

The new 2010 Access help files seem to return "topic not found" when asked for anything relevant. I searched there, this forum, and google for things like

reference the contents of a variable
string conversion

Where does this information live?

Anyway, I really appreciate your help!

 

[PHV]

In fact the real answer is this:
rs2.Fields(mfield1)

I've played with the fact that the Fields collection is the default one for a DAO.Recordset object.

Furthermore, you've used this syntax with a variable:
rs2.mfield1
say you replace the variable with a string literal:
rs2."[Item 20ID]"
Do you think it should compile properly ?
Hint: note the double quotes ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

I am an RTFM kinda guy. Now teach me to fish.

This is a little confusing because vb/vba is very sloppy. But here it goes

1) It is very sloppy because there is actually two seperate types of notation. Bang and Dot. I will talk Dot first
2) Before talking this you have to understand collections. A collection is an object that contains other objects. To refer to an object in a collection you use either the named index or that item or its numerical index

collectionName.item("namedIndex").value
collectionName.item(1).value

In this notation you can use a variable
x = "namedIndex"
then
collectionName.item(x).value
or
y = 1
then
collectionName.item.value

example
numbered index: rs2.fields.item(1).value
named index: rs2.fields.item("namedIndex").value
variable: rs2.fields.item(x).value

3)Now this is what gets lost in the sloppiness. All objects have default properties and/or methods. VB lets you choose to write the default or omit it.

For most objects the default property is "value". So you can simply write:
numbered index: rs2.fields.item(1)
named index: rs2.fields.item("namedIndex")
variable: rs2.fields.item(x)

Now for a collection the default property is "item" and hardly anyone writes it.
So this is fine:
collectionName("namedIndex")
rs2.fields("NamedIndex)

But guess what the default property of a recordset is? Yep, the "Fields" collection. So that can get dropped
rs2("namedIndex")
rs2(1)
rs2(somevariable)

4)That is why the following is incorrect
collectionName.("namedIndex")
the dot does not seperate a collection from the index

5) Now for Bang. This only works with named indices of collections and does not allow for variables.
CollectionName!NamedIndex
rs2.Fields!YourFieldName
But as said, "Fields" is the default and does not need to get written
rs2!YourFieldName
This is a little confusing because in this case you drop the collection name, but still keep the !

Fish on.

 

[markcrobinson]

Thanks!
I love Access VB, but not as much as BASIC

 

[PHV]

MajP,
Are you sure that the DAO.Recordset.Fields collection exhibit an Item property ?

 

[MajP]

Yes, of course. Why would it not?