Posts Tagged 'Access'

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

Enabling Mouse Scrolling In VBA

I’ve just had to set up a new PC and, for a few minutes there, was confused as to why the scroll wheel did not work in the VBA code window.

Then I remembered that VBA is based on the Microsoft Visual Basic 6 IDE which doesn’t include built-in support for the scroll wheel.

After a little searching I found the following article on the Microsoft site: http://office.microsoft.com/en-us/access/HA101175901033.aspx (opens in new window)

In short, to enable the mouse wheel scrolling behaviour in the VBA window, you must download a package from Microsoft at http://office.microsoft.com/search/redir.aspx?AssetID=XT101665991033&CTT=5&Origin=HA101175901033 (opens in new window) and then do the following:

  • Run the downloaded executable to extract the files
  • Add the included DLL to your machine
  • Register the DLL using RegSvr32
  • Run the included registry file to add the required elements to your machine’s registry (remembering to take a backup of the registry first of course)
  • Add the MouseWheel Fix to your VBA project using the Add-In manager

Now the scroll wheel should work in your VBA code!

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

  • 222,048 hits

Counter

ClustrMap