Function that find all controls on a form and puts their tab index in numerical order

[breezett93]

I am trying to implement the functions listed here:

https://extramiledata.com/get-list-form-controls-sorted-tab-order/

I created a button on my form that calls the first function:

Private Sub btnControlsInTabOrder_Click()
Dim QuoteFrm01 As String

TestControlsInTabOrder (QuoteFrm01)
End Sub

I am getting an error message on this line of TestControlsInTabOrder. "2494, action or method requires a Form Name argument. "

 DoCmd.OpenForm FormName, acDesign

I'm not sure what argument would be missing. Only the name is required. Everything else is optional.

 

[dhookom]

Where do you set the value of QuoteFrm01? Is this the actual name of the form or a string memory variable as your code suggests?

Just a guess, but try:

Private Sub btnControlsInTabOrder_Click()
[COLOR=#4E9A06]'Dim QuoteFrm01 As String[/color]

TestControlsInTabOrder ("QuoteFrm01")
End Sub

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Since you "created a button on my form", you may also try:
[tt]
TestControlsInTabOrder ([blue]Me.Name[/blue])[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[breezett93]

I tried both of your solutions, and they take me to the same spot.

I now have a run-time error 2465 "Application-defined or object-defined error" on this line in the TestControlsInTabOrder Function.

For Each ctl In ControlsInTabOrder(Forms(FormName).Detail, True, True)

Here's the whole short function for easy viewing:

Public Function TestControlsInTabOrder(FormName As String)
' This procedure provides an example of how to use ControlsInTabOrder().
' It puts the visible controls of the Detail section that have a tab stop in
' tab index order and prints their names to the immediate window.
    
    Dim ctl As Control
    
    DoCmd.OpenForm FormName, acDesign
    
    For Each ctl In ControlsInTabOrder(Forms(FormName).Detail, True, True)
        Debug.Print ctl.Name
    Next ctl

    DoCmd.Close acForm, FormName

    Set ctl = Nothing
End Function

 

[Andrzejek]

I think you have an issue with the other Function:

Public Function ControlsInTabOrder(FormSection [blue]As Access.Section[/blue], _
    TabStopOnly As Boolean, VisibleOnly As Boolean) As Collection
...

where you pass [tt]Forms(FormName).Detail[/tt]
So, do you have a [tt]Detail[/tt] section on your Access Form?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[breezett93]

Yes, the form I am testing does have a Detail section with ~20 controls.

 

[dhookom]

The functions work as expected in my database. Set a breakpoint in your code to step through. Also, please tell us how you are calling the test function.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[breezett93]

When I click the button, the test function is called:

Private Sub btnControlsInTabOrder_Click()

TestControlsInTabOrder (Me.Name)
End Sub

I pretty much copy/pasted both functions making no changes. Then I added one button to trigger everything.

 

[dhookom]

Try enter the name of a form that isn’t open. Make sure the name is in quotes.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[breezett93]

I tried your suggestion. Same error in the same place.

For Each ctl In ControlsInTabOrder(Forms(FormName).Detail, True, True)

It seems like something is wrong with the ControlsInTabOrder function as info is passed to it.

Public Function ControlsInTabOrder(FormSection As Access.Section, _
    TabStopOnly As Boolean, VisibleOnly As Boolean) As Collection

I'm guessing you also did not make any adjustments and just copy/pasted?

 

[dhookom]

Did you compile your code? There was a zip file on the code web site. Did you check it out? It might contain a working demo.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[dhookom]

The zip download only contains the code.

The other issue I see regularly is naming a module the same as the name of a sub or function.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[breezett93]

I did download the zip file. It has a .bas file which I've never imported into Access before. I know the .bas file is in the downloads folder, but when I try to import it, the folder is empty.

 

[dhookom]

Can you answer my other questions about the name of the module and compiling your code?

If you continue to struggle, I would create a new simple database with one form and the code.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[breezett93]

I do not have this code in a module. For testing, I have the code in the VBA of the form (QuoteFrm01).

When I have tried compiling in the past, the database usually locks up. Likely this is due to its age and size.

 

[dhookom]

Move the code to a separate module named modCtrlCode. Try it in a new database with only a form with some text boxes in the detail section.



Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[breezett93]

Okay, I made a new module in a new database and copied both functions to it. Then I made a new button on one of the forms calling the Test function. After clicking the button, I got no errors, but I got no message either showing the results of the list of controls.

 

[dhookom]

Did you check the immediate window (press Ctrl G)?
Are you attempting to submit the same form as the one containing the button?
I would close all forms and call the test function from the Immediate Window.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[breezett93]

Thank you! The results were in the immediate window.

Now I'll try to replicate in the original database.

 

[dhookom]

Glad you got the simplified sample to work.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016