Type Mismatch 1

[LittleRedHat]

I'm using exactly the same coding that has worked in other Access databases. The search and reset work perfectly, except that a "type mismatch" error is displayed on the search. Close that and the required/searched record is already displayed. The debugger highlights Set rst = Me.RecordsetClone. The RecordSource and type are correct.

Private Sub BtnFindByName_Click()
Dim FindByNameSQL As String
If IsNull(Me.FirstCriteria) Then
MsgBox "Please enter first name to search", , "Brown's"
Me.FirstCriteria.SetFocus
Exit Sub
End If

If IsNull(Me.LastCriteria) Then
MsgBox "Please enter last name to search", , "Brown's"
Me.LastCriteria.SetFocus
Exit Sub
End If

FindByNameSQL = "SELECT * FROM SUPPLIER WHERE First_Name LIKE "
FindByNameSQL = FindByNameSQL + "'" + Me.FirstCriteria + "*'"
FindByNameSQL = FindByNameSQL + " AND Last_Name LIKE"
FindByNameSQL = FindByNameSQL + "'" + Me.LastCriteria + "*'"
Me.RecordSource = FindByNameSQL

Dim rst As Recordset
Set rst = Me.RecordsetClone

If rst.RecordCount = 0 Then
MsgBox "No records matching the criteria", vbExclamation, "Brown's - supplier search"
Else
rst.MoveLast
MsgBox rst.RecordCount & " records found", vbInformation, "Brown's - supplier search"
End If

End Sub

Private Sub BtnResetFormRecordSource_Click()
Me.RecordSource = "Select * FROM SUPPLIER"
Me.LastCriteria = Null
Me.FirstCriteria = Null
Me.FirstCriteria.SetFocus

End Sub

Please has anyone any clues as to why the "type mismatch" message and/or how to avoid it?

WTA

LRH

 

[FancyPrairie]

Access 2000 defaults to ADO rather than DAO. Therefore, the syntax for defining recordset is different. However, you can still use DAO, but your Dim statement should read:

Dim rst as DAO.Recordset

Also, you will need to set a Reference to "Microsoft DAO 3.6 Object Library".

I believe this will solve your problem.

 

[LittleRedHat]

Many thanks. Message no longer received.

The thought never even crossed my mind that it might be a '97 to 2000 issue ... as most of my problems are self-inflicted whoopsies I was too busy trying to find what I'd done wrong! s-)

LRH