Archive for the 'Access 2007' Category

Access 2007: Refering To A Control On A Subform Within A Form On A Tab

Wow! typing that heading is more complicated that solving the problem!

I have a main for that contains a number of tabs (let’s call the form “frm_MainForm”). The tab is called “tab_MyTab” (but this doesn’t really matter – see below!).

One one of those tabs contains another form (let’s call it “frm_FormOnTab”).

This form then contains a subform (let’s call this one “frm_subformOnForm”) which in turn contains a control for which I want to retrieve the value (let’s call the control “txt_MyControl”).

So, to summarise, we have:

frm_MainForm
tab_MyTab
frm_FormOnTab
frm_subformOnForm
txt_MyControl

How do we reference the value contained in txt_MyControl?

The syntax is:

=[Forms]![frm_MainForm]![frm_FormOnTab]![frm_subformOnForm].[Form]![txt_MyControl]

Access 2007 – Hide the Navigation Pane / Shutter Bar

When working on a database in Access 2007 it may be useful to hide the Navigation Pane (Shutter bar) at the left hand side of the screen.

We can do this manually by changing the Navigation options in the Current Database properties within the Access options window. (Click the Microsoft Office Button and then click the Access Options button).

You may then disable the the Navigation Pane (for this database) by using the Enable Navigation Pane checkbox.

You can also hide and unhide the Navigation Pane at anytime by pressing the F11 key. (You could use the SendKeys code from my other post here and replace F1 with F11 to do this in VBA code).

However, it may be useful to remove the Navigation Pane entirely.

We can do this by using the following macro:

1) Start a new macro.

2) In the “Action” column select “SendCommand”

3) In the “Arguments” column select “WindowHide”

Running this macro when your database first loads will now hide the navigation pane.

Access 2007 – Hide the Ribbon Bar

When working on a database in Access 2007 it may be useful to hide the Ribbon Bar at the top of the screen.

We can do this manually by pressing Ctrl+F1 at anytime.

However, it may be useful to do this within VBA code.

We can do this by using the following code:

If Application.CommandBars("Ribbon").Height = 147 Then
SendKeys "^{F1}", True
End If

This will test the current size of the Ribbon Bar (which is in the Application.CommandBars collection).

If the Ribbon is currently shown its height will be 147.

We can use this information to test if the Ribbon Bar is currently visible and, if it is, to hide it by using the SendKeys function to send the Ctrl+F1 key combination.

Perhaps in the next version of office Microsoft will let use use Application.CommandBars(“Ribbon”).Visible=False!

If you found this useful you may like the following post regarding how to hide the Navigation Pane. (https://movefirstblog.wordpress.com/2008/09/10/access-2007-hide-the-navigation-pane-shutter-bar/)


Blog Stats

  • 222,048 hits

Counter

ClustrMap