ms access VBA expands subform on current

This is a workaround to expand subdatasheet when a record on the main
datasheet is clicked (selected on current)

Me.SubdatasheetExpanded = False
Me!frmSub1.SetFocus 'subdatasheet name

' on current even of the main datasheet
Private Sub Form_Current()
'Select current Record in the MainForm
DoCmd.RunCommand acCmdSelectRecord

'Hold SHIFT and CTRL, then press the DOWN Arrow to Expand all SubDatasheets
SendKeys "+^({DOWN})"

'Hold SHIFT and CTRL, then press the UP Arrow to Collapse all SubDatasheets
SendKeys "+^({UP})"

End Sub

test post

Null values in the composite index fields

Access does not allow you to put null values in fields that make a part of a composite index (typically foreign keys fields). In some situations that is an undesirable limitation. For instance, U may want to allow the user to keep it blank without the choice and built in some logic to handle these situations. In this case U need a work around.

For all such fields U can input a standard (default) value equal to 0 in the table designer. On your form a foreign key field will typically have a dropdown list which would not have 0 value. From the user perspective the field will be left out blank. However on the background, on insert of a new record with the "blank" value for the field, Access will put 0 in the table. (0 - well, any value U like)

SO if U want to use composite indexes to avoid double records (which is a good practice), but U would like to make the choice not obligatory, U can use this routine.

Running executable from VBA / Access / Shell

When calling an executable file from VBA, you need to use CMD
function, otherwise Shell endup in a default my documents directory
and there is no way to tell it the right path.

This function calls CMD with the path parameter and the file
parameter. Path parameter also guarantees that your executable will
find any files it needs.

Public Function runSchell(Optional path As String = "", Optional file
As String = "XCheckSample.exe") As Long

