Defining and Navigating recordsets in VBA

[RBohannon]

I'm using Access2000. I have one table with employee salary
information. I need to calculate the bi-weekly gross pay (BWG) for
each employee. The BWG is slightly different for leap years. I want
to be able to update all employees' BWG with one mouse click. I've
written a function in a module to do this, but I'm getting an error
"type mismatch" and the line
Set rs = db.OpenRecordset("tblEmployees")
is highlighted.

Below is my entire code. I'm trying to follow instructions from a
book, and I don't know why this isn't working correctly.

Any help (or suggestions of a better way to do this) is appreciated.

Public Function BWGUpdate()
' calcualte and display bi-weekly gross

Dim intYear As Integer ' current year
Dim db As Database
Dim rs As Recordset
Dim fldSal As Field ' salary
Dim fldBWG As Field ' bi-weekly gross

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblEmployees")

' get current year
intYear = Val(Right(Date, 4))

rs.MoveFirst
Do While Not rs.EOF
'check for leap year
If blnLeapYear(intYear) Then
rs(fldBWG) = rs(fldSal) * 0.038251
Else
rs(fldBWG) = rs(fldSal) * 0.038356
End If

rs.Update
rs.MoveNext
Loop

rs.Close

End Function

Public Function blnLeapYear(intYear As Integer) As Boolean
' check if current year is a leap year
' if 2/29/intYear is a valid date, we are in a leap year
blnLeapYear = IsDate("2/29/" & intYear)
End Function

[Wayne Morgan]

Access 2000 and newer uses ADO by default instead of DAO. Some of the
component names, such as Recordset, are the same. The best way to handle
this problem is to explicitly specify which you want to use.

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

If this causes a compile error, in the code window go to Tools|References
and make sure there is a check next to Microsoft DAO 3.6 Object Library.
--
Wayne Morgan
MS Access MVP
"RBohannon" <ra*****@hotmail.com> wrote in message
news:ad*************************@posting.google.co m...

I'm using Access2000. I have one table with employee salary
information. I need to calculate the bi-weekly gross pay (BWG) for
each employee. The BWG is slightly different for leap years. I want
to be able to update all employees' BWG with one mouse click. I've
written a function in a module to do this, but I'm getting an error
"type mismatch" and the line
Set rs = db.OpenRecordset("tblEmployees")
is highlighted.

Below is my entire code. I'm trying to follow instructions from a
book, and I don't know why this isn't working correctly.

Any help (or suggestions of a better way to do this) is appreciated.

Public Function BWGUpdate()
' calcualte and display bi-weekly gross

Dim intYear As Integer ' current year
Dim db As Database
Dim rs As Recordset
Dim fldSal As Field ' salary
Dim fldBWG As Field ' bi-weekly gross

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblEmployees")

' get current year
intYear = Val(Right(Date, 4))

rs.MoveFirst
Do While Not rs.EOF
'check for leap year
If blnLeapYear(intYear) Then
rs(fldBWG) = rs(fldSal) * 0.038251
Else
rs(fldBWG) = rs(fldSal) * 0.038356
End If

rs.Update
rs.MoveNext
Loop

rs.Close

End Function

Public Function blnLeapYear(intYear As Integer) As Boolean
' check if current year is a leap year
' if 2/29/intYear is a valid date, we are in a leap year
blnLeapYear = IsDate("2/29/" & intYear)
End Function

[Randy A. Bohannon]

Thanks for the help.

Randy