Item Not Found in Collection 1

[FireGeek21]

I am getting "Item Not Found in Collection" in this FORM_LOAD function. Can anyone see what the issue is? I've been looking at this far too long to see the issue.

Dim sql As String
Dim ItemName As String
Dim db As DAO.Database

Dim rs As Recordset
Dim x As Integer

ItemName = Me.lblRunType.Caption

Set db = CurrentDb
sql = "SELECT Max(LastRun.LastRun_DT), Max(LastRun.Parameter_DT), LastRun.ItemRun FROM LastRun WHERE ItemRun = '" & ItemName & "' GROUP BY LastRun.ItemRun"
Set rs = db.OpenRecordset(sql)

Me.lblLastRun.Caption = "Append Queries for Active Staff last run on " & rs!LastRun_DT & " with a parameter date of " & rs!Parameter_DT & "."


FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[dhookom]

Try alias your columns:

Set db = CurrentDb
sql = "SELECT Max(LastRun.LastRun_DT) as Something, Max(LastRun.Parameter_DT) as SomethingElse, " & _
   " LastRun.ItemRun FROM LastRun WHERE ItemRun = '" & ItemName & "' GROUP BY LastRun.ItemRun"
Set rs = db.OpenRecordset(sql)

Me.lblLastRun.Caption = "Append Queries for Active Staff last run on " & rs!Something & _
   " with a parameter date of " & rs!SomethingElse & "."

Duane
Hook'D on Access
MS Access MVP

 

[FireGeek21]

Here's how I modified it. Same issue!

<code>
Dim sql As String
Dim ItemName As String
Dim db As DAO.Database

Dim rs As Recordset

ItemName = Me.lblRunType.Caption

Set db = CurrentDb
sql = "SELECT Max(LastRun.LastRun_DT) as LastRun, Max(LastRun.Parameter_DT) as ParamRun, LastRun.ItemRun FROM LastRun WHERE ItemRun = '" & ItemName & "' GROUP BY LastRun.ItemRun"
Set rs = db.OpenRecordset(sql)

Me.lblLastRun.Caption = "Append Queries for Active Staff last run on " & rs!LastRun_DT & " with a parameter date of " & rs!Parameter_DT & "."
</code>

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[FireGeek21]

It appears as if the Set rs = db.openrecordset(sql) isn't opening anything yet if I try x = recordcount, it returns 1.

UGH!!!

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[Andrzejek]

You SQL does not have fields called LastRun_DT or Parameter_DT

You may want to change your SQL to something like:
[tt]
sql = "SELECT Max(LastRun_DT)[blue] As LastRun_DT[/blue], Max(Parameter_DT) [blue]As Parameter_DT[/blue], ItemRun FROM LastRun WHERE ItemRun = '" & ItemName & "' GROUP BY ItemRun"
[/tt]

You may also check if your Select statement returns any records.

[pre]
Set rs = db.OpenRecordset(sql)

If rs.BOF <> rs.EOF Then[green]
'You have some record(s) here[/green]
Else[green]
'Sorry, no records[/green]
End If[/pre]

Have fun.

---- Andy

 

[FireGeek21]

Still getting the same error! [headbang]

    Dim sql As String
    Dim ItemName As String
    Dim db As DAO.Database
    Dim rs As Recordset
    
    ItemName = Me.lblRunType.Caption

    Set db = CurrentDb
    sql = "SELECT Max(LastRun_DT) As LastRun, Max(Parameter_DT) As Parameter, ItemRun FROM LastRun WHERE ItemRun = '" & ItemName & "' GROUP BY ItemRun"
    Set rs = db.OpenRecordset(sql)
    
    If rs.RecordCount >= 1 Then
        Me.lblLastRun.Caption = "Append Queries for Active Staff last run on " & rs!LastRun_DT & " with a parameter date of " & rs!Parameter_DT & "."
    Else
        Exit Sub
    End If

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[FireGeek21]

This is showing rs.recordcount = 1 but still getting the "Item Not Found in Collection" error.

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[Andrzejek]

Your field's names from your SQL and how you use them must match, but yours don't:

sql = "SELECT Max(LastRun.LastRun_DT) as [blue]LastRun[/blue], Max(LastRun.Parameter_DT) as [red]ParamRun[/red], LastRun.ItemRun FROM LastRun WHERE ItemRun = '" & ItemName & "' GROUP BY LastRun.ItemRun"
Set rs = db.OpenRecordset(sql)

Me.lblLastRun.Caption = "Append Queries for Active Staff last run on " & rs![blue]LastRun_DT[/blue] & " with a parameter date of " & rs![red]Parameter_DT[/red] & "."


Have fun.

---- Andy

 

[FireGeek21]

That was it! Ha, I knew I stared at this far too long to see the obvious.

Thanks Andy!

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)