Dim st As String
If path = "" Then path = CurrentProject.path & "/temp"
st = "cmd /c Start ""Title"" /wait /D """ & path & """ " & file
runSchell = Shell(st, 1)

End Function

Slow ms access forms in ms office 2007 when converted from office 2003

When an application developed for office 2003 is opened in access 2007
you might experience some slow behavior in controls that compute sum.
there is a hotfix for that.
steps:
make sure you install office service pack 1.
(be carefull when choising the right language, and conform it. It is a
kind of tricky)
http://www.microsoft.com/downloads/details.aspx?displaylang=nl&FamilyID=9ec51594-992c-4165-a997-25da01f388f5

install hotfix pack
(send a request and the fix will be emailed)
http://support.microsoft.com/kb/956054

now it should work!

MS Access, VBA for Working with ODBC linked tables

I had to link several Oracle tables via ODBC. It works find via Access
wizard. Nevertheless, every time the application starts, the user is
asked to enter login and I found a workaround. There are actually two
options to do this.

1. Lookup queries
2. VBA function that recreate linked tables at startup.

The problem of option 1 is the performance if you need just a smal
subset of your data. The problem is that you cannot path through a
variable such as DATE_MIN or DATE_MAX, because the server side
obviously does not know its meaning. So just standard server side sql
is applicable. If you can leave with it, use option 1 and google for
path through queries, it is quite easy to make.

Option 2 was a half day research. First I tried to extend a connection
string for a table definition and refresh it. however this does not
work and access keeps on asking the login. Strange enough, if you
completely CREATE a table definition then it works, but only for this
specific linked table that you created. If you try to open an existing
linked tables coming from the same ODBC database, guess what, you get
the login prompt.

So because re-creating linked tables every time your App got start is
the only option , i made a friendly work around.

In your autoexec macro call a function ConnectLinkedTables ()

This function I defined in a selfmade module mdlConnect

Public Function ConnectLinkedTables()

Dim myDB As Database
Set myDB = CurrentDb
Dim rs As DAO.Recordset
Dim tdf As DAO.TableDef

Set rs = CurrentDb.OpenRecordset("qADMINTABLE")
With rs

Do Until .EOF

If TableExists(!TABLE_NAME) Then CurrentDb.TableDefs.Delete
(!TABLE_NAME)
Set tdf = New DAO.TableDef
'Debug.Print tdf.Connect
tdf.Name = !TABLE_NAME
tdf.Connect = !DATABASE_PATH
tdf.SourceTableName = !TABLE_SOURCE_NAME
CurrentDb.TableDefs.Append tdf
.MoveNext

Loop

.Close

End With

' This one works like a charm!

End Function

qADMINTABLE - source if your linked tables that has to have fields
preceeded with ! in this function.
In my case qADMINTABLE is a query that joins ADMIN_TABLE - tables that
have to be linked with ADMIN_DATABASE
that has a connection string.

DATABASE_NAME DATABASE_PATH
Productivity data ASAP formulier
X:\Part\CCS624\DATAMART\ASAP\DATA\PROD_be.mdb
GenDMAAP
ODBC;DSN=GenDMAAP;SERVER=GenDMAAP;UID=etl_admin;PWD=etl_passw;
DRIVER=Microsoft ODBC for Oracle

the query:

SELECT ADMIN_TABLE.TABLE_ID, ADMIN_TABLE.TABLE_NAME,
ADMIN_TABLE.TABLE_DATABASE_ID, ADMIN_TABLE.TABLE_SOURCE_NAME,
ADMIN_TABLE.CONNECT_AT_LOGIN, ADMIN_TABLE.TABLE_DESC,
ADMIN_DATABASE.DATABASE_PATH
FROM ADMIN_DATABASE INNER JOIN ADMIN_TABLE ON
ADMIN_DATABASE.DATABASE_ID = ADMIN_TABLE.TABLE_DATABASE_ID;

so the function walks through all the tables that have to be linked
and recreate links.
P.S. You will need a function that checks if table exist, google it TableExists

Well, this basically is. Add more tables to ADMIN_TABLE or change
ADMIN_DATABASE.DATABASE_PATH if the login changes.
Your app will experience a slight delay during the start up, but still
it is shorter than time you need to type login and it is error free.
So the end user should appreciate this automation.

Kind regards / Met vriendelijke groet,
Maxim

+31 624 659 713 | Bleekerskade 26 | 6981 LB Doesburg |
www.4suc6.com | KvK 09175538

Management of controls' metadata (tooltips, captions etc.) and international support in your MS Access applications.

A complex access application can have hundreds of controls and the
problem you face is how to smartly manage controls metadata such as
tool tips, captions etc? A flexible solution would allow international
support and update of metadata also by the user himself.

As the standard each control has a number of textual properties and a
developer can assign a value to the tool tip text. Handmade management
of these properties will soon become complex task because of the
growing number of controls. it also makes further customization such
as international support.

An effective solution to this problem is to separate metadata from the
controls and manage it in a separate table(s). Here is a simplified
solution.
On form load event a function GetTooltip is called. The argument of
this function is the control name.

Example:

Private Sub Form_Load()
For Each CTL In Me.Controls
On Error Resume Next
CTL.ControlTipText = GetTooltip(CTL.Name)
Next
End Sub

Public Function GetTooltip(CTRL As String)
GetTooltip = Nz(DLookup("[CTRL_TOOLTIP_NL]", "CONTROLS",
"UCASE([CTRL_NAME])='" & UCase(CTRL) & "'"), "")
End Function

As you can see the function GetTooltip runs a dlookup function that
finds necessary metadata (in this case dutch translation of the
tooltip text) in the metadata table CONTROLS.

The metadata table look like this:

CTRL_NAME CTRL_TOOLTIP_NL CTRL_TOOLTIP_EN

cmdRefresh Gegevens Vernieuwen Refresh data
cmdRepAgents Rooster Rapport Roster Report
imgAgentsForm Medewerker Gegevens Employee Data
cmdAgentRooster Medewerker Bruto Rooster Employee Bruto Schedule
cmdAgentSkill Medewerker Skills Employee Skills

A nice thing about Dlookup is that you can compute the searched
column. For example if the application has a variable ULan that
carries user language settings, the call of dlookup can be adjusted to
the current user language settings like that:

GetTooltip = Nz(DLookup("[CTRL_TOOLTIP_" & ULAN & "]", "CONTROLS",
"UCASE([CTRL_NAME])='" & UCase(CTRL) & "'"), "")

In this case ULAN shoul be assigned a standard language abbreviation
(NL - for dutch, EN for English etc).

In a similar manner all labels, captions and other textual metadata
can be pulled out of the controls metadata table.

This works great for me, why not trying this solution in your applications?

4SUC6