In Parts I and II, we set up our Userform and controls and built some special purpose procedures to handle the data and the CommandButtons. Now, we’ll create the Recordset that will fill our TextBoxes with useful information.

The Initialize event fires whenever a Userform opens. This is a good place to create the Recordset because we know it will be available whenever the form is showing.

Most of this procedure is setting up strings. Those pesky connection strings and sql statements are hard for me to make. The way that I do it is to create an external data table in Excel (Data>Get External Data) and then read the Connection and CommandText properties. Then I make any changes like removing the ODBC; from the connection string and replacing the database information with variables. Once I have the external data table set up, I go to the Immediate Window and type

You’ll get a couple of really long strings, but you have only to copy and paste them into your code. If you’re anal about seeing all your code like me, then you’ll want to break up the strings like I did.

If you’re following along at home, there are two things that you must do: Change sDbPath to point to the folder with Northwind.mdb in it; and set a reference to the ADO Library (Tools>References from the VBE). My References box looks like this

When the Userform is closed, the QueryClose event fires. We don’t need the Recordset if the form’s not open, so we release it back to the wild (and from our computers memory) in this event.

The hard part is over. In Part IV, we’ll see how to code the buttons to move between records. You’ll be amazed at how simple it is.