runtime error 2950

[aman]

Hi folks
I don't know what is happening with the following code. When there are no records in the table tblbatchdetails and the person try to insert a record then in the following function the error comes at dmax statement. But if I make this line comments and try to insert the record 2 or 3 times then the code works perfetcly fine. If after that I remove the comments from that line and try to run the following code then it starts working absolutely fine. I think when there are no records in the table for particular date then the code gives problem otherwise dmax statement works fine.

Public Function Func3(AnyPerson As String, AnyStartTime As Date, AnyEndTime As Date) As String
 Dim dtmLower As Date
    Dim dtmUpper As Date
    dtmUpper = AnyStartTime
    [COLOR=darkred]dtmLower = nz(DMax("Finishtime", "tblbatchdetails", "Name='" & AnyPerson & "' and Date1=dateserial(" & Format(Date, "yyyy,mm,dd") & ")"))[/COLOR]         
Dim dtmtotaltime As Date
    dtmtotaltime = dtmUpper - dtmLower
    If dtmtotaltime = dtmUpper Then
    Func3 = "00:00:00"
    Else
            Func3 = dtmtotaltime
            End If
       End Function

sometimes the code works fine the the first time but sometimes it doesn't. Can anyone please tell me why? My backend is Access and frontend is Excel.The code is written in excel/vba.

Any suggestions anyone please?
Thanks
Aman

 

[JANR]

Solved here???: http://www.access-programmers.co.uk/forums/showthread.php?t=192595

Same problem or.....

JR

 

[aman]

Thats a different query . Here the runtime error 2950 is coming up but in that there was a problem with date format.

Thanks
Aman

 

[JANR]

How about checking for NULL's see Guus's answer here: http://www.access-programmers.co.uk/forums/showthread.php?t=192465

Same function as as far as I can see??

JR

 

[aman]

I have written the following code and I tried to run the form no in the morning and again it gave me reserved error 2950 but I know when I will make the dmax line comments then will run the code 2 or three times and after that when I will remove the comments from that line and again run the code then it will run fimr. What is this happening? I am not understanding anything.

 Public Function Func2(AnyPerson As String, AnyStartTime As Date, AnyEndTime As Date) As String
 Dim dtmLower As Date
    Dim dtmUpper As Date
    dtmUpper = AnyStartTime
    dtmLower = nz(DMax("Finishtime", "tblbatchdetails", "Name='" & AnyPerson & "' and Date1=dateserial(" & Format(Date, "yyyy,mm,dd") & ") And Finishtime Is Not Null"), 0)
     Dim dtmtotaltime As Date
    dtmtotaltime = dtmUpper - dtmLower
            Func2 = dtmtotaltime
       End Function

Can anyone please explain me why this error message is coming?

Thanks a lot in advance!

Aman

 

[aman]

Hi All

I have come to know what is going on. May be I am going out of way as the project is developed in access as a backend and excel as a frontend so all the coding is in excel/vba.SO I am not sure if its OK to post in this access forum or not. Anyway I am writing the following function but because I have to refer a table in access database , I need to open the recordset and use that table name in the dmax statement. Please see the code below:

Public Function Func3(AnyPerson As String, AnyStartTime As Date, AnyEndTime As Date) As String
 Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=C:\Documents and Settings\Amanpreet Kaur\Desktop\Sunlife Direct Pilot System.mdb;"
      Set rs = CreateObject("ADODB.Recordset")
    rs.Open "tblbatchdetails", cn, adOpenKeyset, adLockOptimistic, adCmdTable
  Dim dtmLower As Date
    Dim dtmUpper As Date
    dtmUpper = AnyStartTime
       [COLOR=darkred] [/COLOR][COLOR=darkred]dtmLower = nz(DMax("Finishtime", "tblbatchdetails", "[Name]='" & AnyPerson & "' and [Date1]=dateserial(" & Format(Date, "yyyy,mm,dd") & ")"))[/COLOR]         
  Dim dtmtotaltime As Date
    dtmtotaltime = dtmUpper - dtmLower
    If dtmtotaltime = dtmUpper Then
    Func3 = "00:00:00"
    Else
            Func3 = dtmtotaltime
            End If
                   End Function

I have a problem in code in red colour as tblbatchdetails is a table in access so how can we connect the frontend to this table. If I keed the access database opened while running the userform then it works fine but if the access database is closed and we try to tun the userform then it gives reserved error 2950 in dmax statement.

Thanks for any help.

Aman

 

[JANR]

Just a suggestion, but have you checked that the date fields your are sending to the function DOSEN'T contain any NULLS. Since the parameters in your function is declared as Date, if you send an invalid date or NULL then you will get an error.

Try to include a whereclause in your query to exclude any dates with nulls.

....Where(( Not([Datefield]) Is Null))

JR

 

[chergh]

I really hate it when people post the same question twice on the same board.

Anyway to reiterate what I said on the excel board do you have a named range in your excel sheet which has the same name as your query?

 

[aman]

JANR,the date format is correct and it doesn't contain null at all.
Cherug, I have not defined any date range as the database is access and frontend is just excel so I don't need named ranges at all in excelsheet.

Thanks

 

[chergh]

JANR,the date format is correct and it doesn't contain null at all.
Cherug, I have not defined any date range as the database is access and frontend is just excel so I don't need named ranges at all in excelsheet.

Thanks

At least get my username right.

I have no idea what not defining a date range has to do with a named range in excel. Named range are also an excellent way to find data in excel rather than using an absolute cell reference or relying on find functions.

I assume you have already looked at: http://support.microsoft.com/kb/931407

 

[aman]

Chergh, I resolved my problem.To let you know the dmax statment I was using was not suitable and I replaced that code with the following:

Public Function Func2(AnyPerson As String, AnyStartTime As Date, AnyEndTime As Date) As String
 Dim dtmLower As Date
    Dim dtmUpper As Date
    Dim dtmtotaltime As Date
    dtmUpper = AnyStartTime
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=C:\Documents and Settings\Amanpreet Kaur\Desktop\Sunlife Direct Pilot System.mdb;"
      Set rs = CreateObject("ADODB.Recordset")
    strSQL = "SELECT Max(Finishtime) FROM tblbatchdetails WHERE [Name]='" & AnyPerson & "' and [Date1]=dateserial(" & Format(Date, "yyyy,mm,dd") & ")"
rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic, adCmdText
If IsNull(rs.Fields(0)) Then
 dtmLower = "00:00:00"
Else
dtmLower = rs.Fields(0)
End If
     dtmtotaltime = dtmUpper - dtmLower
      If dtmtotaltime = dtmUpper Then
         Func2 = "00:00:00"
    Else
           Func2 = dtmtotaltime
    End If
End Function

Thanks anyway 4 looking.