DAO Recordset Problem

  • Thread starter Thread starter Jean Stretton
  • Start date Start date
J

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
 
Good point.... !

--
Ken Snell
<MS ACCESS MVP>

Douglas J. Steele said:
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.
Click to expand...
 
Hy!
I am working on my first DB project, and I have a question, that may be interesting for you also.
I created a form for date searching, for this I wont to make a funcion that generate SQL queries

But on Ron Macro it give me an eror
here is my script with coments



Sub Chek_Click()

Dim q, v1 As String
q = "SELECT * FROM Reclamatii WHERE NR = True"

If (C11) Then
v1 = ([Forms]![F_Cautare]![C11])
q = q + " AND DataPrimirii = " + "#" + v1 + "#" ' row 6
End If

'until here it is OK

Dim v2 As String
If (C2) Then ' here is the problem, and I dont know why
v2 = ([Forms]![F_Cautare]![C2]) ' if I declare v2 together with q and v1 it gives eror on row 6
q = q + " AND Strada = " + "'" + v2 + "'"
End If

Debug.Print q

Dim rst1, rst2 As Dao.Recordset
Set rst1 = CurrentDb.OpenRecordset(q)
Set rst2 = CurrentDb.OpenRecordset("ReclamatiiCautate")
Dim st1, st2 As String
Do Until rst1.EOF
rst2.AddNew

rst2!NR = rst1!NR
rst2!DataPrimirii = rst1!DataPrimirii
rst2!Strada = rst1!Strada
rst2!Bloc = rst1!Bloc
rst2!Sector = rst1!Sector
rst2!Telefon = rst1!Telefon
rst2!Nume = rst1!Nume
rst2!Prenume = rst1!Prenume
rst2!Operator = rst1!Operator
rst2!Efectuat = rst1!Efectuat
rst2!DataEfectuarii = rst1!DataEfectuarii
rst2!Remarca = rst1!Remarca

rst2.Update
rst1.MoveNext

Loop

End Sub



Could someone help me, plz!
thx!!!!!
 
Back
Top