How do I move to the previous field in a recordset?

[gnawoncents]

I am pretty inexperienced with VBA recordsets, and hope there is a simple fix to my problem. I have a recordset (rstQuestions) and am using a For Each...Next operation to go through the fields (fldQuestions) and pull data from relevant ones. I'm running into a problem though when I try to navigate to the previous field (not record).

Here's what I'm trying to do... I need to find the relevant field in a recordset (done; no problems), move to the next field in that same record, retrieve and store the field's value in a string, then return to the previous field for further processing.

I have tried fldQuestions.MovePrevious and fldQuestions.MoveNext, but those seem reserved for records, not fields. (I get an "Error 438: Object doesn't support this property or method.")

I even experimented with "Next fldQuestions" and "Previous fldQuestions," with equally poor results.

Is what I'm attempting possible? If so, what is the correct syntax?

I know I could get the same data with a DLookup function, but since my code runs through every field of every record, I'm pretty sure that would slow down the procedure significantly.

Any ideas? Thank you in advance for your help.

[Mariostg]

YourRecordSet!YourFieldName to select a specific field value at a given recordset row.

Ex:
rs.open sql, cn
rs.MoveFirst 'Moves to first Record
x=rs!YourFieldName 'Assign the value of YourFieldName value to x.

[gnawoncents]

Thanks for the reply.

Since the field name in question changes with each iteration of the procedure, I had previously tried a variation of your suggestion:

strComments = Nz(rstQuestions!(strLOOKUP), "")

...where strLOOKUP is the field name I need.

However, I got the compile error, "Type-declaration character does not match declared data type."

What am I missing here?

[Mariostg]

It's rstQuestions!(strLOOKUP) that creates the error. No brackets...rstQuestions!strLOOKUP

[gnawoncents]

When I remove the parentheses, then it just looks for a field named "strLOOKUP," which doesn't exist. Is there a better way to indicate strLOOKUP is a string than parentheses?

[Mariostg]

I think I understand better what you want to do. This snippet should give you a good clue

    Dim f As String
    f = "YourDesiredFieldName"
    rs.Open sql, cn
    strComments = Nz(rs.Fields(f), "")
 

[gnawoncents]

Thanks! It seems to be working now. I was missing the ".Fields" portion of the code. Sorry for not explaining myself better earlier.

[Mariostg]

No problem. Glad it is working. You might want to mark your post as solved.

Never Mind... you did mark it.