Type Mismatch

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I don't understand why I'm getting a "Type Mismatch" with this code.
The only value I'm using is the "ICNNO"
I'm trying to transfer data to a report with: select * from [q_LetterVals]
where [ICNNo] = " & "'" & txtICNNO & "';")

Suggestions???


Dim stDocName As String
Dim stLinkCriteria As String
Dim dbcurrent As Database
Dim rstemp As Recordset

stLinkCriteria = "[ICNNO]=" & "'" & txtICNNO & "'"

If IsNull(Me.txtICNNO) Or Me.txtICNNO = "" Then
MsgBox "Please enter an ICNNO number"
Exit Sub
End If


Set dbcurrent = CurrentDb
Set rstemp = CurrentDb.OpenRecordset("select * from [q_LetterVals] where
[ICNNo] = " & "'" & txtICNNO & "';")
stDocName = "rptThankYou"
If rstemp.RecordCount > 0 Then
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
Exit Sub
End If

If rstemp.RecordCount <= 0 Then
MsgBox "There is no record for this ICN Number. Please review and
try again"
txtICNNO.SetFocus
Exit Sub
End If
 
Just a minor syntax problem:
select * from [q_LetterVals] where [ICNNo] = " & "'" & txtICNNO & "';")
try
select * from [q_LetterVals] where [ICNNo] = '" & txtICNNO & "';"
 
In
Dan @BCBS said:
I don't understand why I'm getting a "Type Mismatch" with this code.
The only value I'm using is the "ICNNO"
I'm trying to transfer data to a report with: select * from
[q_LetterVals] where [ICNNo] = " & "'" & txtICNNO & "';")

Suggestions???


Dim stDocName As String
Dim stLinkCriteria As String
Dim dbcurrent As Database
Dim rstemp As Recordset

stLinkCriteria = "[ICNNO]=" & "'" & txtICNNO & "'"

If IsNull(Me.txtICNNO) Or Me.txtICNNO = "" Then
MsgBox "Please enter an ICNNO number"
Exit Sub
End If


Set dbcurrent = CurrentDb
Set rstemp = CurrentDb.OpenRecordset("select * from [q_LetterVals]
where [ICNNo] = " & "'" & txtICNNO & "';")
stDocName = "rptThankYou"
If rstemp.RecordCount > 0 Then
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
Exit Sub
End If

If rstemp.RecordCount <= 0 Then
MsgBox "There is no record for this ICN Number. Please review
and try again"
txtICNNO.SetFocus
Exit Sub
End If
Click to expand...

What version of Access are you using? Does it work if you declare the
recordset like this:

Dim rstemp As DAO.Recordset

?

In any case, you should close that recordset before you exit the
procedure.
 
Back
Top