Visual Basic Database Tutorial - Part 4, Page 5
Hold onto your frilly knickers folks, this is where we dive straight in at the deep end, with a tonne of real-world code.
You should find the below code fairly easy to walk through, with comments every step of the way. Add this behind your 'Find It!' command button and give it a test run!
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.txtTotalNumber.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
Wow, groovy or what! Try looking up the details for QUEDE, GALED, MAGAA or perhaps even ROMEY.
Page 5 of 8