DAO Recordset Problem

[Jean Stretton]

I saw Douglas Steele's response to another user's problem
about getting type mismatch errors when using the DAO
Recordset and thought it was the answer to mine. However
I have Set rs As DAO.Recordset and no reference to ADO in
my project. Its probably something much simpler - i.e.
user error in the code. Can anyone help?

This is the code:

Private Sub CmdExit_Click()
On Error GoTo Err_CmdExit_Click

Dim TheEnquiryNo As Integer
Dim TheEnquiryRel As Integer
Dim TheEnquirySuffix As String
Dim TheEnquiryVersionNo As Integer




TheEnquiryNo = EnquiryNo
TheEnquiryRel = EnquiryRel
TheEnquirySuffix = EnquirySuffix
'The form field referred to on next line is bound to an
Autonumber field in the underlying recordset

TheEnquiryVersionNo = Me!EnquiryVersionNo.Value



Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ws

Set db = CurrentDb

'The following line cause the type mismatch error

Set rs = db.OpenRecordset("SELECT * FROM tblEnquiryRel05
WHERE EnquiryVersionNo = '" & TheEnquiryVersionNo & "'",
dbOpenDynaset)

Set ws = Workspaces()




With rs
.MoveFirst
Do While rs.EOF = False

.Edit
!MachineNo = Me!MachineNo.Value
!ImpressionNo = Me!ImpressionNo.Value
!PartWeight = Me!PartWeight.Value
!ParisonWeight = Me!ParisonWeight.Value
!CycleTime = Me!CycleTime.Value
!CommentProjects = Me!CommentProjects.Value
!TranNo = Me!TranNo.Value
!CadFileName = Me!CadFileName.Value
!CadLevel = Me!CadLevel.Value
!SupplierNo2 = Me!SupplierNo2.Value
!SupplierNo3 = Me!SupplierNo3.Value
Loop

End With
rs.Close

Set db = Nothing
Set rs = Nothing

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmProjectsEntry03"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Forms!frmProjectsEntry03!EnquiryNo = TheEnquiryNo
Forms!frmProjectsEntry03!EnquiryRel = TheEnquiryRel
Forms!frmProjectsEntry03!EnquirySuffix =
TheEnquirySuffix
Forms!frmProjectsEntry03!EnquiryVersionNo =
TheEnquiryVersionNo

Forms!frmProjectsEntry03!CmdTooling.SetFocus
Forms!frmProjectsEntry03!CmdRunDetails.Enabled = False

DoCmd.Close acForm, "frmProjectsEntry05"

Exit_CmdExit_Click:
Exit Sub

Err_CmdExit_Click:
MsgBox Err.Description
Resume Exit_CmdExit_Click

End Sub

 

[Ken Snell]

Good point.... !

--
Ken Snell
<MS ACCESS MVP>

Don't think that's it, Ken, or else the error would be because of the line:

Set db = CurrentDb

Jean: Since EnquiryVersionNo is an AutoNumber or, in other words, a numeric
value, your SQL statement is incorrect (you've got quotes in it.)

Try

Set rs = db.OpenRecordset("SELECT * FROM tblEnquiryRel05
WHERE EnquiryVersionNo = " & TheEnquiryVersionNo ,
dbOpenDynaset)

As well, I'd advise using Long, rather than Integer, for your declaration of
at least TheEnquiryVersionNo. AutoNumbers are Longs, which means their
values can be anywhere from -2,147,483,648 to 2,147,483,647. Integers can
only be from -32,768 to 32,767.