Archive for the 'Access' 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 VBA – Change the Name of a Field in an Existing Table

I’ve just been importing some records from Excel to Access using VBA.

By default the names of the fields of the imported records are “F1”, “F2”, “F3” etc.

To make things a little clearer I wanted to rename these imported fields to more meaningful names.

Here’s how to do it:

CurrentDb().TableDefs(TableName).Fields("OldName").Name = "NewName"

where:
TableName is the name of the table in which we want to change the names
“OldName” is the old name of the field
“NewName” is the new name we want to use for this field

Access Queries: Concatenate Multiple Fields Into A New Field Seperated By New Lines (vbCRLF)

Often I come across a database which has been designed with multiple address line fields.

We usually want to concatenate these multiple address lines into a new field with each of the old fields on a new line.

To do this I usually create a new query and use something like the following SQL:

SELECT IIf(Not IsNull([Addr1]),[Addr1] & Chr(13) & Chr(10),"")
& IIf(Not IsNull([Addr2]),[Addr2] & Chr(13) & Chr(10),"")
& IIf(Not IsNull([Addr3]),[Addr3] & Chr(13) & Chr(10),"")
& IIf(Not IsNull([Addr4]),[Addr4] & Chr(13) & Chr(10),"")
AS AddressPart, tblPerson.PersonID INTO newaddress
FROM tblPerson;

Where addr1, addr2, addr3 and addr4 are the old single line address fields, AddressPart is the new field with each of the single lines combined and newaddress is the temporary table I want to put the results of the query into.

This checks if each of the original fields are NULL and, if not, adds them to the new field followed by Chr(13) and Chr(10) (the equivalent of a vbCrLf). If the field is NULL it is ignored.

I usually put the results of the query into a new table and then use an UPDATE statement to combine this with the existing table.

Although this approach isn’t very elegant it allows me to retain each of the seperate fields until I’ve imported the new joined up address and checked if everything has worked okay.

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/)

Microsoft Access – Handling NULL values with the NZ() function

In Microsoft Access VBA, as in many other implementations, if we try to evaluate an expression containing a NULL value the whole expression will return NULL.

For example:

Dim varMyValue as Variant
varMyValue = NULL
MsgBox IsNull(varMyValue + 2)

This will return TRUE.

However, it is often the case that we would like this expression to “ignore” the NULL value and return the value 2.

We can achieve this using the NZ function.

The syntax of this function is as follow:

Nz(variant, [valueifnull])

Thus we could ignore the NULL value and substitute it with 0.

e.g.

Dim varMyValue as Variant
varMyValue = NULL
MsgBox NZ(varMyValue,0) + 2

Here the NULL value is replaced with the value zero and our message box displays the value 2.

We could also use the NZ function in other ways.

If we have a variable containing a string , for example, the country of a purchaser (e.g. UK, US, PL) and we use this to calculate a postage rate for goods sent to this purchase we could use the NZ function in the following way to replace a NULL value with a suitable message or calculation.

e.g.

strPostage = NZ(strCountry,0) ' if we wanted to set the postage value to zero if it is NULL

or

strPostage = NZ(strCountry,"No Country Code Given") ' to maybe use in a MsgBox if strCountry is NULL

We could also use the NZ function in an Access SQL query.


SELECT [Surname], [FirstName], [DOB], NZ([Address],"No Address Available")

Note, however, that this function is only available within Access and not within any of the rest of the Office suite. Also, other variants of SQL may not support this function.

Microsoft Access – Parse OpenArgs – Seperating Each OpenArg Value

I haven’t used the OpenArgs property in Microsoft Access for ages.

However, today I needed to pass a variable number of values from a form to another form.

In doing so I needed to seperate out each value passed in the OpenArgs property of the calling form which have been seperated using the | character (accessed using shift + \ on my UK keyboard).

So how did I do it?

Here’s the code:

Dim Hold_OpenArgValues() as String
Hold_OpenArgValues()=Split(Me.OpenArgs, "|")

We can then do something with the values returned to the array.

As an example we could write them to the Immediate window using the UBound function to decide how many items have been passed (this was going to be variable remember).

For counter = 0 to UBound(Hold_OpenArgValues)
  Debug.Print "Value " & counter, Hold_OpenArgValues(counter)
Next counter


Blog Stats

  • 221,277 hits

Counter

ClustrMap