UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Error 3001: Invalid Argument, Office 2007    
 
   
revans611
post Mar 8 2010, 02:18 PM
Post#1



Posts: 385
Joined: 12-November 07
From: Texas


I am getting a perplexing error message when I try to open recordset using VBA SQL. My code is as follows:
div class='codetop'>CODE
Function NonWorkingHoursElapsedTime(startDate As Date, StartTime As Date, completedDate As Date, completedTime As Date) As String
Dim rst As DAO.Recordset
On Error GoTo errHandler
strSQL = "SELECT * FROM Login WHERE LoginDate >=#" & startDate & "#"
debug.print strSQL
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

The debug.print function prints numerous lines for the sql that look like the following:
SELECT * FROM Login WHERE LoginDate >=#3/5/2010#
SELECT * FROM Login WHERE LoginDate >=#3/5/2010#
SELECT * FROM Login WHERE LoginDate >=#3/5/2010#
SELECT * FROM Login WHERE LoginDate >=#3/5/2010#
SELECT * FROM Login WHERE LoginDate >=#3/5/2010#
SELECT * FROM Login WHERE LoginDate >=#3/5/2010#
SELECT * FROM Login WHERE LoginDate >=#3/5/2010#
..... repeated an additional 123 times
Then, when the "set rst =" line is reached, I get the following message: 3001 Invalid Argument
All of the dates that are passed to the function are in Short Date format as is the LoginDate field from the Login table.
There are only 3 records in the Login table.
The cause must be something simple, but it has me stumped.
Go to the top of the page
 
SOS_UA
post Mar 8 2010, 02:20 PM
Post#2



Posts: 405
Joined: 26-February 10



Try adding an extra comma to this line:
et rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
like:
Set rst = CurrentDb.OpenRecordset(strSQL, , dbOpenSnapshot)
Go to the top of the page
 
revans611
post Mar 8 2010, 02:24 PM
Post#3



Posts: 385
Joined: 12-November 07
From: Texas


Now I get a new message: Cannot open any more databases.
Go to the top of the page
 
Peter46
post Mar 8 2010, 02:28 PM
Post#4


UtterAccess VIP
Posts: 7,489
Joined: 3-October 05
From: Oadby Leics, UK


I think your original code was correct.
Can you confirm that you are closing the database and setting the variable to nothing after each use.
Go to the top of the page
 
SOS_UA
post Mar 8 2010, 02:30 PM
Post#5



Posts: 405
Joined: 26-February 10



Yeah, I was thinking the OPTIONS instead of type, but also to correct your statement a little - it should be closing the RECORDSET (not database).
Go to the top of the page
 
Peter46
post Mar 8 2010, 02:31 PM
Post#6


UtterAccess VIP
Posts: 7,489
Joined: 3-October 05
From: Oadby Leics, UK


That is so (recordset)
Go to the top of the page
 
revans611
post Mar 8 2010, 02:33 PM
Post#7



Posts: 385
Joined: 12-November 07
From: Texas


I too think my original code was correct. I changed it back and got the 3001 error code. I exit and
restart the database after the error. It never gets to a code line to close the recordset or the database.
Go to the top of the page
 
SOS_UA
post Mar 8 2010, 02:38 PM
Post#8



Posts: 405
Joined: 26-February 10



I would set a breakpoint to see how it is happening. Where are you calling this function from? Is it in a query? It sounds like it and that you may have a null that is causing a problem.
Go to the top of the page
 
revans611
post Mar 8 2010, 03:14 PM
Post#9



Posts: 385
Joined: 12-November 07
From: Texas


I already tried a breakpoint, but it did not help. No nulls. Next I will check my calling function.
Go to the top of the page
 
revans611
post Mar 8 2010, 03:23 PM
Post#10



Posts: 385
Joined: 12-November 07
From: Texas


The most puzzling part of this mystery to me is the debug.print that results in 130 lines that come from just one instance of the debug.print. Could I have
stumbled on an Access bug? It's happened to me before. Is there any way to get a Microsoft person involved at this point, or
would that be premature?
Go to the top of the page
 
SOS_UA
post Mar 8 2010, 03:25 PM
Post#11



