Browse by Tags

All Tags » Tips » VBA (RSS)
Mon, Oct 5 2009 5:09

Is Nothing

I’m working on some largish updates to the Auto FE Updater and I wanted to ensure that the new routine which logs errors would properly handle the situation if there were errors in the error logging routine.  A great deal of searching a dimly remembered...
Posted by Tony | 1 comment(s)
Filed under: , ,
Wed, Sep 23 2009 15:32

InStrRev

I’ll be darned.  I had no idea InStrRev existed in VBA code.  It is new in Access 2000 so it’s been around for nine years.  I had to build this function myself in A2.0 or A97 to get the file name from a string which had the path and file...
Posted by Tony | 1 comment(s)
Filed under: , ,
Tue, Sep 22 2009 22:32

Queries and the 3061 "Too few parameters. Expected 1." message

Your query is working just fine referencing a form to select some records: but when you run it in code you get the dreaded 3061 "Too few parameters. Expected 1." message. One option is to evaluate the Forms![Form name]![Field Name] expression...
Posted by Tony | 2 comment(s)
Filed under: , ,
Thu, May 21 2009 20:31

Redimming an array 1,000,000 times takes 4.63 seconds

The following question was asked at StackedOverflow. How do you redimension an array in VBA? . One response was some detailed code showing how to redim an array efficiently by doubling the number of elements. This took 0.41 seconds for a million records...
Posted by Tony | with no comments
Filed under: , ,
Sat, Apr 25 2009 15:32

Overflow error 6

A newsgroup poster had the following very standard lines of code strSQL = "SELECT Mytable.Field1, Mytable.Field2, Mytable.Field3 FROM Mytable" SET rs = Currentdb.OpenRecordset(strSQL,dbOpenDynaset) The error was Overflow error 6. He then replied...
Posted by Tony | 2 comment(s)
Filed under: , ,
Mon, Mar 2 2009 20:34

Hiding 70 controls based on a value

At the blog entry Getting a list of control names on a form Nate posted the following comment: Thanks for the great tip! It doesn't seem to include labels and command buttons, but it is still a great help. I have a form where I am hiding 70 controls...
Posted by Tony | with no comments
Filed under: , ,
Sun, Feb 22 2009 23:32

Using record selector on a form to select multiple records

A fellow Access MVP asked if there was a way of using the record selector to select multiple records and perform an action on them. Tom Wickerath, also a fellow Access MVP, responded with How to enumerate selected form records in Access 2003 and in Access...
Posted by Tony | 2 comment(s)
Filed under: , ,
Sun, Dec 28 2008 22:56

Why Do So Many Programmers Use One Character Index Variables?

Why Do So Many Programmers Use One Character Index Variables has a very good point for those of us of a certain age accompanied by greying hair, bifocals and ... And yes I did learn Fortran as a teenager at the local college. Although it was actually...
Posted by Tony | 4 comment(s)
Filed under: ,
Fri, Dec 26 2008 22:53

A very interesting solution for Code 39 barcode

Jim at a client found the following a very interesting solution for 3 of 9 bar code which was done as VBA code by James Mercantile. Barcode Generator for Code 3 of 9, Code 39, and Mil-spec Logmars. The VBA code reads the size of the control and creates...
Posted by Tony | 2 comment(s)
Filed under: , ,
Sun, Nov 30 2008 16:18

Standard NotInList VBA code

I finally got around to creating a Using NotInList in a combo box page at my website. I keep forgetting to make up same sample code and re use it as I don't care for the MS sample code in the Access help. A bit too verbose and it doesn't show...
Posted by Tony | with no comments
Filed under: , ,
Sun, Nov 30 2008 15:23

Using pictures on a form and report

The following question was asked in the comp.databases.ms-access newsgroup. I'd like to add a picture in a report's detail section where the path to the picture is from a table. Incredibly easy. The same code works on a form as well. Using the...
Posted by Tony | with no comments
Filed under: , , ,
Wed, Nov 26 2008 14:37

My muddiness when it comes to OpenArgs

I was getting an syntax error message when I clicked on a New button on the Equipment Quick Finder form . The equipment form in the Granite Fleet Manager executes a reasonable amount of code on startup. I had added that New button to the Quick Finder...
Posted by Tony | 3 comment(s)
Filed under: , , ,
Wed, Nov 19 2008 3:41

I just love the Access Developers Handbook

I recently added the ability to view unit photo's on the main menu of the Granite Fleet Manager . However if the user doesn't have photos or doesn't want to view them on the main menu then I wanted the form to be centered. I figured that the...
Posted by Tony | 3 comment(s)
Filed under: , , ,
Tue, Nov 18 2008 1:08

Watch out for Null OpenArgs

Well, I gave the problem away with the subject line. I had the following code which I thought was working quite nicely. If Left(Me.OpenArgs, 3) <> "New" Then But then I suddenly noticed when viewing a screen that a few fields were empty...
Posted by Tony | 1 comment(s)
Filed under: , ,
Fri, Nov 7 2008 20:09

Adding a record count to a tab caption on a tab control part 2

In my previous blog entry on this topic I stated "I also didn't bother putting any logic in to update the count of notes as the user was entering them. Hmm, for the sake of neatness and tidiness though I should." So I decided I'd better...
Posted by Tony | with no comments
Filed under: , ,
Tue, Nov 4 2008 23:10

Adding a record count to a tab caption on a tab control

I decided that it would be useful for someone looking at a particular part in the Granite Fleet Manager to know if there were any notes associated with that part. The idea being that there seldom would be any such and the mechanic wouldn't have any...
Posted by Tony | 1 comment(s)
Filed under: , , ,
Sat, Oct 4 2008 14:58

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 | 1 comment(s)
Filed under: , , ,
Thu, Oct 2 2008 21:04

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: , ,
Tue, Sep 16 2008 15:44

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: , ,
Wed, Sep 10 2008 22:47

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: , , ,
More Posts Next page »