Looping to select items in a listbox one-by-one 1

[kevvo]

I am trying to write code for a reporting database. In the main form, users have to select a contract number from a listbox, and then they hit a button which is coded to run a number of queries, and then export reports based on these queries.

Problem being that I have to run all of these reports one-by-one manually every month. I am searching for code which will select each value in the list box so that the later code selects the relevant records and exports the reports,then returning to select the next record in the listbox etc.etc... without manual intervention.

Any ideas?

 

[rubbernilly]

For a = 0 to lstbox.listcount-1
debug.print lstbox.itemdata(a)
'OR for a particular column, here the first
debug.print lstbox.Col(0,a)
Next a

 

[kevvo]

Hi,

Thanks for this, maybe I should clarify a bit more the way things are working in the DB;

The list box is a combo box in a form (form = "reports", combo box = "choopurch"). When I run the above code it is asking for an object, even when I insert the objects as named just above.

Could you show the code using the name of the objects I have put above?

I would also guess that before the "next a" code, I would put the sequence of queries to run for each record in the list.

Thanks

 

[rubbernilly]

If you have the code on the form, you won't have to worry about the form name. The form name would only serve to qualify the combo-box object if you were manipulating it from some other location.

For a = 0 to choopurch.listcount-1
debug.print choopurch.itemdata(a)
Next a


Or, from another location:

With Forms![Reports].choopurch
For a = 0 to .listcount-1
debug.print .itemdata(a)
Next a
End With

(Your form name of "Reports" might get you into trouble as it is a reserved word).

The debug.print line is just a placeholder for you to see that it is stepping through the items in the combobox. Remove it and add whatever real code you need to to process the associated queries. If you had the queries listed alongside the data in the combo box and you included those queries in the combo box, you could get at them through the column property of the combobox.

For instance, you have the following entry in some table somewhere:

MonkeyJuggler Query1,Query2,Query3,Query4

So that in your combobox MonkeyJuggler was the first column (and maybe the only one users see) and the list of queries were all in the second column, then you could dynamically process the Queries listed using the Split() function and stepping through an array.

If you want help with that, post back.

 

[kevvo]

Hi, and thanks again for getting back so quickly.

I've put the code in and it is detailed below:

Private Sub Command58_Click()

For a = 0 To choopurch.ListCount - 1

DoCmd.OpenQuery "Planactdel", acNormal, acEdit
If Me!incp = True Then
DoCmd.OpenQuery "PLANACTaddplanSUB", acNormal, acEdit
DoCmd.OpenQuery "PLANACTaddplanYRSUB", acNormal, acEdit
End If
If Me!inci = True Then
DoCmd.OpenQuery "PLANACTaddACTSUB", acNormal, acEdit
DoCmd.OpenQuery "CheckPLANACTCLPNTariff", acNormal, acEdit
End If
DoCmd.OutputTo acOutputReport, "PLANACTbyContract2005", acFormatSNP, "Filename", no
DoCmd.OutputTo acOutputQuery, "ContractPerfExcelExportHRG2005", acFormatXLS, "Filename", no

Next a

Exit_Command58_Click:
Exit Sub

Err_Command58_Click:
MsgBox Err.Description
Resume Exit_Command58_Click
End Sub


The problem I'm getting is that it's looping, but performing the process on the same (i.e. 1st) list item every time - it's not taking each subsequent item and performing the queries and exports.

I think it's nearly there though!

Thanks

 

[rubbernilly]

Ahhh... and I bet your queries are set to read from that choopurch combobox in their criteria?

(BTW, if I had an award to give for coolest field name, "choopurch" would win, I think).

If that is the case, then try this:

Private Sub Command58_Click()
[red]Dim a as Long
Dim sCurrent as String

sCurrent = choopurch[/red]

For a = 0 To choopurch.ListCount - 1
[red]   choopurch = choopurch.itemdata(a)[/red]     
   DoCmd.OpenQuery "Planactdel", acNormal, acEdit
   If Me!incp = True Then
   DoCmd.OpenQuery "PLANACTaddplanSUB", acNormal, acEdit
   DoCmd.OpenQuery "PLANACTaddplanYRSUB", acNormal, acEdit
   End If
   If Me!inci = True Then
   DoCmd.OpenQuery "PLANACTaddACTSUB", acNormal, acEdit
   DoCmd.OpenQuery "CheckPLANACTCLPNTariff", acNormal, acEdit
   End If
  DoCmd.OutputTo acOutputReport, "PLANACTbyContract2005", acFormatSNP, "Filename", no
  DoCmd.OutputTo acOutputQuery, "ContractPerfExcelExportHRG2005", acFormatXLS, "Filename", no

Next a

[red]choopurch = scurrent[/red]

Exit_Command58_Click:
    Exit Sub

Err_Command58_Click:
    MsgBox Err.Description
    Resume Exit_Command58_Click
End Sub

Changes in [red]RED[/red]. This now will set the value of the combobox to be each item in the dropdown, and then run the queries. Also, it will first store the current value of the combobox in a variable so that it can re-write it at the end of the procedure.

HTH
Rubbernilly

 

[kevvo]

That's done the trick, and saved me at least 4 hours a month!

Thanks loads Rubbernilly!

And btw the "choopurch" genius comes from a derivation of the fact that the combobox "chooses" the "purchaser" for each of the items in the list!