Help Needed:Error 3219 Invalid Operation When running a query

J

Jim Evans

I have a query that gets Sales records by a date range. The Criteria for the
Date Range Calls 2 Functions :\Between FromDate() and ToDate().

The Module code for the Functions:


Public Function ToDate() As Date
'Written by Helen Feddema 9/14/98
'Last modified 7-4-2002

On Error GoTo ErrorHandler

'Pick up To date from Info table
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblInfo", dbOpenTable)
With rst
.MoveFirst
ToDate = Nz(![ToDate], "12/31/2004")
.Close
End With

ErrorHandlerExit:
Exit Function

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & _
Err.Description
Resume ErrorHandlerExit

End Function

Public Function FromDate() As Date
'Written by Helen Feddema 9/14/98
'Last modified 9-2-2003

On Error GoTo ErrorHandler

'Pick up from date from Info table
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblInfo", dbOpenTable)
With rst
.MoveFirst
FromDate = Nz(![FromDate], "1/1/2003")
.Close
End With

ErrorHandlerExit:
Exit Function


When try to preview the Report, I receive the Subject message twice and
then the No Records message.
Can anybody see the problem?

tia,

Jim Evans
 
D

Dirk Goldgar

Jim Evans said:
I have a query that gets Sales records by a date range. The Criteria
for the Date Range Calls 2 Functions :\Between FromDate() and
ToDate().

The Module code for the Functions:


Public Function ToDate() As Date
'Written by Helen Feddema 9/14/98
'Last modified 7-4-2002

On Error GoTo ErrorHandler

'Pick up To date from Info table
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblInfo", dbOpenTable)
With rst
.MoveFirst
ToDate = Nz(![ToDate], "12/31/2004")
.Close
End With

ErrorHandlerExit:
Exit Function

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & _
Err.Description
Resume ErrorHandlerExit

End Function

Public Function FromDate() As Date
'Written by Helen Feddema 9/14/98
'Last modified 9-2-2003

On Error GoTo ErrorHandler

'Pick up from date from Info table
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblInfo", dbOpenTable)
With rst
.MoveFirst
FromDate = Nz(![FromDate], "1/1/2003")
.Close
End With

ErrorHandlerExit:
Exit Function


When try to preview the Report, I receive the Subject message twice
and then the No Records message.
Can anybody see the problem?

tia,

Jim Evans
Click to expand...

Did you create the table, "tblInfo", from which these functions are
reading values, with the fields ToDate and FromDate? Did you add a
record to that table with the values you want? If the table doesn't
exist, you'll get an error on the OpenRecordset call; if the table is
empty, you'll get an error on the .MoveFirst call.

I don't see declarations for dbs and rst. Did you leave them out, or do
you not have Option Explicit set for the module? If they are declared
at the module level, you have another problem you haven't discovered
yet.
 
J

Jim Evans

dbs and rst are declared in the declarations section of the Module. The
Table and Fields exist and are populated.

As I work with this more, it seems that the query does not access the
Functions. Is this possible and what could cause this problem?

Jim Evans

Dirk Goldgar said:
Jim Evans said:
I have a query that gets Sales records by a date range. The Criteria
for the Date Range Calls 2 Functions :\Between FromDate() and
ToDate().

The Module code for the Functions:


Public Function ToDate() As Date
'Written by Helen Feddema 9/14/98
'Last modified 7-4-2002

On Error GoTo ErrorHandler

'Pick up To date from Info table
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblInfo", dbOpenTable)
With rst
.MoveFirst
ToDate = Nz(![ToDate], "12/31/2004")
.Close
End With

ErrorHandlerExit:
Exit Function

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & _
Err.Description
Resume ErrorHandlerExit

End Function

Public Function FromDate() As Date
'Written by Helen Feddema 9/14/98
'Last modified 9-2-2003

On Error GoTo ErrorHandler

'Pick up from date from Info table
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblInfo", dbOpenTable)
With rst
.MoveFirst
FromDate = Nz(![FromDate], "1/1/2003")
.Close
End With

ErrorHandlerExit:
Exit Function


When try to preview the Report, I receive the Subject message twice
and then the No Records message.
Can anybody see the problem?

tia,

Jim Evans
Click to expand...

