An Alternative to the BrowseTo Command

<< Click to Display Table of Contents >>

Navigation:  VBA >

An Alternative to the BrowseTo Command

I use Navigation Controls in my software quite a bit. I find them cleaner than Tab Controls and they do have the advantage of automatically shutting down recordsets when you move focus from one navigation button to the next. But I have never actually controlled which button has the focus, I have always left that to the user and just organised the buttons in a sensible order. Of course the day came when I decided to get in Control.

 

The only Microsoft Solution I could find

 

After a while the sparse information on moving Navigation control focus (.setfocus is a no go) lead me to this Microsoft blog post on the BrowseTo command.  I tried many different combinations of subform names and I just couldnt get it to work. I even resorted to Macro's (in a moment of madness) for a brief period  (see figure1) and still couldn't get anywhere. This generally involved an error with the wording "The macro action BrowseTo requires a valid path argument. So I gave up

 

201605_gr1

Figure 1. The BrowseTo Action in a macro

 

 

 

Garry's GoTo Solution

 

I stumbled on the goto command and I use that to shift the focus to the Navigation button that I want to switch to.  I then I send off a Carriage Return using the SendKeys command (ugly). In the following code, the navigation button is called NavButAll.

 

 

Forms![FX_AGstockManager]![NavButAll].GoTo

SendKeys "{ENTER}"

 

Lets Look At My Solution in Action

 

Here is a Navigation tab (figure 2) and I want the item search box to show all the valid records in the All Stock Navigation tab.

 

201605_gr2

Figure 2. A search box in a navigation control

 

My code then changes the label of the button and changes the focus to the button

 

201605_gr3

Figure 3 - Change the focus in code and change the label of the Nav button

 

The Code Under The Item No OnClick Event

 

The full code to do this is as follows ( I apologize in advance that the code is very specific). Remember that the Navigation button "All A & G Stock" shown in figure 2 is called NavButAll

 

Private Sub gstockItemNo_Click()

  Dim Varid As Variant

  Dim i As Integer

  

  Varid = gstockItemNo

  

  If Not IsNull(Varid) Then

    If DCount(1, "tfxGstockJobs", "itemno = '" & gstockItemNo.Column(1) & "'") > 1 Then

      Form_FX_AGstockManager.NavButAll.NavigationWhereClause = "itemno = '" & gstockItemNo.Column(1) & "'"

      Form_FX_AGstockManager.NavButAll.Caption = gstockItemNo.Column(1)

      lblMulti.visible = True

      

      Call showAllGstock

 

    Else

     'I only found 1 so I open the full form itself and do not switch to the tab view.

      OpenGJob gstockItemNo.Column(2)

 

    End If

  Else

    'Nothing was found

  End If

  

End Sub

 

Private Sub showAllGstock()

 

Forms![FX_AGstockManager]![NavButAll].GoTo

SendKeys "{ENTER}"

 

End Sub

 

 

The BrowseTo Command Definition

 

https://msdn.microsoft.com/en-us/library/office/ff196381.aspx

 

 

Other Links You May Like