Type Mismatch

[Guest]

Originally I asked a question w/ subject "Cancel Command"
My question has greatly changed so I'm sending this:

PLEASE Help me fix the TYPE in the below code:
The error I get is "TYPE MISMATCH"


Sub cmdDataUpdate_Click()
On Error GoTo Err_cmdDataUpdate_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim dbcurrent As Database
Dim rstemp As Recordset
stLinkCriteria = "[icnno]=" & "'" & InputBox("Please Enter the ICN
Number") & "'"

Set dbcurrent = CurrentDb
Set rstemp = CurrentDb.OpenRecordset("select * from tbltrackingdata
where " & stLinkCriteria & ";")

If rstemp.RecordCount <= 0 Then
MsgBox "There is no record for this ICN Number."
Exit Sub
End If


stDocName = "frmTrackingData"
DoCmd.OPENFORM stDocName, , , stLinkCriteria
Forms![frmTrackingData].ICNNO.Locked = True
Echo True

Exit_cmdDataUpdate_Click:
Exit Sub

Err_cmdDataUpdate_Click:
MsgBox Err.Description
Resume Exit_cmdDataUpdate_Click

End Sub

 

[Sandra Daigle]

Which line is causing the error? Is icnno a numeric field? If so you don't
need to wrap the search value in quotes.

stLinkCriteria = "[icnno]=" & InputBox("Please Enter the ICN Number")

A couple of other things, instead of opening a recordset you can test for
the presence of a record using the Dcount function. If you do want the
recordset then you need to close it and then destroy the reference to it.

Also, I am not fond of using "Exit Sub" to control logic (except for error
handling) since it makes the code difficult to read, follow and debug.
Instead, use a structured if clause to control the logic path.

One other thing, the Access Wizards insist on creating and using a variable
for the form name when the wizard creates a click event to open a form. You
don't really need this - it's just as easy to refer directly to a literal
string in the docmd.openform command. This isn't wrong - just a waste of
programming lines in my opinion. While there are times that it's beneficial
to use a variable, this isn't one of them.

if dcount("icnno",tbltrackingdata",stLinkCriteria)>0 then
DoCmd.OPENFORM "frmTrackingData", , , stLinkCriteria
Forms![frmTrackingData].ICNNO.Locked = True
else
MsgBox "There is no record for this ICN Number."
endif

 

[Douglas J Steele]

If Sandra's answer doesn't solve your problem, another possibility is that
you're getting the wrong Recordset object for rstemp.

Assuming you're using Access 2000 or newer, the normal result of the
statement

Dim rstemp As Recordset

is an ADO recordset. However, you're trying to use DAO, so you need to
ensure that you get a DAO recordset:

Dim rstemp As DAO.Recordset

The problem is that Recordset is an object in both the ADO and DAO models.
When you have references set to both models, Access chooses the first one it
finds, and ADO is usually higher in the list of references than DAO.