Type MisMatch on Set rs = db.OpenRecordset(SQLString)

[Troy Mac]

Why am I getting a Type Mismatch error on this code on the first instance of Set rs = db.OpenRecordset(SQLString)?

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:\Program Files\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 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
'date

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

rs.Close
'Close the Recordset

db.Close
'Close the Database

End Sub

[barrk]

check the VB General post for answer.

[HunterMcCray]

Originally posted by Troy Mac
Why am I getting a Type Mismatch error on this code on the first instance of Set rs = db.OpenRecordset(SQLString)?

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:\Program Files\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 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
'date

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

rs.Close
'Close the Recordset

db.Close
'Close the Database

End Sub

This may sound silly, but is your CustId field in your databse a number field or a text field? If it is a number field then your SQL statement will generate a type mismatch error.

Hope it helps,

Hunter

[Troy Mac]

Thanks Hunter it is a Text field thats the crazy thing! I just can't figure out why it is erroring?

[HunterMcCray]

Originally posted by Troy Mac
Thanks Hunter it is a Text field thats the crazy thing! I just can't figure out why it is erroring?

Try replacing the "=" with "LIKE". I know that "=" should work, but it is easy enough to try.

Hunter

[Troy Mac]

Hunter, I found out thanks to Lauren from this site that I needed to qualify the database and recordsource like this

dim db as DAO.Database
dim rs as DAO.Recordset

and if I am using ADO just replace the DAO with ADO

This is because I am using VB6.0 Thanks anyway

Troy