Easy Drill Through with Macros

Published 14 January 08 11:18 PM

This tip comes from Tim Getsch - from www.checkoutmycards.com. He wrote to me after seeing my recent post about Display As Hyperlink:

There is the trick that I use for creating hyperlink navigation.

  1. Create an Access (not VBA) macro with a short name (e.g. dt for Drill Through)
  2. Add named macros for each of the things you commonly want to drill to
    • Contact:           OpenForm “Contacts”, WhereCondition: ="[ID]=" & Screen.ActiveControl
    • Orders:             OpenForm “Orders”, WhereCondition: ="[OrderID]=" & Screen.ActiveControl
    • ProductByID:     RunCode MySpecialOpenForm([ProductID])
    • …
  3. When you want a hyperlink simply do the following
    • Set Display As Hyperlink to Screen Only
    • Flip to the Events tab of the property sheet and put the cursor in the On Click event
    • >Type “dt.” (You get auto-complete with all of your named macros in the dt macro group.)
    • Click the dropdown if you want to see the complete list of named macros.
    • Select the macro you want.

This really makes navigation much more manageable. 

Tim
by Steven Greenberg

Comments

# Vladimir Cvajniga said on January 14, 2008 10:20 PM:

I'd like to point you to my problem with A2002 - Text box (memo-field): division of words with diacritics: http://www.utteraccess.com/forums/showflat.php?Cat=&Board=83&Number=1565928&Zf=&Zw=&Zg=0&Zl=a&Main=1565928&Search=true&where=&Zu=122546&Zd=l&Zn=&Zt=28&Zs=&Zy=#Post1565928&Zp=

I've gone through KB hot-fixes for A2002 SP3 but non of them is a fix for my problem... which, of course, is very important. It seems to be a bug in Czech version of A2002 and it's critical for some of my applications.

Pls, help if you can!

TIA

# Zac Woodall said on January 15, 2008 11:55 AM:

Vladimir, have you tried this in newer versions of Access?  XP isn't being actively updated right now, so if you weren't able to find an existing update/KB, there is probably little hope of getting a fix (short of your corporation requesting a QFE).

# Stuart said on January 15, 2008 1:50 PM:

Hello,

Why Access 2007 memory usage in Win XP is not the same in Win VISTA?

Task manager says that the same Front-End allocates much more memory in Windows XP.

It's strange...

# Tim Getsch said on January 15, 2008 2:19 PM:

This is one of my favorite Access 2007 tricks.  I posted an example screen shot of one of the many places where we use this technique.

http://blog.checkoutmycards.com/2008/01/15/great-post-on-the-microsoft-access-team-blog/

# Zac Woodall said on January 15, 2008 3:01 PM:

Stuart: I'm not aware of any difference in the way we work with memory in Vista over XP.  This may well be because of the way Windows allocates the user mode heap.

How much of a delta are we talking about?

# Steve Schapel said on January 16, 2008 3:21 AM:

Steven and Tim,

Thank you very much for this very neat approach.  I like the leveraging of the hyperlink paradigm, so simple to implement with macros.  That "Screen Only" setting for the Display As Hyperlink property is a nice idea.

# Vladimir Cvajniga said on January 16, 2008 8:47 AM:

Zac: I have a report from one of my colleagues saying that A2003 seems to perform OK. The problem is that I desperately need to use A2002...

I wonder if other (international) versions of A2002 have the same problem, eg. French, Portuguese, Spanish, ...

# Zac Woodall said on January 16, 2008 7:33 PM:

Man, I'm sorry Vladimir.  I wish you had the option of using 2003 or 2007.  Sort of that, the only other potential way to get a fix would be to go through PSS.

# Vladimir Cvajniga said on January 17, 2008 3:05 AM:

Zac: Some of our users would like to run Access apps on Windows 98 (yes, Windows 98). A2003 and higher won't run on W98.

Not sure what PSS means. Product Support Services? How we could ask for PSS and how much would it cost?

# clintc said on January 17, 2008 8:14 AM:

Here is a link to information about the Microsoft Support Lifecycle http://support.microsoft.com/lifecycle/.

Mainstream support for Office XP expired June 11th, 2006. http://support.microsoft.com/gp/lifean21.

The policies indicate we provide security fixes but not product design or quick fix engineering requests (QFE).

If you do want to make an inquiry to support here is the URL that will get you started:

http://support.microsoft.com/oas/default.aspx?&c1=505&gprid=1266&

Good luck.

# Vladimir Cvajniga said on January 17, 2008 8:31 AM:

clintc: TYVM for the information. I appreciate your help.

A telephone "inquiry" would cost me at least 330 USD. WOW! That's expensive! Anyway, I'll keep thinking about it.

