Visual Basic Database Tutorial - Part 4
So your end users are merrily tapping away at the call centre, easily pulling up customer details with your supercool program. Suddenly, a terrifying scream echoes through the corridors. Move over Poirot, this aint no murder... its much more serious.
Enter stage - RUNTIME ERROR!
Try entering an incorrect customer ID into your program, such as KARLOS, and hitting the Find button. You should get this awful message:
Hit the End button and let's discuss this like the adults we're not.
Runtime errors occur when youve told Visual Basic to do something it really cant handle. In this case, its screaming: "No current record". But why?
In Visual Basic, each set of records has two special markers in it - known as the Beginning Of File (BOF) and the End Of File (EOF).
The BOF sits just before the first record and the EOF, just after the last record. In graphical terms, it looks a little like this:
OK, so Im no Picasso - but you get the idea. And at least Ive still got both my arms.
[Ed: Ears, Karl. Picasso cut off his EAR!]
If you remember, we set the Recordset object to hold the results of our SQL statement. Most of the time, this will contain details such as the number of orders. But when the customer ID cant be found in the orders table - meaning the customer is either non-existent or hasnt yet placed an order - an empty set of records is returned to the Recordset object.
In graphical terms, that empty set of records looks like this:
So when we say txtTotalNumber.Text = rs.Fields("NoOfOrders") - Visual Basic replies with "Whoah, hold on a minute. There's no current record for me to grab your 'NoOfOrders' field from!"
We can check to see whether there is a current record by examining the BOF and EOF properties of the Recordset. When youre sitting on an actual record, both the BOF and EOF properties are false. If we are at the beginning and end of a Recordset after we execute an SQL string (BOF=True and EOF=True), we know it didnt return any records.
So let's edit our Visual Basic code to reflect that. Add the bolded code behind your command button:
Private Sub cmdFind_Click()Dim db As Database' This is the object that will hold the connection' to our databaseDim rs As Recordset' This is the object that will hold a set of' records coming back from the databaseDim SQLString As String' This is just to temporarily hold the SQL stringSet db = OpenDatabase("c:\Microsoft Visual Studio\Nwind.mdb")' This activates the database object, telling it' to link to the Nwind.mdb database. Note that' you may have to change this path depending on' where Visual Basic has been installed on your PC.SQLString = "SELECT Orders.CustomerID, " & _ "Count(Orders.OrderID) " & _ "AS NoOfOrders From Orders " & _ "GROUP BY Orders.CustomerID " & _ "HAVING (((Orders.CustomerID)='" & txtCustID.Text & "'))"' This SQL statement was created in Access. It simply returns' the number of orders for a particular customer using the' 'Count' feature on the 'Total' line. If you'd like to use' Count, but are a little unsure about it - search Access help -' it's very simple!Set rs = db.OpenRecordset(SQLString)' This ties the recordset object with the database object.' You're telling it to set the recordset object to whatever' the "db.OpenRecordset" function returns. And that function' will return a set of records according to the SQL statement' you pass it.If rs.BOF = True And rs.EOF = True Then ' Obviously the customer cannot be found in the ' orders table, so let's tell the user - and close ' the recordset/database connections MsgBox ("Cannot find customer - " & _ "txtCustID.Text & " - " & _ "in the Orders table!") rs.Close db.Close Exit SubEnd IftxtTotalNumber.Text = rs.Fields("NoOfOrders")' Simply throws the value in the 'NoOfOrders' field' from the Recordset, direct into the txtTotalNumber' text boxSQLString = "SELECT Orders.CustomerID, " & _ "Last(Orders.OrderDate) " & _ "AS LastOrderDate From Orders " & _ "GROUP BY Orders.CustomerID " & _ "HAVING (((Orders.CustomerID)='" & txtCustID.Text & "'))"' We've already figured out the number of orders - so' this is the SQL statement that finds out the last order' dateSet rs = db.OpenRecordset(SQLString)' This is the second time we've seen this statement. Here,' it says the Recordset object to hold the records' from our new SQLString statementtxtLastDate.Text = rs.Fields("LastOrderDate")' Here, we're taking the information from the 'LastOrderDate'' field and placing it in the txtLastDate text boxtxtLastDate.Text = Format(txtLastDate.Text, "Long Date")' Now we're just formatting to make it look prettyrs.Close' Close the Recordsetdb.Close' Close the DatabaseEnd Sub
Page 6 of 8