Convert Active Control Name

[Middlemarch]

How can I convert a Form active control name to it's recordsource number?



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

[June7]

A control doesn't have a 'recordsource number'. Fields have an index reference depending on the order pulled by SQL. Exactly what are you trying to accomplish?

[Micron]

I'm guessing that the field number is what's wanted.
So loop over recordsetclone fields and get the value of the loop when field name test is found. Presumably 1 based result though, and not 0 based is wanted.

[Middlemarch]

I'm doing that e.g.

Set r = Me.subTracks.Form.RecordsetClone
r.MoveFirst
    For i = 0 To r.Fields.Count - 1
        Debug.Print i, r.Fields(i).Name
    Next

I already know the field name though, and want its number.
Perhaps there no 'immediate' way ? If so I can loop until the right name.
Thanks.

[Micron]

Don't move first if you haven't tested the record count. If you test for bof and eof being true, then in this case you don't need to move first, nor do you need the count.

Dim i As Integer
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
If Not (rs.EOF And rs.BOF) Then
     For i = 0 To rs.RecordCount - 1
          If rs.Fields(i).Name = "Test" Then MsgBox i + 1
     Next
End If
Set rs = Nothing

If it matters and the form is a datasheet, this has nothing to do with the position of the datasheet column.

[Middlemarch]

@Macron. I've confused myself... thought it was working, but no.
rs.Fields(i).Name is the Name field in the property sheet, but what I want to use is the control source name.

[Micron]

A control source is either
- the name of the field in the form recordsource (the underlying query or table for the form) that the field is bound to or
- an expression that provides a calculated (or other) result to the control

what I want to use is the control source name.

So that would mean you want the field name that the control is bound to? That wouldn't make sense because you already know that it is Test.

EDIT - come to think of it, your post title refers to the active control, so what is the point of looping anything as in post 4?

[Middlemarch]

I'm certainly going around in circles.. it's not the control source, in my test that was also it's Name and it mucked me up.
It may be better to show you some code as I now doubt my ability to describe what I'm after.

Dat = Me!subTracks.Form.ActiveControl.Name
 Select Case Dat
        Case "Path"
            o = 37
        Case "txtNum"
            o = 48
        Case "txtName"
            o = 5
        Case "TSize"
            o = 7
        Case "Year"
            o = 38
        Case "TStatus"
            o = 9
        Case "Notes"
            o = 12
        Case "txtTime"
        
        Case Else
            Exit Sub
    End Select

Then I open a recordset on my table and
if r(o) = MyMatch then ....do stuff

I know the values of "o" by trial and error.
What I'm trying to achieve is do away with the Select Case and calculate what "o" is depending on the Form.ActiveControl.Name

[June7]

By what trial and error? Is o supposed to be the field index in recordset? As advised, this depends on the order fields are retrieved in dataset. Is form bound to table, query object, or SQL statement? The field order in recordset is not necessarily same as in form's RecordSource.

In other words, a field's recordset index is not a property of any control that may be bound to that field. And a field's recordset index can be different in various datasets.

Since a bound control's ControlSource would be the same as field name, maybe this would be useful:

rs.Fields("[" & Me.subTracks.Form.ActiveControl.ControlSource & "]")

If you want to provide db for analysis, follow instructions at bottom of my post.

[Micron]

I've never seen ActiveControl used that way. AFAIK, it's a property of the screen object, not a form. You're still not making sense with "I know the values of o" yet you want to calculate it? If you can't just use IF r(o) = Screen.ActiveControl.Name THEN, then we (or at least I) need to see a db copy that you compact/zip and post here. Remove whatever you need to for privacy. Just make sure you leave instructions for what to do/open and such. We're almost 10 posts in and I have no idea what you're really saying and I think it might be your use of terms.

[Middlemarch]

@June, Hi by 'trial and error' meant I'd worked out in advance what the 'o' values were.
The Form (subform) is bound to a query, and yes I think the field index in recordset is what I'm after.
I suspect you're saying there's no direct correlation between the query and datasheet Form as regards numbering ? (There are more fields in the query than on the Form.)
So okay, I need to device some way to get that result, if there's no "Access way". I'm sure I'll figure something. Form shows 16 fields so maybe Switch.

[June7]

You got what the 'o' values (field index) for fields of form? And yes, those indexes could be different in the recordset. So unless the fields are in the same order in both datasets (say the first 16), even if there are more fields in the recordset, there is no correlation.

[Minty]

I think it might help if we knew what your where trying to achieve from a slightly higher level here.
If you are on field "DateFilledOut" on your form and you want to do what?

I don't understand the relevance of knowing what field index it is in the underlying recordset?
How does that actually mean anything?

[moke123]

I've read this thread a dozen times and I'm still confused. Especially with the introduction of "The Form (subform) is bound to a query, " and "datasheet Form "
What is it you're doing with "o"?

[orange]

Based on what others have said, I suggest you tell us more about WHAT you want to achieve in order to offer responses as to HOW it may be done.