Solved Select the second tab from the left

[Marshall Brooks]

Like the title says ...

My database has a switchboard and 7 main forms - I'll call them Form1, Form2 ... Form7.

There is a button on the switchboard that brings up a pop-up with checkboxes that let's you select multiple forms to open.

So, if I select Forms 2, 3, 5, 6, and 7, I end up with (left to right), the switchboard, Form2, Form 3, Form 5, Form 6, and Form 7 with Form 7 Selected.

I would like Form 2 selected (the first form loaded after the switchboard).

If there a way to do this with code?

If not, I can check if Form1 is loaded, and if so, Open Form1, else if Form2 is loaded, open Form2 etc., which should give the same end result.

Thanks in advance!

 

[MajP]

If not, I can check if Form1 is loaded, and if so, Open Form1, else if Form2 is loaded, open Form2

Public Function IsLoaded(FormName As String) As Boolean
 IsLoaded = CurrentProject.AllForms(FormName).IsLoaded
End Function
Public Sub Test()
  Debug.Print IsLoaded("frmMain")
End Sub

 

[Marshall Brooks]

Didn't word that clearly. I know how to check if the form is loaded. I don't know how (or if it is possible programmatically) to select the form on the second tab, other than looping through the forms (which with only 7 forms isn't a huge deal.)

 

[MajP]

You know the first form you opened. In this case it "form2". So simply set the focus
forms(variableholdingfirstformsname).setfocus

 

[theDBguy]

Didn't word that clearly. I know how to check if the form is loaded. I don't know how (or if it is possible programmatically) to select the form on the second tab, other than looping through the forms (which with only 7 forms isn't a huge deal.)

Perhaps a screenshot could help clarify the request?

 

[Marshall Brooks]

You know the first form you opened. In this case it "form2". So simply set the focus
forms(variableholdingfirstformsname).setfocus

Brilliant and much simpler than how I was planning to do it!!!

 

[MajP]

When forms are opened they are added to the forms collection in order. They are indexed starting at 0. If your switchboard is open first it is Forms(0). The next form opened is Forms(1).
In your case this should work.
if Forms.count > 1 then Forms(1).setfocus

Note

If a form contains controls for which the Enabled property is set to True, you can't move the focus to the form itself. You can only move the focus to controls on the form. In this case, if you try to use SetFocus to move the focus to a form, the focus is set to the control on the form that last received the focus.

 

[Marshall Brooks]

Even better!!! Thanks again!

 

[Marshall Brooks]

if Forms.count > 1 then Forms(1).setfocus

Doesn't seem to work for me - that returns me to the Switchboard.
But

If Forms.Count > 2 Then Forms(2).SetFocus

does work.

Are you SURE the index starts at zero? (You would know better than I would ...)

 

[theDBguy]

Are you SURE the index starts at zero? (You would know better than I would ...)

Should be easy enough to verify. I would think you would get an error with this, if using a zero is invalid:

If Forms.Count > 2 Then Forms(0).SetFocus

 

[Marshall Brooks]

Disregard - we are both correct. I forgot there was a hidden form that loads at startup. I had 8 forms visible and "MsgBox Forms.Count" returned 9 - so the count starts at 1 but the index starts at 0. Forms(0).SetFocus opened the hidden form.

It works!!!!

 

[MajP]

This may be more foolproof. You want to set focus on the Nth visible form. So loop the forms collection and determine the index of the Nth visible form
This would work even if you have multiple forms hidden (not common)

Public Sub SelectVisibleForm(FormNumber)
  'This procedure is One based. i.e. The second visible form opened   is then FormNumber 2.
  Dim visibleCount As Integer
  Dim i As Integer
  For i = 0 To Forms.Count - 1
    If Forms(i).Visible Then visibleCount = visibleCount + 1
    If visibleCount = FormNumber Then
      Forms(i).SetFocus
       Exit Sub
    End If
  Next i
End Sub

 

[Marshall Brooks]

Very nice code and I appreciate the effort, but I like the single-line code you posted earlier. And it's not THAT critical. If we have to click a different tab to get to the desired form once in a blue moon, I can live with that.

 

[Marshall Brooks]

Semi-related question ...

At startup, my database opens a hidden form and then the switchboard. It uses tabbed windows. From the switchboard, users can open any combination of 7 additional forms. If the switchboard is closed, the timer event of the hidden form re-opens it.

However, it opens it after the last visible form. I can manually drag it to the left.

Is it possible to do this via VBA?

i.e. can I either (re-)open the switchboard as the first visible tab, or open it and move it to the far left using VBA?

I don't want to go the brute force approach of checking all the forms that could be open and closing and re-opening them, although I'm pretty sure that would work. (It's less hassle to move it manually in that case.)

Thanks in advance!

 

[MajP]

This seems to work well to set your form order. Hide not close. Hide all of them and add the visible forms to a custom collection. Add your switchboard at the beginning of the collection. Show then in order as stored in the collection. Should not be any flicker or cause onload events to fire.

Public Sub SetOrder(FirstFormName As String)
  Dim frm As Access.Form
  Dim myForms As New Collection
  For Each frm In Forms
   
    If frm.Name = FirstFormName Then
      If myForms.Count > 0 Then
        myForms.Add frm, frm.Name, 1
      Else
        myForms.Add frm, frm.Name
      End If
    ElseIf frm.Visible = True Then
      myForms.Add frm, frm.Name
    End If
    frm.Visible = False
  Next frm
   
  For Each frm In myForms
    frm.Visible = True
  Next frm
   
End Sub

Public Sub Testit()
  SetOrder "Switchboard"
End Sub

 

[Marshall Brooks]

Thank you!!! (I'll test it later ...)

 

[MajP]

FYI. The design is up to you, but it is much more common now a days use pop up forms vice tab forms with only one form open at a time. Better control of the logic, and you can make it look more like a real application. If there is a reason to have multiple forms visible then you may consider an unbound main form with a tab control holding subforms.

 

[Marshall Brooks]

@MajP - Code in Reply #15 works great and is exactly what I was looking for.

Tabbed forms work for us and how we use the database. I have seen other databases that don't use this method and that work well also.

Thanks again!!!

 

[Marshall Brooks]

@MajP Ran into an odd error with your code (Reply #15).

The code works fine if I run it standalone.

I am trying to run it from a timer on a hidden form that re-opens the switchboard if it is manually closed.

In that case, it does not move the form to the left, although it does open the form - probably because the form has not finished opening yet. Also, I don't get any error messages, but the timer is not running after this occurs.

The relevant portion of the timer code looks like this:

Private Sub Form_Timer()
   On Error GoTo Err_Handler
'https://www.access-programmers.co.uk/forums/threads/multiple-events-at-different-times-from-one-timer.328630/
Me.TimerInterval = 1000 ' 1-second
Static TimerCounter As Long
TimerCounter = TimerCounter + 1 ' or at the end of proc if all should run on first call (TimerCounter == 0)
   If TimerCounter Mod 1 = 0 Then 'Check every second.
'     Debug.Print "Proc 0", timer, Now()
        If SysCmd(acSysCmdGetObjectState, acForm, "frmSwitchboard") = 0 Then
           DoCmd.OpenForm "frmSwitchboard"
        '   DoUntil (SysCmd(acSysCmdGetObjectState, acForm, "frmSwitchboard") <> 0)
        '        DoEvents
        '   Loop
           Call SetOrder("frmSwitchboard")
           Forms("frmSwitchboard").SetFocus
        End If
   End If

I tried adding the green steps to wait until the switchboard opened before running SetOrder, but with those line uncommented, I get an error "The Open Event was cancelled" on startup.

???

 

[Marshall Brooks]

Somewhat surprised this does not work either:

Private Sub Form_Timer()
   On Error GoTo Err_Handler
'https://www.access-programmers.co.uk/forums/threads/multiple-events-at-different-times-from-one-timer.328630/
Me.TimerInterval = 10000 ' 10-seconds
Static TimerCounter As Long
TimerCounter = TimerCounter + 1 ' or at the end of proc if all should run on first call (TimerCounter == 0)
   If TimerCounter Mod 1 = 0 Then 'Check every 10 seconds.
'     Debug.Print "Proc 0", timer, Now()
        If SysCmd(acSysCmdGetObjectState, acForm, "frmSwitchboard") = 0 Then
           DoCmd.OpenForm "frmSwitchboard"
           Pause (5)
           Call SetOrder("frmSwitchboard")
           Forms("frmSwitchboard").SetFocus
        End If
   End If

More info - I tried commenting out the Call SetOrder and .SetFocus lines and the timer does not run again if I close and re-open the switchboard form. Not sure why. I put in a msgbox to fire when the timer runs (or at 10 seconds) and I see it until I close the switchboard. Then I close the switchboard and click OK on the msgbox and the switchboard opens, but the msgbox never appears again ???