ListBox help

[nlburgess]

I have a list box that is populated from a query. There is a report that
uses the selection from this list to pull data. The list contains the names
of various employers. My questions is, is there a way to force the report to
run based on all the employers listed? In other words I want the report to
run each time for each employer listed.

Thanks


Nate

 

[nlburgess]

Adding to this message:

I am proficient in Java so I know if i wanted to do this in java the theory
would look something like this.

int i = 0;
while(me.listbox.selected(i) = true)
{
DoCmd.SelectObject acReport, "Run_CFO_Report", True
DoCmd.PrintOut
i++; this increases i by 1
}

How can this logic apply?

 

[Guest]

Look in VBA Help for the ItemsSelected property. It has an example of how to
do what you are after. Also, you method of producing a report is incorrect.
Here is an sample:


Dim ctl As Control
Dim varItm As Variant

Set ctl = Me.ListBoxName
For Each varItm In ctl.ItemsSelected
Docmd.OpenReport "Run_CFO_Report", , , "[EmployerName] = '" & _
ctl.ItemData(varItm) & "'"
Next varItm

Set ctl = Nothing

 

[nlburgess]

Thanks,

I was able to get this to work. Is there a way to get the ctl item to
display as a label. I want the current Employer reporting on to be displayed
as a header.

Look in VBA Help for the ItemsSelected property. It has an example of how to
do what you are after. Also, you method of producing a report is incorrect.
Here is an sample:

Dim ctl As Control
Dim varItm As Variant

Set ctl = Me.ListBoxName
For Each varItm In ctl.ItemsSelected
Docmd.OpenReport "Run_CFO_Report", , , "[EmployerName] = '" & _
ctl.ItemData(varItm) & "'"
Next varItm

Set ctl = Nothing

Adding to this message:

[quoted text clipped - 20 lines]

 

[Guest]

Do you mean on the report or on the form?

--
Dave Hargis, Microsoft Access MVP

Thanks,

I was able to get this to work. Is there a way to get the ctl item to
display as a label. I want the current Employer reporting on to be displayed
as a header.

Look in VBA Help for the ItemsSelected property. It has an example of how to
do what you are after. Also, you method of producing a report is incorrect.
Here is an sample:

Dim ctl As Control
Dim varItm As Variant

Set ctl = Me.ListBoxName
For Each varItm In ctl.ItemsSelected
Docmd.OpenReport "Run_CFO_Report", , , "[EmployerName] = '" & _
ctl.ItemData(varItm) & "'"
Next varItm

Set ctl = Nothing

Adding to this message:

[quoted text clipped - 20 lines]

Nate

 

[nlburgess]

Do you mean on the report or on the form?

Thanks,

[quoted text clipped - 21 lines]

 

[Guest]

put a text box on your form, it can be hidden.
during the loop, put the name in the text box:

For Each varItm In ctl.ItemsSelected
Me.txtEmployer = ctl.ItemData(varItm)
Docmd.OpenReport "Run_CFO_Report", , , "[EmployerName] = '" & _
ctl.ItemData(varItm) & "'"
Next varItm

Now, On the report create a text box and set its control source to the
control on the form:

=Forms!MyFormName!txtEmployer
--
Dave Hargis, Microsoft Access MVP

Do you mean on the report or on the form?

Thanks,

[quoted text clipped - 21 lines]

Nate