Navigate a Recordset With a Userform - Part III
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.
Private Sub UserForm_Initialize()
Dim sConn As String
Dim sSQL As String
Dim sDbPath As String
Dim sDbName As String
’store the path and name of the database
sDbPath = “C:Program FilesMicrosoft OfficeOfficeSamples”
sDbName = “Northwind”
’store the connection string
sConn = “DSN=MS Access Database;”
sConn = sConn & “DBQ=” & sDbPath & “” & sDbName & “.mdb;”
sConn = sConn & “DefaultDir=” & sDbPath & “;”
sConn = sConn & “DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;”
’store the SQL statement
sSQL = “SELECT Employees.EmployeeID, Employees.LastName, “
sSQL = sSQL & “Employees.FirstName, Employees.BirthDate, Employees.HireDate “
sSQL = sSQL & “FROM `” & sDbPath & “” & sDbName & “`.Employees Employees”
‘Create a new connection and recordset
Set mADOCon = New ADODB.Connection
Set mADORs = New ADODB.Recordset
mADORs.CursorLocation = adUseClient
‘Open the connection and recordset
mADOCon.Open sConn
mADORs.Open sSQL, mADOCon, adOpenDynamic
‘Go to the first record
mADORs.MoveFirst
‘Call special purpose subs
FillTextBoxes
DisableButtons “ButtonFirst”, “ButtonPrev”
End Sub
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
Print Sheet1.QueryTables(1).Connection
Print Sheet1.QueryTables(1).CommandText
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.
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
mADORs.Close
mADOCon.Close
Set mADORs = Nothing
Set mADOCon = Nothing
End Sub
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.
I can´t find Part IV…
And I´ve proplems with the sSQL statement, seems the connection between SELECT and FROM statements.
I´m especially concerned with this ` marker.
Any Ideas?
part IV is here
http://www.dailydoseofexcel.com/archives/2004/05/26/navigate-a-recordset-with-a-userform-part-iv/