Item not found in this collection

Hi,
I have a form that contains a list box (lstReports). This list displays all
the reports of which prefix is either “rpt� or “rpf�. If the report prefix is
“rpt�, the report opens directly. If the report prefix is “rpf�, the report
will open via a parameter form (for dates bracketing).
This is the VBA code on the OnOpen event of the form:

Private Sub Form_Open(Cancel As Integer)
Dim objAO As AccessObject
Dim objCP As Object
Dim strValues As String
Set objCP = Application.CurrentProject
For Each objAO In objCP.AllReports
If left(objAO.Name, 3) = "rpt" Or left(objAO.Name, 3) = "rpf" Then
strValues = strValues & Mid(objAO.Name, 4) & ";" & left(objAO.Name,
3) & ";"
End If
Next objAO
LstReports.RowSourceType = "Value List"
LstReports.RowSource = strValues
End Sub

Underneath the list box, I have a Text box called: txtReportDesc. This
allows me to see a brief description of the report before opening it.
This is the function I have put in:
Function ReportDescription(ReportName As Variant) As String
On Error GoTo Err_ReportDescription
Dim db As Database
Dim con As Container
Dim doc As Document
Dim prp As Property
Set db = CurrentDb()
Set con = db.Containers("Reports")
Set doc = con.Documents(ReportName)
Set prp = doc.Properties("description")

ReportDescription = prp.Value
Exit_ReportDescription:
Exit Function
Err_ReportDescription:
If Err.Number = 3270 Then
ReportDescription = "There is no description for this Report"
Resume Exit_ReportDescription
Else
MsgBox Err.Description
Resume Exit_ReportDescription
End If
End Function

This is the code for the OnOpen event of the list box which enables the
description to be displayed:
Private Sub LstReports_Click()
Me!txtReportDesc = ReportDescription("rpt" & Me!LstReports)
Me!txtReportDesc = ReportDescription("rpf" & Me!LstReports)
End Sub

This works well, but every time I click on a report’s name in the list box,
I get “Item not found in this collection�, I press “OK� and the report opens.
I would like to get rid of this message, and any help would be really
appreciated.

Each item in your listbox is the name of a report with the rpt or rpf
stripped off the front. However, you're trying to get the description for
both the name of the report with rpt AND with rpf in front: it's only going
to be one or the other!

Now, you're storing the prefix in the listbox as well as the name. You
should be able to retrieve that prefix using the Column property.

Try the following:

Private Sub LstReports_Click()
Me!txtReportDesc = ReportDescription(Me!LstReports.Column(1) &
Me!LstReports)
End Sub


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Chris" wrote in message
...
Hi,
I have a form that contains a list box (lstReports). This list displays
all
the reports of which prefix is either "rpt" or "rpf". If the report prefix
is
"rpt", the report opens directly. If the report prefix is "rpf", the
report
will open via a parameter form (for dates bracketing).
This is the VBA code on the OnOpen event of the form:

Private Sub Form_Open(Cancel As Integer)
Dim objAO As AccessObject
Dim objCP As Object
Dim strValues As String
Set objCP = Application.CurrentProject
For Each objAO In objCP.AllReports
If left(objAO.Name, 3) = "rpt" Or left(objAO.Name, 3) = "rpf" Then
strValues = strValues & Mid(objAO.Name, 4) & ";" &
left(objAO.Name,
3) & ";"
End If
Next objAO
LstReports.RowSourceType = "Value List"
LstReports.RowSource = strValues
End Sub

Underneath the list box, I have a Text box called: txtReportDesc. This
allows me to see a brief description of the report before opening it.
This is the function I have put in:
Function ReportDescription(ReportName As Variant) As String
On Error GoTo Err_ReportDescription
Dim db As Database
Dim con As Container
Dim doc As Document
Dim prp As Property
Set db = CurrentDb()
Set con = db.Containers("Reports")
Set doc = con.Documents(ReportName)
Set prp = doc.Properties("description")

ReportDescription = prp.Value
Exit_ReportDescription:
Exit Function
Err_ReportDescription:
If Err.Number = 3270 Then
ReportDescription = "There is no description for this Report"
Resume Exit_ReportDescription
Else
MsgBox Err.Description
Resume Exit_ReportDescription
End If
End Function

This is the code for the OnOpen event of the list box which enables the
description to be displayed:
Private Sub LstReports_Click()
Me!txtReportDesc = ReportDescription("rpt" & Me!LstReports)
Me!txtReportDesc = ReportDescription("rpf" & Me!LstReports)
End Sub

This works well, but every time I click on a report's name in the list
box,
I get "Item not found in this collection", I press "OK" and the report
opens.
I would like to get rid of this message, and any help would be really
appreciated.

Hi Douglas,

Thank you very much, it worked! Fantastic.
Chris.

"Douglas J Steele" wrote:

Each item in your listbox is the name of a report with the rpt or rpf
stripped off the front. However, you're trying to get the description for
both the name of the report with rpt AND with rpf in front: it's only going
to be one or the other!

Now, you're storing the prefix in the listbox as well as the name. You
should be able to retrieve that prefix using the Column property.

Try the following:

Private Sub LstReports_Click()
Me!txtReportDesc = ReportDescription(Me!LstReports.Column(1) &
Me!LstReports)
End Sub


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Chris" wrote in message
...
Hi,
I have a form that contains a list box (lstReports). This list displays
all
the reports of which prefix is either "rpt" or "rpf". If the report prefix
is
"rpt", the report opens directly. If the report prefix is "rpf", the
report
will open via a parameter form (for dates bracketing).
This is the VBA code on the OnOpen event of the form:

Private Sub Form_Open(Cancel As Integer)
Dim objAO As AccessObject
Dim objCP As Object
Dim strValues As String
Set objCP = Application.CurrentProject
For Each objAO In objCP.AllReports
If left(objAO.Name, 3) = "rpt" Or left(objAO.Name, 3) = "rpf" Then
strValues = strValues & Mid(objAO.Name, 4) & ";" &
left(objAO.Name,
3) & ";"
End If
Next objAO
LstReports.RowSourceType = "Value List"
LstReports.RowSource = strValues
End Sub

Underneath the list box, I have a Text box called: txtReportDesc. This
allows me to see a brief description of the report before opening it.
This is the function I have put in:
Function ReportDescription(ReportName As Variant) As String
On Error GoTo Err_ReportDescription
Dim db As Database
Dim con As Container
Dim doc As Document
Dim prp As Property
Set db = CurrentDb()
Set con = db.Containers("Reports")
Set doc = con.Documents(ReportName)
Set prp = doc.Properties("description")

ReportDescription = prp.Value
Exit_ReportDescription:
Exit Function
Err_ReportDescription:
If Err.Number = 3270 Then
ReportDescription = "There is no description for this Report"
Resume Exit_ReportDescription
Else
MsgBox Err.Description
Resume Exit_ReportDescription
End If
End Function

This is the code for the OnOpen event of the list box which enables the
description to be displayed:
Private Sub LstReports_Click()
Me!txtReportDesc = ReportDescription("rpt" & Me!LstReports)
Me!txtReportDesc = ReportDescription("rpf" & Me!LstReports)
End Sub

This works well, but every time I click on a report's name in the list
box,
I get "Item not found in this collection", I press "OK" and the report
opens.
I would like to get rid of this message, and any help would be really
appreciated.