SELECT tblUser.userID, tblUser.username
FROM tblUser
WHERE (((tblUser.active)=True))
ORDER BY tblUser.username;

I change the query source to:

SELECT tblUser.userID, tblUser.username
FROM tblUser
WHERE (((tblUser.active)=True))
UNION SELECT 0, 'All'
FROM tblUser
WHERE (((tblUser.userID)=1))
ORDER BY tblUser.username;

I base my virtual record on another record, but I assign its values myself.Therefore my query returns an enlarged list.When using on a form, I set the default value of the dropdown list to 0.I set the dropdown’s update event that when it is set to 0 the filter omits this dropdown, and in all other cases it omits the selected one.I hope you liked this hint.

# The secret of my giant excel array formula is soon to be revealed. 928 characters, 78 left braces, 76 function in a cell.# My Excel monster-formula makes 240 cells needless. Half as many lines are enough 9:13 AM Mar 16th from web# What do you think, how can such a big formula be maintained? Tomorrow I’ll give you the answer.10:39 AM Mar 17th from web# Here’s the great day, and the secret of the giant excel array formula.3 minutes ago from web

One of my customers needed an auxiliary line to perform a calculation reoccurring in every line.This broke the unity of the whole system and made it quite hard to use.My formula was much simpler at the beginning, but I improved it so many times that it became gigantic.It is not easy to review a formula of such size, and to maintain it the traditional way is quite impossible.I turned to VBA for a solution. I pieced the formula together in a macro.I created variables for the recurring parts of the formula, which could even follow from each other:

strDate = "R" & intDateRow & "C"strIndex = "IF(RC" & intIndexColumn & "=0, R24C15, RC" & intIndexColumn & ")"strMonthStartDate = "DATE(YEAR(" & strDate & "),MONTH(" & strDate & ")+1,1)"strMonthEndDate = "DATE(YEAR(" & strDate & "),MONTH(" & strDate & ")+1,0)"

Than from these variables I aggregated the whole function.By breaking apart the formula, it remained maintainable, and comprehensible.This was one of the great secrets, I gathered together in 9 years of Excel programming, but there are a lot more.

Error communicating with the OLE server or ActiveX Control

The error is generally caused by the use of special characters in object names.When you create a form in Hungarian or other localized Access you will see, that the objects on the form get problematic names (Labels – Címke, Header – Űrlapfej, Footer – Űrlapláb, Combobox – KombináltLista etc.)The solution:1. You have to rename the special named objects on the problematic form.2. If the error remained then create a new mdb file and import all objects from your old mdb.

1. Select A2 cell.
2. Choose Insert –> Name –> Define
3. Type a name for the range: “Last”
4. Write in the refers to box “=A1″ without quotes and without dollar signs.
5. Give a name to this range, for example “last”

This range name will refer to the upper cell to the current position.It can be used like this:

=SUM(B2:Last)

will give you a dynamic range that will grow or shrink when you insert or delete some rows, even if the deleted row is the last row from the range.This is a perfect solution.If you insert multiple rows and don’t fill every cell, the normal reference will not upgrade, the relative name will always work.

MsgBox Err.Number & " - " & Erl & " - " Err.Description

But this will show 0 instead of the line number if we didn’t put numbers at the beginning of the rows.Dick Kusleika wrote about the MZ Tools, and MZTools is a great tool for this too.There is an “add line number” icon on the toolbar which made the line numbering in my stead.

Add Line Number Icon