Recordset errors

[Shelley]

I'm getting extremely frustrated... I have the following
on a field's afterupdate event. First, I kept getting
a 'type mismatch' run time error 13 at the 'Set myRS'
line. It won't matter if I get Access to accept my
recordset though, because then it doesn't recognize
the .edit method.

I've been using recordsets for years... this is pretty
simple code... what am I missing?


Private Sub BillPercent_AfterUpdate(Cancel As Integer)
Dim myrs As Recordset, myDB As Database, mySql As String
Set myDB = CurrentDb
mySql = "Select WorkOrder, WorkTask, PercentComplete from
Workorders where [WorkOrder] = '" & Me!WorkOrder & "' and
[worktask] = '" & Me!WorkTask & "'"
Set myrs = myDB.OpenRecordset(mySql, dbOpenDynaset)


With myrs
myrs.Edit
myrs!PercentComplete = Me!BillPercent
myrs.Update
myrs.Close
End With

End Sub

The table 'WorkOrders' has the following structure
Name Type Size
WorkOrder Text 50
WorkTask Text 2
ReleaseNum Text 50
JobNumber Text 50
Description Text 255
Location Text 255
SubRegion Text 50
Price Currency 8
EstMH Single 4
PercentComplete Single 4
BillType Text 15

 

[Dirk Goldgar]

I'm getting extremely frustrated... I have the following
on a field's afterupdate event. First, I kept getting
a 'type mismatch' run time error 13 at the 'Set myRS'
line. It won't matter if I get Access to accept my
recordset though, because then it doesn't recognize
the .edit method.

I've been using recordsets for years... this is pretty
simple code... what am I missing?


Private Sub BillPercent_AfterUpdate(Cancel As Integer)
Dim myrs As Recordset, myDB As Database, mySql As String
Set myDB = CurrentDb
mySql = "Select WorkOrder, WorkTask, PercentComplete from
Workorders where [WorkOrder] = '" & Me!WorkOrder & "' and
[worktask] = '" & Me!WorkTask & "'"
Set myrs = myDB.OpenRecordset(mySql, dbOpenDynaset)


With myrs
myrs.Edit
myrs!PercentComplete = Me!BillPercent
myrs.Update
myrs.Close
End With

End Sub

The table 'WorkOrders' has the following structure
Name Type Size
WorkOrder Text 50
WorkTask Text 2
ReleaseNum Text 50
JobNumber Text 50
Description Text 255
Location Text 255
SubRegion Text 50
Price Currency 8
EstMH Single 4
PercentComplete Single 4
BillType Text 15

It sounds like you're using Access 2000 or later, and either you don't
have a reference set to the DAO object library, or you have references
to both the DAO and ADO libraries, with ADO higher in the list of
references. Because ADO also contains a Recordset object, Access thinks
your "Dim myrs As Recordset" is declaring an ADO Recordset, which isn't
compatible with the DAO recordset returned by the DAO OpenRecordset
method, and which doesn't have an .Edit method.

Use the Tools -> References... dialog to make sure you have a reference
set to the Microsoft DAO 3.6 Object Library. Then, if you don't plan to
use the ADO library, remove the reference to it. If you want to keep
both references, declare your objects from these libraries by explicitly
qualifying them with the library ID, either DAO or ADODB:

Dim myrs As DAO.Recordset
Dim myDB As DAO.Database

I think it's a good idea to explicitly qualify the declarations, even if
you aren't keeping both references at the moment, just in case the ADO
reference gets added later.

 

[TC]

Just to butt in - there's no point in doing this:

With myrs
myrs.Edit
myrs!PercentComplete = Me!BillPercent
myrs.Update
myrs.Close
End With

The with/end with has no effect or benefit, if you are going to use the
recordset name explicitly on the included lines.

To benefit from the with/end with, do this:

With myrs
..Edit
!PercentComplete = Me!BillPercent
..Update
..Close
End With

or preferably:

With myrs
.Edit
!PercentComplete = Me!BillPercent
.Update
.Close
End With

HTH,
TC

 

[Dirk Goldgar]

Just to butt in - there's no point in doing this:

With myrs
myrs.Edit
myrs!PercentComplete = Me!BillPercent
myrs.Update
myrs.Close
End With

The with/end with has no effect or benefit, if you are going to use
the recordset name explicitly on the included lines.

With myrs
.Edit
!PercentComplete = Me!BillPercent
.Update
.Close
End With

Good point, TC.