Find Recordset field number using field value

[AJ]

I am very new to access and have a question regarding a recordset. I would
like to access a field in a recordset based on an index but I am not sure of
my starting index number. If I have the value of a field can I figure out
what the recordset field index number is?
For example lets say I haev a table with all the months as column headers:
Jan, FEb, Mar, Aprl........
If I have the value "MAR" can I find out that this would be field(3) of the
recordset??
I have a table with sales figure broken down my month. I have to step
forward and backward through the columns and I think it would be easy to use
an index for this but I need to figure out the starting field number (index).

PLEASE HELP!!!
AJ

 

[Douglas J. Steele]

Realistically, it would be a mistake to have fields named Jan, Feb, Mar,
etc. Fields like that are known as repeating groups, and are a violation of
database normalization. Jeff Conrad lists some good resources to learn more
about normalization at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

That having been said, you could determine which field in recordset rstCurr
is named Mar using:

Dim bytLoop As Byte

For bytLoop = 0 To (rstCurr.Fields.Count - 1)
If rstCurr.Fields(bytLoop).Name = "Mar" Then
Msgbox "Mar is field number " & bytLoop
Exit For
End If
Next bytLoop

 

[AJ]

Thanks Douglas. I do not have a table with months as column names, I was jsut
trying to explain the situation.
Anyway, your example is exactly what I need although, in your example you
have rst (I assume that is the name of your recordset), I get an "no object"
error.
Did I do something silly when I added it to my code?

 

[Beetle]

The rst in Doug's reply is just an example of a recordset variable. You will
need to declare and set your own recordset variable to use in the code. You
could name the variable anything you want, but in keeping with naming
conventions, recordset variables usually start with rst or something similar.
Below is an example of declaring and setting a recordset variable;

Dim rstCurr As DAO.Recordset

Set rstCurr = Me.RecordsetClone

This would create a copy, or clone, of the recordset you are currently
working in, which is then used in your code.

HTH