Open the recordset in access and use table name in dmax statement

**aman1234** · 05-10-2010, 05:27 AM

Hi folks

I have developed a project in access as a backend and excel as a frontend so all the coding is in excel/vba.
I am writing the following function but becvause 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
        dtmLower = nz(DMax("Finishtime", "tblbatchdetails", "[Name]='" & AnyPerson & "' and [Date1]=dateserial(" & Format(Date, "yyyy,mm,dd") & ")"))         
  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

**romperstomper** · 05-10-2010, 05:41 AM

What is the point of the recordset if you do that? Why not include a WHERE statement in the SQL rather than just opening the table and select the Max value? In other wordds, something like:

strSQL = "SELECT Max(Finishtime) FROM tblbatchdetails WHERE [Name]='" & AnyPerson & "' and [Date1]=dateserial(" & Format(Date, "yyyy,mm,dd") & ")"
rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic, adCmdText

**aman1234** · 05-10-2010, 05:56 AM

Thanks for your reply. But I am getting runtime error 3265 "Item cannot be found in the collection corresponding to the requested name or ordinal" in the red line.

Public Function Func2(AnyPerson As String, AnyStartTime As Date, AnyEndTime As Date) As String
 Dim dtmLower As Date
    Dim dtmUpper 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
dtmLower = rs.Fields("Finishtime") 
            Dim dtmtotaltime As Date
    dtmtotaltime = dtmUpper - dtmLower
            Func2 = dtmtotaltime
       End Function

Can you please how to store the max finish time in a variable so that we can find out the elapsed time.

Thanks
Aman

**romperstomper** · 05-10-2010, 06:04 AM

Your field is probably called MaxOfFinishTime or something similar. You can alias it in the SQL string or use Fields(0) since it's the only field in the recordset.

**aman1234** · 05-10-2010, 06:28 AM

Many thanks for your reply. The code works fine when there are few records in the table and if the table is empty then in gives me an error "Invalid use of null"

Public Function Func2(AnyPerson As String, AnyStartTime As Date, AnyEndTime As Date) As String
 Dim dtmLower As Date
    Dim dtmUpper 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
dtmLower = rs.Fields(0)
    Dim dtmtotaltime As Date
    dtmtotaltime = dtmUpper - dtmLower
    If dtmtotaltime = dtmUpper Then
    Func2 = "00:00:00"
    Else
            Func2 = dtmtotaltime
            End If
    End Function

**romperstomper** · 05-10-2010, 06:30 AM

Check to see if records were returned:

Public Function Func2(AnyPerson As String, AnyStartTime As Date, AnyEndTime As Date) As String
 Dim dtmLower As Date
    Dim dtmUpper 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 not rs.EOF Then
dtmLower = rs.Fields(0)
    Dim dtmtotaltime As Date
    dtmtotaltime = dtmUpper - dtmLower
    If dtmtotaltime = dtmUpper Then
         Func2 = "00:00:00"
    Else
            Func2 = dtmtotaltime
    End If
Else
Func2 = "00:00:00"
End If
End Function

**aman1234** · 05-10-2010, 06:39 AM

Again it gives runtime error 94, "Invalid use of null" at

dtmLower = rs.Fields(0)

Thanks

**romperstomper** · 05-10-2010, 06:44 AM

Oh, so you are actually getting a null field:

If IsNull(rs.Fields(0)) Then
 ' handle accordingly
Else
dtmLower = rs.Fields(0)
End If

**aman1234** · 05-10-2010, 06:56 AM

It worked gr8. Thanks a lot 4 your help.

Open the recordset in access and use table name in dmax statement

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Open the recordset in access and use table name in dmax statement

Re: Open the recordset in access and use table name in dmax statement

Re: Open the recordset in access and use table name in dmax statement

Re: Open the recordset in access and use table name in dmax statement

Re: Open the recordset in access and use table name in dmax statement

Re: Open the recordset in access and use table name in dmax statement

Re: Open the recordset in access and use table name in dmax statement

Re: Open the recordset in access and use table name in dmax statement

Re: Open the recordset in access and use table name in dmax statement

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions