VBA for Access

VBA for Access
Had I Only Known

by Margaret Magnus

My Web Site
Margo's Magical Letter Page

I was assigned a project using VBA for Access. Before I started the project, I did have experience with normal programming languages like C and Pascal and with scripting languages in, for example, Filemaker. The purpose of this document is to try to communicate what simple things hung me up for a long time, but which I had a hard time looking up, because I didn't know what questions to ask.

I am not super expert in VBA. No doubt there are others who have better solutions and more insight on these issues. If so, don't hesitate to contact me.

Basic VBA Facts

Visual Basic for Applications is a full fledged, interpreted, object oriented language for Window operating system. You can use Visual Basic to program anything you like. It is also the language used to program Windows Office applications in order to automate them. You probably don't want to program Word much in this manner, but you might find Visual Basic helpful in programming Excel or (especially) Access.

Documentation

You need to distinguish between the structures which are part of the Visual Basic language proper (and therefore universal), like built-in variable types (Boolean, Integer, String), control flow operations (Do-Loop, For, If-Then, etc.), vs. the constructs which are imposed on top of VB for a particular application (and therefore only relevant for, in this case, Access). I had the book VB and VBA in a nutshell. This book only covers the former. I couldn't start programming in Access with that alone. If you want to know how to use VBA for Access, you need references which specifically address VB as it applies to Access. After some searching around and buying a couple books I'll never use, I ended up with Alison Balter's Access 2002 Desktop Development as an intermediate text. (Make sure you have the right title -- Balter has two Access books that look identical -- one concerns networking stuff). Before I attempted VBA, I had read through the Access Help files and pretty much learned everything that isn't VBA. Balter's book covers all of that as well, but in a more accessible manner than the Help files. I really don't recommend trying to learn Access VBA with the Help files. I also found Getz, Litwin and Gilbert's Access 2000: Volume 1 helpful... It covers things that Balter doesn't, and sometime simply proposes a little bit different solutions to specific problems. However, I didn't know enough to read the Getz book to begin with.

Other Bits of Advice

Because these and other books are quite good at covering the topic, I'm not going to attempt to repeat it all here. My only thought was to provide my personal notes on stuff that I spent some time figuring out, that I didn't readily happen on in the texts (though for the most part it is there somewhere) -- the sort of thing that even if you know another programming language, you might not necessarily know about VBA for Access. For the solutions to most of these problems, I went to the Internet, and didn't really find it. This is an attempt to get you up and functional as quickly as possible with as little effort as possible.

Arguments: When you call a Subroutine (which doesn't return a value), you may not use parentheses for the argument list UNLESS you only pass one parameter, in which case, the parens are optional. If you call a Function (which does return a value), you may not use parentheses, but you delimit the parameters by commas. If you do use parentheses for a Subroutine, you will noe get a compiler error, but a runtime error, which does not mention the word 'parentheses' in it. However, when you DEFINE either, you use parnetheses. (Don't you just love Microsoft?)

ADO vs. DAO And Recordsets: Underlying every form is a Recordset, which conceptually is like a table containing only the records which have been selected for that form. Forms!Form_Name.RecordSource is a thing of type Recordset... You can delcare a Recordset and set it equal to a SQL query, but it's not that simple.

DAO is the older system. ADO (A stands for ActiveX) is the newer running on Access and Windows 2000, and it's the only one I've tried to use, so if your system uses DAO, you must turn elsewhere for advice on that point -- but some of this page may help anyway, so read on.

You often need, I find, to deal with Recordsets, because, after all, the whole point of working with a database is to find data and rearrange it. But I find it kind of a pain in ADO. You can't just declare something as a Recordset. This doesn't work:

'THIS DOESN'T WORK
Dim rs As Recordset
'Or Dim rs As ADODB.Recordset
rs = Forms!Some_Form.RecordSource
'Or this -- doesn't work
rs = SQL_stmt
'Or Open like this -- doesn't work
rs.Open SQL_stmt
'Or Open like this -- doesn't work, because you didn't instantiate, and there's a type mismatch -- you need an ADODB.Recordset
rs.Open SQL_stmt, CurrentProject.Connection
rs.MoveFirst

No. In Balter's book, how to manipulate ADODB.Recordsets quite reasonably starts on page 583, even though dealing with Recordsets is almost the first thing you need to do. The discussion starts so late in the book, because you really do need to know all the preceding stuff to be able to use them. So to define and use a Recordset, you have to:

'Declare the right kind of Recordset for your computer
'For Access 2000, use
Dim rs As ADODB.Recordset
'Instantiate the Recordset -- provide memory for it on the heap using New
'This means that you have to remember to manually release that memory when you're don with the Recordset, or you'll get a memory leak
Set rs = New ADODB.Recordset
'Open a Connection to the Recordset
rs.Open SQL_stmt_Or_Query_Name, CurrentProject.Connection
'Now finally you can call the Recordset commands
rs.MoveFirst
some_int = rs.RecordCount
'etc.
'Then you have to remember, like I said, to close down the connection
rs.Close
Set rs = Nothing

