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]

SQL Server – Enter Value from Keyboard

Here’s another of those things I forget if I don’t use SQL Server for a while.

To enter a value of <NULL> into a field in SQL Server just type:
[Ctrl] + 0

(Hold down the Ctrl key and press the zero key).

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

Excel VBA – PERSONAL.XLS Locked For Editing – Resolution

I’ve just been working on an Excel problem that requires one worksheet to be opened at a given time (using the Windows Task Scheduler) and a macro run.

However, when testing this I came across the “Personal.XLS locked for editing” message.

It seems that this is due to having more than one instance of Excel running at one time.

Dave Peterson at http://www.pcreview.co.uk/forums/thread-2831009.php has suggested a solution to this.

This involves marking the personal.xls file as read-only and this, indeed, seems to stop the error.

If you haven’t come across the personal.xls file before it’s a hidden workbook that opens when you start Excel. Any code in this workbook is available in all workbooks you have open in Excel.

On a Windows XP machine the file is usually located in the Excel startup folder at:

C:\Documents and Settings\[UserName]\Application Data\Microsoft\Excel\XLSTART

If you’re using Vista it’s probably in the folder at:

C:\Users\[UserName]\AppData\Roaming\Microsoft\Excel\XLSTART

where [UserName] is the user name you’re currently logged onto the machine as.

However, the file could be located elsewhere so use Search to find it if it’s not in the folders mentioned above.

Although making the file read-only solves this problem it does raise other issues if you often add code to personal.xls but is a useful workaround in my situation.

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.

Excel VBA – Save As (SaveAs) Without Confirmation

When saving a file in Excel from VBA code it is sometime useful to avoid the display of a Save As dialog if the file to be saved already exists (i.e. to overwrite any exisiting files).

This is pretty easy but one of those things I always forget how to do!

Here’s the code:

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs (etc.)
Application.DisplayAlerts = True

Thanks to the users at http://www.vbforums.com/showthread.php?t=528826 who reminded me of this for about the hundredth time!

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. (http://movefirstblog.wordpress.com/2008/09/10/access-2007-hide-the-navigation-pane-shutter-bar/)

HUMOUR – An Elephant On A Slope and B Is The New C

The joy of StubleUpon is finding pages like the following.

The following two images are of answers submitted for exams.

His professor sent him an e-mail the following day:

Dear Michael,

Every year I attempt to boost my students’ final grades by giving them this relatively simple exam consisting of 100 True/False questions from only 3 chapters of material. For the past 20 years that I have taught Intro Communications 101 at this institution I have never once seen someone score below a 65 on this exam. Consequently, your score of a zero is the first in history and ultimately brought the entire class average down a whole 8 points.

There were two possible answer choices: A (True) and B (False). You chose C for all 100 questions in an obvious attempt to get lucky with a least a quarter of the answers. It’s as if you didn’t look at a single question. Unfortunately, this brings your final grade in this class to failing. See you next year!

May God have mercy on your soul.

Sincerely,
Professor William Turner

P.S. If all else fails, go with B from now on.
B is the new C

If you like this particular form of humour take a look at the two sites that I found these on:

http://totallysux.com/photo/20338/

http://funnyexamanswers.com

SQL SERVER – SUBSTRING- The Equivalent of the VBA MID Function

Following on from my previous post regarding the SQL Server CHARINDEX function here’s some information about the SUBSTRING function for those of you, like me, tend to think in VBA!

The SQL Server equivalent of the VBA MID function is SUBSTRING.

We can use it to find a part of a string.

For example:

SUBSTRING(“This is text”,3,2) would return the string “is”.

Microsoft defines the function’s syntax as follows.

Syntax

SUBSTRING ( expression , start , length )

Arguments

expression

Is a character string, binary string, text, image, a column, or an expression that includes a column. Do not use expressions that include aggregate functions.

start

Is an integer that specifies where the substring begins.

length

Is a positive integer that specifies how many characters or bytes of the expression will be returned. If length is negative, an error is returned.

Note Because start and length specify the number of bytes when SUBSTRING is used on text data, DBCS data, such as Kanji, may result in split characters at the beginning or end of the result. This behavior is consistent with the way in which READTEXT handles DBCS. However, because of the occasional strange result, it is advisable to use ntext instead of text for DBCS characters.