Did you create the table, "tblInfo", from which these functions are
reading values, with the fields ToDate and FromDate? Did you add a
record to that table with the values you want? If the table doesn't
exist, you'll get an error on the OpenRecordset call; if the table is
empty, you'll get an error on the .MoveFirst call.

I don't see declarations for dbs and rst. Did you leave them out, or do
you not have Option Explicit set for the module? If they are declared
at the module level, you have another problem you haven't discovered
yet.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

Jim Evans said:
dbs and rst are declared in the declarations section of the Module.
Click to expand...

In that case, each of the functions should end with these additional
lines:

Set rst = Nothing
Set dbs = Nothing

That wouldn't be the source of this particular problem, though, so far
as I can tell.

What does the definition of rst look like, and what version of Access
are you running? If the definition is

Dim rst As Recordset

or

Private rst As Recordset

and you're running Access 2000 or 2002, then change it to

Dim rst As DAO.Recordset

If the compiler squawks at that, click Tools -> References... and add a
reference to the Microsoft DAO 3.6 Object Library.
The Table and Fields exist and are populated.

As I work with this more, it seems that the query does not access the
Functions. Is this possible and what could cause this problem?
Click to expand...

Is the module in question a standard module, not a form, report, or
class module?
 
J

Jim Evans

Dirk Goldgar said:
In that case, each of the functions should end with these additional
lines:

Set rst = Nothing
Set dbs = Nothing

That wouldn't be the source of this particular problem, though, so far
as I can tell.

What does the definition of rst look like, and what version of Access
are you running? If the definition is
Click to expand...

Delcarations:

Option Compare Database
Option Explicit

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Access 2003. db format: 2002 - 2003
Dim rst As Recordset

or

Private rst As Recordset

and you're running Access 2000 or 2002, then change it to

Dim rst As DAO.Recordset

If the compiler squawks at that, click Tools -> References... and add a
reference to the Microsoft DAO 3.6 Object Library.
Click to expand...

This reference is included.
Is the module in question a standard module, not a form, report, or
class module?
Click to expand...

I suppose it is a Standard. It is listed in the Modules section of the
Project Explorer. I am just becoming acquainted with Modules. I imported
this Module and the related Main Menu form from another db.

Funny thing is, this doesn't work in several dbs but it does work in the db
in which it was all originally created

Jim
 
D

Dirk Goldgar

Jim Evans said:
Delcarations:

Option Compare Database
Option Explicit

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Access 2003. db format: 2002 - 2003


This reference is included.


I suppose it is a Standard. It is listed in the Modules section of the
Project Explorer. I am just becoming acquainted with Modules. I
imported this Module and the related Main Menu form from another db.

Funny thing is, this doesn't work in several dbs but it does work in
the db in which it was all originally created

Jim
Click to expand...

Hmm, everything sounds okay, except for the unrelated problem I
mentioned. Do you get any error messages when you open the VVB Editor
and click Debug -> Compile ?
 
D

Dirk Goldgar

Note -- I'm going offline for the night now, and won't be back until
tomorrow night at the earliest.
 
D

Dirk Goldgar

Jim Evans said:
Dirk,

Thank you for the time you have taken to assist with this issue.
Click to expand...

I'm glad to help, Jim. Let me know when you have more information, or
if you have solved the problem.
 
J

Jim Evans

Dirk Goldgar said:
I'm glad to help, Jim. Let me know when you have more information, or
if you have solved the problem.
Click to expand...
Dirk,

I removed all objects related to the Main Menu and re-installed it and it is
working properly...have no idea why, but, can;t complain.

Jim
 
D

Dirk Goldgar

Jim Evans said:
Dirk,

I removed all objects related to the Main Menu and re-installed it
and it is working properly...have no idea why, but, can;t complain.
Click to expand...

Hmm. I don't know what reinstalling the Main Menu entails, but if you
fixed whatever was broken, more power to you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Form into access 2
Error 3061: Too few parameters. Expected 1 1
Update a table based on ComboBox Selection 2
Convert export calendar to Outlook to late binding 0
Too Few Parameters, 1 expected 1
Export with VBS with user defined field. 1
Access export to Excel template with merged cells 1
Help with Code and Working Days including Holidays 14

Top