Recordset Problem

[Andrea]

Let me say first of all I am a newbie and know little about actual
coding, mostly self taught. I have developed a database that I use to
track employee records. I use this database among other things to
record attendance and to track staff paid time off that is based on
their career appointment, i.e. 100% to 50%. Each employee accrues 4 to
8 hours of PTO on a monthly basis. I have a tblEmployee,tblAccrual and
a tblPP (Pay Period)that I want to use to calculate this accrual.
Employees may change from full time to part time or per diem (often
do). I need a way to match PTO accrual rate according to career
appointment in any given pay period. I am trying to develop code to
create recordsets to store the accrued PTO by pay period. When I run
the code which I have attached to the OnClick property of a button, I
get error message number 3061 "too few parameters. 1 expected". I
can't figure out what to do about it. I would appreciate any help you
could provide on this. Thanks in advance.
This is what I have so far:

Private Sub cmdAccrual_Click()
On Error GoTo Err

Dim db As database

Dim rstAccrual As DAO.Recordset
Dim rstPP As DAO.Recordset
Dim rstEmployee As DAO.Recordset

Set db = CurrentDb

Set rstAccrual = db.OpenRecordset("SELECT * FROM tblAccrual",
dbOpenDynaset)
Set rstPP = db.OpenRecordset("Select * from tblPP where
((tblPP.[current?])=False)", dbOpenDynaset)

Do Until rstPP.EOF

rstPP.MoveNext

Loop

Quit:
Exit Sub

Err:
Call Error
Resume Quit

End Sub

 

[John Nurick]

Hi Andrea,

At first sight the only way that code will give you error 3061 is if
there's no field in tblPP called [current?].

In any case it's almost always a bad idea to use punctuation or spaces
in field or table names. It means keeping track not only of the names
but of the square brackets around them. Most database engines simply
don't allow it.

Let me say first of all I am a newbie and know little about actual
coding, mostly self taught. I have developed a database that I use to
track employee records. I use this database among other things to
record attendance and to track staff paid time off that is based on
their career appointment, i.e. 100% to 50%. Each employee accrues 4 to
8 hours of PTO on a monthly basis. I have a tblEmployee,tblAccrual and
a tblPP (Pay Period)that I want to use to calculate this accrual.
Employees may change from full time to part time or per diem (often
do). I need a way to match PTO accrual rate according to career
appointment in any given pay period. I am trying to develop code to
create recordsets to store the accrued PTO by pay period. When I run
the code which I have attached to the OnClick property of a button, I
get error message number 3061 "too few parameters. 1 expected". I
can't figure out what to do about it. I would appreciate any help you
could provide on this. Thanks in advance.
This is what I have so far:

Private Sub cmdAccrual_Click()
On Error GoTo Err

Dim db As database

Dim rstAccrual As DAO.Recordset
Dim rstPP As DAO.Recordset
Dim rstEmployee As DAO.Recordset

Set db = CurrentDb

Set rstAccrual = db.OpenRecordset("SELECT * FROM tblAccrual",
dbOpenDynaset)
Set rstPP = db.OpenRecordset("Select * from tblPP where
((tblPP.[current?])=False)", dbOpenDynaset)

Do Until rstPP.EOF

rstPP.MoveNext

Loop

Quit:
Exit Sub

Err:
Call Error
Resume Quit

End Sub

 

[SteveS]

Let me say first of all I am a newbie and know little about actual
coding, mostly self taught. I have developed a database that I use to
track employee records. I use this database among other things to
record attendance and to track staff paid time off that is based on
their career appointment, i.e. 100% to 50%. Each employee accrues 4 to
8 hours of PTO on a monthly basis. I have a tblEmployee,tblAccrual and
a tblPP (Pay Period)that I want to use to calculate this accrual.
Employees may change from full time to part time or per diem (often
do). I need a way to match PTO accrual rate according to career
appointment in any given pay period. I am trying to develop code to
create recordsets to store the accrued PTO by pay period. When I run
the code which I have attached to the OnClick property of a button, I
get error message number 3061 "too few parameters. 1 expected". I
can't figure out what to do about it. I would appreciate any help you
could provide on this. Thanks in advance.
This is what I have so far:

Private Sub cmdAccrual_Click()
On Error GoTo Err

Dim db As database

Dim rstAccrual As DAO.Recordset
Dim rstPP As DAO.Recordset
Dim rstEmployee As DAO.Recordset

Set db = CurrentDb

Set rstAccrual = db.OpenRecordset("SELECT * FROM tblAccrual",
dbOpenDynaset)
Set rstPP = db.OpenRecordset("Select * from tblPP where
((tblPP.[current?])=False)", dbOpenDynaset)

Do Until rstPP.EOF

rstPP.MoveNext

Loop

Quit:
Exit Sub

Err:
Call Error
Resume Quit

End Sub

Andrea,

The error was caused by using the table name before the field,
ie tblPP.[current?].

Like John said, don't use punctuation or spaces. Also, you should start using a
naming convention. Instead of current?, I would use "ynIsCurrent". "yn"
indicates it is a boolean type and "IsCurrent" asks the same question as
"current?".

Here is your (modified) code:
watch for line wrap.....
(this should work.. ....

'********** Begin code ********
Private Sub cmdAccrual_Click()
On Error GoTo Err_cmdAccrual_Click

Dim db As Database

Dim rstAccrual As DAO.Recordset
Dim rstPP As DAO.Recordset
Dim rstEmployee As DAO.Recordset

Set db = CurrentDb

Set rstAccrual = db.OpenRecordset("SELECT * FROM tblAccrual", dbOpenDynaset)
Set rstPP = db.OpenRecordset("Select * from tblPP where [current?] =False",
dbOpenDynaset)
Do Until rstPP.EOF

rstPP.MoveNext

Loop

Exit_cmdAccrual_Click:
'clean up
rstAccrual.Close
Set rstAccrual = Nothing

rstPP.Close
Set rstPP = Nothing

Set db = Nothing
Exit Sub

Err_cmdAccrual_Click:
MsgBox Err.Description
Resume Exit_cmdAccrual_Click

End Sub
'********** End code **********

HTH

 

[Andrea]

Thanks to both John and Steve. I tried it with your suggestions and
the error message disappeared. I love it when I learn new stuff!

 

[John Nurick]

The error was caused by using the table name before the field,
ie tblPP.[current?].

Not so. It's not necessary to use the Table.Field syntax in a simple
query like this, but it doesn't do any harm. Both these queries work
just fine in Northwind:

SELECT * FROM Products WHERE Products.[Discontinued]=FALSE;
SELECT * FROM Products WHERE Discontinued=FALSE;

 

[SteveS]

The error was caused by using the table name before the field,
ie tblPP.[current?].

Not so. It's not necessary to use the Table.Field syntax in a simple
query like this, but it doesn't do any harm. Both these queries work
just fine in Northwind:

SELECT * FROM Products WHERE Products.[Discontinued]=FALSE;
SELECT * FROM Products WHERE Discontinued=FALSE;

????

Last night I pasted in Andrea's code and got the same error she did. The only
way I could get the code to complete was to remove the table name in the where
clause.

Today I paste in Andrea's code and get no error! It runs with no
modification...?? I must have had too much caffeine...


Thanks John..