find name of first control on form

[MikeCDPQ]

I have been looking for way longer than I care to admit :-(

I need to find out what is the name of the field that has tab index 0 (or any other tab index number) on any given form

Any suggestions ?

something like: screen.activeform.control(0).name ???

Thanks

 

[Ed2020]

Pass this function the form name and tab index you want to search for. The function will return the control name.

The form will have to be open before calling the function.

Public Function CtrlTab(ByVal strFormName As String, ByVal intTabIndex As Integer)
    On Error GoTo TrapErr
    
    Dim MyForm As Access.Form
    Dim ctrl As Access.Control
    Dim strRetVal As String
    
    Set MyForm = Forms(strFormName)
    
    For Each ctrl In MyForm.Controls
        If ctrl.TabIndex = intTabIndex Then
            strRetVal = ctrl.Name
            Exit For
        End If
Restart:
    Next ctrl
    
    CtrlTab = strRetVal

ExitHere:
    Exit Function
    
TrapErr:
    Select Case Err.Number
        Case 438
            Resume Restart
        Case Else
            MsgBox Err.Description
    End Select
End Function

Please do not feed the trolls.....

 

[MikeCDPQ]

Thanks Ed2020,

Tried the code and it doesn't quite work. I'll fiddle with it and make it work.

Thanks a bunch !

Mike

 

[Ed2020]

Mike,

Can you post back and let me know what the problem was please?

Cheers,

Ed Metcalfe.

Please do not feed the trolls.....

 

[JoeAtWork]

Perhaps he didn't have the form open when he called the function. Only currently open forms are in the Forms collection.

 

[TheAceMan1]

How are ya MikeMcKesson . . .

In a [blue]module[/blue] in the [blue]modules window[/blue] copy/paste the following:

[blue]Public Function TabIdxName(frmName As String, TabIdx As Integer) As String
   Dim Msg As String, Style As Integer, Title As String
   Dim frm As Form, ctl As Control, Found As String, flg As Boolean
      
   Style = vbInformation + vbOKOnly
   
   If FormExist(frmName) Then
      If Not IsOpenForm(frmName) Then
         DoCmd.OpenForm frmName, acDesign, , , , acHidden
         DoEvents
         flg = True
      End If
      
      Set frm = Forms(frmName)
      
      For Each ctl In frm.Controls
         If ctl.TabIndex = TabIdx Then
            Found = ctl.Name
            Exit For
         End If
      Next
      
      If Found <> "" Then
         tabidxfound = Found
      Else
         Msg = "Control Name not found or TabIdx too high!"
         Title = "Control Not Found! . . ."
         MsgBox mag, Style, Title
      End If
      
      If flg Then DoCmd.Close acForm, frmName, acSaveNo
      Set frm = Nothing
   Else
      Msg = "Form not found or doesn't exist!"
      Title = "Can't Find Form Error! . . ."
      MsgBox Msg, Style, Title
   End If
   
End Function

Public Function FormExist(frmName As String) As Boolean
   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   SQL = "SELECT Name, Type " & _
         "FROM MSysObjects " & _
         "WHERE ((Name='" & frmName & "') AND " & _
                "(Type=-32768));"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If Not rst.BOF Then FormExist = True
   
   Set rst = Nothing
   Set db = Nothing
               
End Function

Function IsOpenForm(frmName As String) As Boolean
   
   If CurrentProject.AllForms(frmName).IsLoaded Then
      IsOpenForm = True
   End If
   
End Function[/blue]

To call the function:

[blue][i]variable[/i] = TabIdxName([purple][b][i]FormName[/i][/b][/purple], [purple][b][i]TabIndex[/i][/b][/purple])
    or
Me![purple][b][i]TextboxName[/i][/b][/purple] = TabIdxName([purple][b][i]FormName[/i][/b][/purple], [purple][b][i]TabIndex[/i][/b][/purple])
    or
= TabIdxName([purple][b][i]FormName[/i][/b][/purple], [purple][b][i]TabIndex[/i][/b][/purple])[/blue]

Doesn't matter if the form is open or not! . . .

[blue]Cheers! . . .[/blue]

See Ya! . . . . . .