A Way Out

However, for most purposes, I find I can avoid all this. A good way to do things is:

1. To create a simple form using the Wizard for the table you want to manipulate. This has the advantage that if you have several tables with the same fields, you can use the same form (critically with the same name) to deal with all those different tables.

2. Make sure the Name of the fields you want to address is set to something you recognize, not for the purposes of manipulating the Recordset, but for addressing the form itself. (Right click on the control for that field, go to Other, and give it some name you like -- I usually give it the same name as the name of the field.)

3. Go to the module for that form (very important) -- you can't manipulate control A in form B from form C's module.

4. Now you can get to the underlying Recordset for this form like this:

Me.RecordsetClone.fieldname = 43

NOTE:

This won't work for everything. For example, rs.Find() doesn't work for Me.Recordset or Me.RecordsetClone. To do a Find on a recordset, you have to create one and instantiate it.

When Possible, Use SQL: You can in principle go to a form and update a table someplace by looping through the records in the form. But I find it's much easier and more reliable when possible to simply go to the underlying table, and run a SQL query on it.

Changing Data - UPDATE, APPEND, DELETE, etc.:
You can create and run UPDATE and APPEND queries (which change data) like this:

Dim SQL_query As String
'Don't forget the quotes around strings
SQL_query = "UPDATE some_table SET field1 = 'bla' WHERE field2 = 'shashasha'"
DoCmd.RunSQL SQL_query

Or if there are variables involved:

'Don't forget the spaces
SQL_query = "UPDATE " & table_name
SQL_query = SQL_query & " SET field1 = "
SQL_query = SQL_query & bla_variable

I didn't have much luck putting lots of &'s in a row.

Finding Data - SELECT:

When you need to SELECT data, you need to store it someplace, and the place to store it is in a Recordset. But like I said, Recordsets are complicated. So, I recommend that when you need to SELECT data from a table, create a form for that table.

1. Then you can get at a subset of the table (or any other table with compatible fields) like this:
SQL_query = "SELECT * FROM table_name WHERE fieldname = 23748"
'This changes the records underlying the form
Me.RecordSource = SQL_query
'Then
Me.RecordsetClone.MoveFirst
Me.RecordsetClone.fieldname = 600

Communicating Between Forms: From within the module for FormC, you can to a limited extent address FormB. You can, for example, say:

Forms!FormB.fieldname.SetFocus

But what you can do this way is very limited. In particular, any DoCmd command, like FindRecord can't be done from another space. So if you want to, for example, determine from FormC whether a particular record in FormB exists, you have to write the DoCmd.FindRecord statement in FormB's module, and include that in a Public Function, like this:

FormB module:

Public Sub FindRecord(some_criterion As String) As Boolean

DoCmd.FindRecord some_criterion

End Sub

Then to call this function, you do:

FormC Module

Form_FormB.FindRecord(the_data)

Notice that you can't get at a form's public functions like this:
'DOESN'T WORK
Forms!FormB.ExistsRecord(the_data)

You have to use the name of the module which contains the function, not the form to which the module is applied.

Nulls and Empty: When in doubt, put an Nz() around function calls and what-not that may return or contain a Null or empty. For example, I got an error for this statement:

inf_name = Me.RecordsetClone.inflection_name

because it couldn't process it if 'inflection_name' happened to be empty. So I changed the line to:

inf_name = Nz(Me.RecordsetClone.inflection_name)

and it was happy.

Subforms: How do you get at a control inside a subform???

'DOESN'T WORK
the_subform_name.Refresh = 43
'DOESN'T WORK
Me.the_subform_name.Refresh = 43
Me.the_subform_name.RecordsetClone.MoveNext
'DOESN'T ALWAYS WORK
Me.the_subform_name.Controls.control_name = 43
'SEEMS TO WORK
the_data = Me.the_subform_name.Controls.Item("control_name").Value
'OFTEN WORKS
Me.the_subform_name.Form.Refresh
Me.the_subform_name.Form.RecordsetClone.MoveNext

You can't just do this:

Forms!the_subform_name.Form

because you have to address the subform from within the main form, otherwise, the 'link-parent', 'link-child' thing isn't active. As best I can tell, the Form keyword allows you to treat the subform as an independent form. If you don't use the Form keyword, it treats the subform as a control in the main form and only gives you the control properties to play with. The Form keyword is a window into the subforms form properties. But I'm still learning. Again, to avoid troubles, try to do things with SQL on the underlying Recordset rather than through the subform Controls.

My Web Site
Margo's Magical Letter Page

VBA for Access Had I Only Known

VBA for Access
Had I Only Known