Convert Active Control Name

[Middlemarch]

The datasheet forms me.recordsetclone index number for the selected field is wanted
Does that describe it better?

[CJ_London]

not in the slightest.

Do you mean the field index for a specific field? if so then perhaps something like

dim index as integer

index=0
while mycontrol.controlsource<>me.recordsetclone.fields(index).name
  index=index+1
wend

[Middlemarch]

Brilliant @CJ, that's it Thank you.
If I'd known to call it a field index would that have helped? My description still sounds ok to me!

[orange]

I agree with CJ's "not in the slightest."

Your response in post #16 reminds me of " the rope is 37...???"
Glad you have a solution, and happy that CJ is still the best guesser we have!

[CJ_London]

If I'd known to call it a field index would that have helped? My description still sounds ok to me!

calling things by their proper names makes a big difference. It's not that difficult, just google the terms of reference - such as 'recordsets'. Only you know your terms of reference. And explaining in simple terms what you are trying to do also helps - at least then we kind of know what area you are working in

[Middlemarch]

What language do you guys speak? !!
> The datasheet forms me.recordsetclone index number for the selected field is wanted
The Form uses Datasheet View
Me.RecordSetclone. index number is another way of saying "field index", surely ?
Is wanted needs no explanation.
Anyway solution was great and everyones feedback is appreciated. I had a fix but it was pretty clunky. I'd made a table with the field name and the Control name
and looped using DLookUp to compare them. CJs method is way better. And very helpful to see other ways of doing things.

[Micron]

I'm baffled as to why you didn't weren't at least close in post 5. You asked

So if control is called Test - get x for r(x).Name= "Test"

I gave you
If rs.Fields(i).Name = "Test" Then MsgBox i + 1
If the field name is Test, the msgbox was giving you the 1-based field position in the form recordset. I'd call that the logical/intuitive position.
If you wanted the index, all you had to do was remove the +1?

[Micron]

Me.RecordSetclone. index number is another way of saying "field index", surely ?

No, it is not. You were told that a recordset does not have an index number. Only the fields have index numbers wrt recordsets of any kind.
Consider the recordset as your house, the house has several doors (recordset fields). The garage door index is 3 (the 4th door according to zero based count).
Would you ask what is the index number of your house when given the name "front" for the door?

[Middlemarch]

Apologies if I missed a point you'd made ab out the +1
To me an index is a number that references something (as x in r(x))
If I needed a number for 'front' I'd have to find its index.

[Micron]

If I needed a number for 'front' I'd have to find its index.

Yes if you meant find the index by looking at the field names in a counting loop. But, you cannot return the index for any field in a recordset by passing its name to anything. You have to know the index number to get the name. Both CJ's and my code makes use of a counter loop that tests the name of a field and says, OK since we're at 5 in the counter now, the index must be 5 based on the name we're looking for. Neither code passes the name to anything and returns the index. The magic is all in the counting loop. BTW, if the index value is 5, it is the 6th field in the fields collection of the recordset.

[Middlemarch]

Thanks @Micron. Understood. Here's what I did, but now replaced. And no need for the table.

Function RSNumberFor(controlName) As Integer
    Dim i As Integer
    Dim rs As DAO.Recordset
    RSNumberFor = -1
    Set rs = Me.subCDTracks.Form.RecordsetClone
    If Not (rs.EOF And rs.BOF) Then
         For i = 0 To rs.Fields.Count - 1
            If Nz(DLookup("[Table]", "tblControlNames", "[Form] = " & Chr$(34) & controlName & Chr$(34))) = rs.Fields(i).Name Then
               RSNumberFor = i
               Exit For
            End If
         Next
    End If
    Set rs = Nothing
End Function

[Minty]

I'll still ask my question again, as I must be being thick, I still can't see what the point of this exercise is.

You know the field name, by definition you should know it's data type, so what do you need the recordset field index actually for?
What's the purpose?

[moke123]

Been wondering the same thing as Minty

[Middlemarch]

I didn't answer answer @Minty as I wasn't sure what to say. It seemed obvious, wanting to know about your data and its structure etc.
In this particular case I was building a Msgbox of all 'parts' of my field, instantly showing their names and addressing to help me with coding.

[Micron]

It seemed obvious, wanting to know about your data and its structure etc.

Maybe creating table relationships would take care of that. When you need a reminder, you look at them. Unless I've misunderstood that statement, I don't see how knowing the index of a field within a recordset helps at all with the data and the structure of the data.
Recordset/field "structure" is somewhat fluid, given that the representation of what lies beneath can be massaged through queries and recordsets, so those are not really structure at all. At least that's my take.