Not a Member Yet,
Click here to Register

ID: 306
Viewed: 3465
Added: Aug 19, 2002
Snippet uploaded by: snippet
Written By: Unknown
Demo: Sorry, no demo

User Rated at: 0 Stars
Rate This:

Thank you for your vote. Please wait...

It appears you already voted for this snippet

It appears your vote value was empty

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!

Highlight all by clicking in box

Highlight All
Private Sub cmdFind_Click()

Dim db As Database
'This is the object that will hold the connection
'to our database

Dim rs As Recordset
'This is the object that will hold a set of
'records coming back from the database

Dim SQLString As String
'This is just to temporarily hold the SQL string

Set db = OpenDatabase("c:Microsoft Visual StudioNwind.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!")
Exit Sub
End If

txtTotalNumber.Text = rs.Fields("NoOfOrders")
'Simply throws the value in the 'NoOfOrders' field
'from the Recordset, direct into the txtTotalNumber
'text box

SQLString = "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

Set 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 statement

txtLastDate.Text = rs.Fields("LastOrderDate")
'Here, we're taking the information from the 'LastOrderDate'
'field and placing it in the txtLastDate text box

txtLastDate.Text = Format(txtLastDate.Text, "Long Date")
'Now we're just formatting to make it look pretty

'Close the Recordset

'Close the Database

End Sub;

No Comments to show

Please completely fill out the form below if you want to review this snippet. All reviews are subject to validation.

Replying to a Comment...

Adding your comment. Please wait...

Thanks for adding your comment!. After further review it will be added.

There was a problem adding your comment. Please try again.

Please complete all the fields in the form before sending.

© 2002 - 2024 All Rights Reserved. Conditions
Do NOT follow this link or you will be banned from the site!