Posts: 405
Joined: 26-February 10



No bug. It is running that function once for every record in your query that you are calling it from.
Go to the top of the page
 
revans611
post Mar 8 2010, 03:54 PM
Post#12



Posts: 385
Joined: 12-November 07
From: Texas


I just finished recreating the front end database and back end database in case either one was corrupted. That did not work.
Go to the top of the page
 
revans611
post Mar 8 2010, 04:04 PM
Post#13



Posts: 385
Joined: 12-November 07
From: Texas


I don't think so. There are only 3 records in the table, and if it was running the function once for each record, 1) How would I get 130 lines for the debug.print; 2) it would have to execute the code in that follows the "set rst =" that follows it and is not shown here. It never gets that far.; 3) If I put a msgbox before the "strSQL =" line, it gets executed only once. iconfused.gif
Go to the top of the page
 
SOS_UA
post Mar 8 2010, 04:09 PM
Post#14



Posts: 405
Joined: 26-February 10



Where is this function being called from?
Go to the top of the page
 
revans611
post Mar 8 2010, 04:15 PM
Post#15



Posts: 385
Joined: 12-November 07
From: Texas


From here:
div class='codetop'>CODE
Private Sub Completed_Time_AfterUpdate()
Dim oTime As New clsTime
Dim oProject As New clsProject
Dim oPrimaryTask As New clsPrimaryTask
If Me.Start_Date <> "" And Not IsNull(Me.Start_Date) _
And Me.Completed_Date <> "" And Not IsNull(Me.Completed_Date) _
And Me.Start_Time <> "" And Not IsNull(Me.Start_Time) _
And Me.Completed_Time <> "" And Not IsNull(Me.Completed_Time) Then
DoCmd.Hourglass True
Me.Elapsed_Time = oTime.NonWorkingHoursElapsedTime(Me.Start_Date, Me.Start_Time, Me.Completed_Date, Me.Completed_Time)
Call oProject.CalculateElapsedTimeAverages
Call oPrimaryTask.CalculateElapsedTimeAverages
DoCmd.Hourglass False
End If
End Sub
Go to the top of the page
 
SOS_UA
post Mar 8 2010, 04:20 PM
Post#16



Posts: 405
Joined: 26-February 10



Well, I guess I'm down to needing to actually see the database to know what is really happening.
Go to the top of the page
 
revans611
post Mar 8 2010, 04:28 PM
Post#17



Posts: 385
Joined: 12-November 07
From: Texas


This is a variation on Microsofts Tasks.accdb with a split database. You will have to relink to the back end wherever you put it. The problem
is manifested when either the start date, start time, completed date or completed time is changed. Each fires the function(s) during the AfterUpdate event.
Attached File(s)
Attached File  TasksMonitor.zip ( 267.82K )Number of downloads: 6
Attached File  TasksMonitor_be.zip ( 31.58K )Number of downloads: 6
 
Go to the top of the page
 
revans611
post Mar 8 2010, 04:29 PM
Post#18



Posts: 385
Joined: 12-November 07
From: Texas


Hey! The new UA is pretty cool.
Go to the top of the page
 
SOS_UA
post Mar 8 2010, 05:20 PM
Post#19



Posts: 405
Joined: 26-February 10



Woah, okay I think we need a better explanation of what it is you are trying to accomplish.
've been stepping through this code and it appears that you are ending up recursively calling the same functions and I don't see the need for that.
Also, you should not be using strings for numbers, especially when trying to get the values (example dividing by 60 or doing the mod operations). You need to use numbers.
HAs to why this ends up with 128 debug.print statements, it is due to all of the recursion that is happening.
Go to the top of the page
 
gemmathehusky
post Mar 8 2010, 05:43 PM
Post#20


UtterAccess VIP
Posts: 4,751
Joined: 5-June 07
From: UK


what is in your error handler?
There does that transfer control to?
====
try
set the database as an object
dim db as database
set db = currentdb
...
...
Set rst = Db.OpenRecordset(strSQL, dbOpenSnapshot)
=====
also ttry adding a space between
>= #
Ofind sql is very pernickety about closed up syntax - which it cannot parse correctly.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    14th December 2019 - 04:56 AM