P.S. If there was a "technical" bug on my 10-years old car, I'm sure that Honda would repair it for free...

# Vladimir Cvajniga said on January 19, 2008 5:03 AM:

clintc:

WARNING!!! There's a report in one of Czech/Slovak database forums that A2007 perfoms same way as A2002.

http://forum.builder.cz/read.php?21,2400613:

Ja som to skusil v 2007 a vysledok je rovnaky. Vychadza mi, ze znaky s diakritikou berie ako riadiace, alebo nieco podobne. Je to pren koniec slova.

I'll try to translate:

I have tried it in 2007 and the result is the same. In my opinion, Access takes characters with diacritics as "control" characters, or something like that. It's the end of the word.

May the problem reside in operating system? I've been having problems with copying through clipboard since I have switched to Windows XP and started usind Access 2002. When I copy some text with diacritics in Access VBA code and paste it to Notepad characters with diacritics are messed up. Same on the opposite way, i.e. Notepad to Access. I was talking about the problem with Czech Microsoft tech-help but they say it's my computer. But I have reports from Netherlands that their Windows have similar problems.

Also, there's a BIG problem in A97: if someone writes down his/her notes in Windows XP's Notepad (or Word) and then copies the text to A97 memo-box (via clipboard) the diacritics are messed up as well.

IMHO, Microsoft internationalization is not done well and various products in various international versions have problems with diacritics.

# Tom Wickerath said on January 19, 2008 5:26 AM:

Hi Vladimir,

> Also, there's a BIG problem in A97: if someone

> writes down his/her notes in Windows XP's Notepad

> (or Word) and then copies the text to A97 memo-box

> (via clipboard) the diacritics are messed up as well.

I could be wrong, but I think one would need Unicode in order to support the use of diacritics. Unicode was introduced with JET 4.0 (Access 2000). It was not available in JET 3.5x (Access 97).

# Vladimir Cvajniga said on January 19, 2008 7:28 AM:

Tom: I think you are right. I miss backward compatibility... I mean Windows clipboard is not designed to do it. MS should concern on clipboard functions.

But, as I said, the problem remains in Windows XP. There's still a problem with copying through clipboard:

Access 2002 => Notepad

Notepad > Access 2002

A2002 should handle UNICODE... as well as Notepad. But there's a problem somethere in between, probably in Windows clipboard or maybe in Windows "intenationalization" core.

# Tom Wickerath said on January 20, 2008 5:15 PM:

Have you tried this test?

Excel or Word 2002 => Notepad

Notepad => Excel or Word 2002

or, perhaps this test?

NonMicrosoft App. => Notepad

Notepad => NonMicrosoft App.

# Vladimir Cvajniga said on January 20, 2008 7:22 PM:

Tom:

Excel (Word) -> Notepad: OK

Notepad -> Excel (Word): OK

Excel VBA (Word VBA) -> Notepad: not OK

Notepad -> Excel VBA (Word VBA): not OK

============================

NonMicrosoft App

Paint Shop Pro 7 (text entry) -> Notepad: OK

Notepad -> Paint Shop Pro 7 (text entry): OK

============================

Microsoft application is not fully compatible with MS Notepad (problem with VBA).

Non-Microsoft application is compatible with MS Notepad.

============================

We've tried those tricks with Microsoft CZ tech-help a while ago and they sent me some .REG files... which seemed to help. But, after some time, I have the same problem again. I must say that, in this case, Microsoft hot-line was very helpful and I really appreciate it.

I ran the .REG files only a few minutes ago again and tested the following:

A2002 VBA -> Notepad: OK

Notepad - A2002 VBA: not OK

It also fails when I copy & paste from Notepad to Access VBA Search window. I think it points to an Access issue but I'm quite unsure.

In my opinion, there may also be a problem in Microsoft Update but it's very difficult to track it down. I run Microsoft Update once a month... ;-)

But I also use non-MS apps (ZoneAlarm as a firewall, AVG antivirus, Spamihilator spam filter, ... and a few others).

P.S. Thank you very much for your time.

I could e-mail you those .REG files if necessary but I'm afraid you can't help. My e-mail address is chewingun1 at yahoo dot ca.

Tomorrow I'm going to call my colleague to test A2003 again since I'm not sure if he tested what I wanted... see my post with warning about same behavior in A2007.

I'm sorry for my poor English but I hope you've got the point.

# Vladimir Cvajniga said on January 20, 2008 7:33 PM:

Tom: I have also experienced incorrect behavior with copy&paste in A2002 itself when I copied an excerpt of VBA code with Czech diacritics from an older backup to a newer version of the same project! This was when I first called Microsoft CZ tech-help. I've tried to point them to a potential Microsoft Update issue but they didn't accept it... they just smiled.

New Comments to this post are disabled
Page view tracker