Type Mismatch 13!

[Bruce M. Thompson]

Dim dbCurrent As Database

Dim rsData As Recordset [...]
Set dbCurrent = CurrentDb
Set rsData = dbCurrent.OpenRecordset(strNewSQL) ' <--'''offending
code....

I would guess that you are using Access 2000, or later, and that you have
references to both the DAO and ADO object libraries. While a Database object
exists only in the DAO library (as of yet), a recordset object exists in both
libraries, but are not the same. Try disambiguating your recordset object by
preceding the type with the source library:

Dim rsData As DAO.Recordset

 

[Lee Taylor-Vaughan]

Trying to get the following code to work.

Can someone please take a look and explain why it wont work. (i prefer to
know why so that i can fix it myself.)

Thanks in advance

lee


code

'sql statements used to polpulate lstbox (initial sql w/out order by &
where)
Const cstrOldSQL1 = "SELECT tblActivity.ControlNumberID AS CNumber, "
Const cstrOldSQL2 = "tblActivity.DateDispatch AS [Disp Date], "
Const cstrOldSQL3 = "tblActivity.DispatchType AS Type,
tblActivity.TimeDispatch AS [Disp Time], "
Const cstrOldSQL4 = "tblActivity.IncidentLocationCity AS Location, "
Const cstrOldSQL5 = "[PatientLastName] & '" & ", ' & [patientfirstname] AS
[Pt Name] "
Const cstrOldSQL6 = "FROM tblActivity INNER JOIN tblPatients "
Const cstrOldSQL7 = "ON
tblActivity.ControlNumberID=tblPatients.ControlNumberID"
'concatenates above constants for form one...
Const cstrOldSQLa = cstrOldSQL1 & cstrOldSQL2 & cstrOldSQL3 & cstrOldSQL4 &
cstrOldSQL5 & cstrOldSQL6
Const cstrOldSQL = cstrOldSQLa & cstrOldSQL7
'default sql construct
Const cstrDefaultOrder = " ORDER BY tblActivity.datedispatch DESC ,
tblActivity.TimeDispatch;"
Const cstrDefaultSQL = cstrOldSQL & cstrDefaultOrder


Public Sub cmdSearchNow_Click()
On Error GoTo Err_cmdSearch_Click

Dim strNewSQL As String ' variable to hold the new SQL string for the
RecordSource
Dim dbCurrent As Database
Dim rsData As Recordset
Dim LngCount As Integer

Select Case optSearch
Case 1
If IsNull(Forms!frmDispatchMain!txtFind) Then ' Search by Patient Last
Name
MsgBox "Please enter a name.", , "Data Required"
Exit Sub
ElseIf Not IsNull(Me.txtFind) Then
strNewSQL = cstrOldSQL & " Where [Pt Name] Like '" &
Forms!frmDispatchMain![txtFind].Value & "*' Order by [Pt Name]"
End If

Case 2
If IsNull(txtFind) Then ' Search by control number
MsgBox "Please enter a Control Number (Last 6).", , "Data Required"
Exit Sub
ElseIf Not IsNull(txtFind) Then
strNewSQL = cstrOldSQL & " Where tblActivity.ControlNumberID Like '" &
Forms!frmDispatchMain![txtFind].Value & "*' Order by
tblActivity.ControlNumberID"
End If

Case 3

If IsNull(txtFind) Then ' Search by Date
MsgBox "Please enter a Date in mm/dd/yy format", , "Data Required"
Exit Sub
ElseIf Not IsNull(txtFind) Then
strNewSQL = cstrOldSQL & " Where tblActivity.datedispatch = #" &
Forms!frmDispatchMain![txtFind].Value & "# Order by
tblActivity.datedispatch,tblActivity.TimeDIspatch"
End If

End Select
' Open strNewSQL to find the record count. If there are no records for the
selections error 3021 is
' handled with a message box below.

Set dbCurrent = CurrentDb
Set rsData = dbCurrent.OpenRecordset(strNewSQL) ' <--'''offending
code....
With rsData
.MoveLast
.MoveFirst
Do While rsData.EOF = False
.MoveNext
Loop
LngCount = .RecordCount
End With
Forms![frmDispatchMain]![txtTotal].Value = LngCount

' Assign put the new SQL value into the recordsource
Forms!frmDispatchMain.lstDispMain = strNewSQL
Forms!frmDispatchMain.lstDispMain.Requery
Forms!frmDispatchMain.Refresh
Forms!frmDispatchMain.Repaint


Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
If Err.Number = 3021 Then
MsgBox "There are no Missions for your selection; please retry your
search...", , "Search"
strNewSQL = cstrDefaultSQL
Forms!frmDispatchMain.lstDispMain.RowSource = strNewSQL
Forms!frmDispatchMain.Requery
Else
MsgBox Err.Number & Err.Description
Resume Exit_cmdSearch_Click
End If
Resume Exit_cmdSearch_Click

Forms!frmDispatchMain.lstDispMain = strNewSQL
Forms!frmDispatchMain.Requery
Forms!frmDispatchMain.Repaint
End Sub

 

[Lee Taylor-Vaughan]

Dim dbCurrent As Database
Dim rsData As Recordset [...]
Set dbCurrent = CurrentDb
Set rsData = dbCurrent.OpenRecordset(strNewSQL) ' <--'''offending
code....

This section of code works fine in other sections of the project, but it
doesnt work when ever a select a value in the txtFind box and Click the
SearchNow command button (code already posted in initial post). I am
stumpted.

 

[gandalf]

shouldn't

Const cstrOldSQL5 = "[PatientLastName] & '" & ", ' &
[patientfirstname] AS
[Pt Name] "

read as

Const cstrOldSQL5 = "'[PatientLastName] & '" & ", ' &
[patientfirstname]' AS
[Pt Name] "

? (missing quotes)

-----Original Message-----

Dim dbCurrent As Database
Dim rsData As Recordset [...]
Set dbCurrent = CurrentDb
Set rsData = dbCurrent.OpenRecordset(strNewSQL) ' <- -'''offending
code....

I would guess that you are using Access 2000, or later, and that you have
references to both the DAO and ADO object libraries. While a Database object
exists only in the DAO library (as of yet), a recordset object exists in both
libraries, but are not the same. Try disambiguating your recordset object by
preceding the type with the source library:

Dim rsData As DAO.Recordset

--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at http://www.mvps.org/access)within the newsgroups so that all might benefit.<<


.