Browse by Tags

All Tags » VBA » Tips (RSS)
Saturday, October 04, 2008 2:58 PM

Referring to a control's label

rkc posted a snippet of code in a thread titled Access an attached label in VBA on how to refer to a control's label using some syntax I've never seen used in exactly that fashion. With ctlCurrentControl .Controls(0).BackColor = lngYellow End...
Posted by Tony | with no comments
Filed under: , ,
Thursday, October 02, 2008 9:04 PM

J Street's Access Relinker available for download

See J Street's Access Relinker by fellow MVP Armen Stein. Also see the Access Web's Relink Access tables from code and Allen Browne's Reconnect Attached tables on Start-up as well as my general approach on this topic for shrink wrap apps Relinking...
Posted by Tony | with no comments
Filed under: , ,
Tuesday, September 16, 2008 3:44 PM

Referencing one of three identical controls

The client has a very complex, unbound labour entry form. One requirement is to efficiently add over time and double time hours for the same person, job, activity code, etc. So the solution was to create three sets of controls that are identical except...
Posted by Tony | with no comments
Filed under: , ,
Wednesday, September 10, 2008 10:47 PM

Microsoft Access Report Printing Criteria Selection Form page updated

I've completely updated the page at Microsoft Access Report Printing Criteria Selection Form with the latest code that I currently use. That page was created in about 1996 or so and was among the first five or ten pages I ever created. So it was long...
Posted by Tony | with no comments
Filed under: , ,
Monday, August 25, 2008 7:37 PM

Referencing a control on a subform from the parent form in VBA

Sometimes I get confused when trying to figure out the exact syntax required in VBA code to reference a control on a subform from the parent form. For example Forms![Equipment]![EquipmentPartssbf].Form![cboepPartID] See Referencing a control on a subform...
Posted by Tony | 5 comment(s)
Filed under: , ,
Sunday, July 13, 2008 3:23 PM

Don't forget the small touches - Invoice number and Set date range to last month

I'm just finishing up adding customer invoicing functionality to my Fleet Management app. One bit of functionality a client requested was the ability to know if a customer service order had been invoiced separately by the branch office accounting...
Posted by Tony | with no comments
Filed under: , ,
Saturday, May 31, 2008 6:20 PM

File Compression using the OS and NTFS file system

Thanks to a question in the newsgroups I did some research and updated the Microsoft Access Compatible Compression Solutions page with the below File Compression using the OS and NTFS file system section (And I renamed the web site page from Microsoft...
Posted by Tony | 2 comment(s)
Filed under: , , ,
Saturday, May 03, 2008 3:02 PM

References

Paul asked "I sometimes get frustrated that Access does not always update references between versions...for example a calendar control or Outlook reference" 1) Use a calendar form or the API calls which is what the calendar control uses behind...
Posted by Tony | with no comments
Filed under: , ,
Monday, February 25, 2008 2:21 AM

Minor unexpected behavior in VBA DIR function

This is exceedingly minor. However the following example brought two different results. ?Dir("Q:\1 access\Fleet Mgmt\*.key") PD.keyzzzz ?Dir("Q:\1 access\Fleet Mgmt\pd.key") <nothing> Background. For my "shrink wrap"...
Posted by Tony | 2 comment(s)
Filed under: , ,
Sunday, February 24, 2008 7:17 PM

Fetching the reference description

I've always been irritated at how you can't get at the user friendly reference description as displayed in the Access VBA References screen. When you look at the properties of the reference object you can't see the user friendly name you see...
Posted by Tony | 5 comment(s)
Filed under: , ,
Wednesday, February 20, 2008 4:42 PM

Looking for Access constants?

Say you're creating fields on a table using VBA and the field collections. You're trying to figure out what the possible values are for the field types. And you've managed to figure out something like the following code. Dim dbsupdate As Database...
Posted by Tony | 4 comment(s)
Filed under: , ,
Monday, November 12, 2007 11:14 PM

Shifting fields left on a continuous form

I have a field on a continuous form that is only occasionally visible. Yet, when visible, I'd like it to be right next to another field. And I don't want to leave an ugly gap in the middle. See Shifting fields left on a continuous form in Microsoft...
Posted by Tony | 1 comment(s)
Filed under: , ,
Sunday, October 21, 2007 4:27 PM

Debugging intermittently failing VBA code

I had a problem where an API call would work when stepping through the VBA code but would intermittently fail crashing Access or give an "out of memory" error. (Out of memory is Access's default error message when it doesn't know what...
Posted by Tony | with no comments
Filed under: , ,
Saturday, August 04, 2007 2:15 PM

What I always keep forgetting about IsMissing

like using IsMissing when appropriate on the argument list of a subroutine or function. But I keep forgetting that I have to then declare that variable as a variant. Good: Sub SendQuerytoExcel(strQuery As String, Optional strXLSName As Variant, Optional...
Posted by Tony | 1 comment(s)
Filed under: , ,
Sunday, July 22, 2007 4:27 PM

goto

goto (I love the cartoon's disclaimer.) That said I do use goto's in one major situation. At the beginning of any routine to check conditions and immediately exit. For example if I want to update some rates has the service tech rate been entered...
Posted by Tony | with no comments
Filed under: , ,
Friday, May 25, 2007 4:55 PM

Mousewheel not working in the VBA editor?

I used to use either VBScroll or FreeWheel . But there is now an official Microsoft solution. Mouse wheel events do not work in the Visual Basic 6.0 IDE . I'd suggest staying away from an old version of the Intellipoint software.
Posted by Tony | with no comments
Filed under: ,
Sunday, May 20, 2007 11:22 AM

Resume in code after an error message

Folks So you're working in a module with error handling and you land up in the msgbox routine. How do you determine what line cause the error and how do you easily get back there? When at the message you hit Ctrl+Break and choose Debug you end up...
Posted by Tony | with no comments
Filed under: , ,
Tuesday, May 08, 2007 3:33 PM

Debugging VBA code and docmd.close

An interesting problem came up when working on a very complex time sheet data entry form. There are times when I want to close the form automatically. Turns out that when you've debugged code, and the focus is set to the VBA IDE, running Docmd.Close...
Posted by Tony | 5 comment(s)
Filed under: , ,