RE: Loop statements

Martin,

Read this completely before starting to make the changes. I would suggest
trying this on a COPY of your MDB first.

** I did't know if you used the query "qrycompetitors" for other reports, so
copy the query and rename it "qrycompetitorsTS". In design view change the
criteria from
[Forms]![Main Menu]![ADName] to
[Forms]![Main Menu]![ubADName]. ("ub" stands for unbound)

** Next, add two unbound text boxes to the form "Main Menu". BTW, when you
name objects, try not to use spaces in the names (or reserved words). See
http://www.accessmvp.com/djsteele/AccessTenCommandments.html or
http://www.mvps.org/access/tencommandments.htm

For a list of reserved words, see:
http://allenbrowne.com/AppIssueBadWord.html

** Name one of the new text boxes "ubADName". Set the visible property to
FALSE. This is the current item being processed.

Name the other text box "intProgress". This is to count how many items
have been "transfered".

** Add two buttons. Name one "cmdClear". Change the caption to "Clear".
Name the other "cmdReverse". Change the caption to "Reverse". I would place
them side by side below the list box (if there is room).

** On the "Other" tab of the properties dialog box for the listbox "ADName",
set the "Multi Select" property to "Simple" or "Extended". Check Help for the
differences.

** Delete any code for the button "ProduceReport_Click". Now paste in the
following code.

(Watch for line wrap)

'-----beg code-----------------
Private Sub ProduceReport_Click()
On Error GoTo Err_ProduceReport_Click

Dim sMsg As String, sFilePath As String
Dim vItem

sFilePath = "c:\temp\"
sMsg = "Done! Look in: " & vbCrLf & vbCrLf & sFilePath
'ubASName is an unbound text box on the form
ubADName = Null
'counter
intProgress = 0

For Each vItem In Me.ADname.ItemsSelected
ubADName = Me.ADname.ItemData(vItem)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,
"qryCompetitorsTS", sFilePath & Me.ubADName & ".xls", True
Me.intProgress = Me.intProgress + 1
Me.Repaint
Next

' unselect items in the list box
Call DoList("C", "ADname")

Exit_ProduceReport_Click:
'Tell me when done or error
MsgBox sMsg
Exit Sub

Err_ProduceReport_Click:
MsgBox Err.Description
sMsg = "Error - Aborting Transfer. " & Me.intProgress & " spreadsheets
transfered. Look in: " & vbCrLf & vbCrLf & sFilePath
Resume Exit_Command12_Click

End Sub

Private Sub cmdClear_Click()
Call DoList("C", "ADname")
End Sub

Private Sub cmdReverse_Click()
Call DoList("R", "ADname")
End Sub

'reverse or clear selection in lstEND list box
Sub DoList(psAction As String, psTLD As String)
Dim theList As Control, vItem, n As Long

Set theList = Me(psTLD)
Select Case psAction
Case "C"
For n = 0 To theList.ListCount
theList.Selected(n) = False
Next
Case "R"
For n = 0 To theList.ListCount
theList.Selected(n) = Not theList.Selected(n)
Next
End Select

End Sub
'-----end code-----------------

** Open the properties for the two new buttons and select "[Event
Procedure]" for the "Click" event.

** Open the properties for the button "ProduceReport" and ensure that
"[Event Procedure]" is in the "Click" event


** Go back and check the NAME property of the two new text boxes and the two
new buttons to ensure they are named correctly and the Click events have
"[Event Procedure]" in them.

-------------------------------------
Usage:

The CLEAR button will unselect all items in the list box.

If you want to select ALL 120 items in the list box, click the REVERSE button.
If you want to select 119 items, click (select) the one you DON'T want, then
click the REVERSE button. Same goes if you don't want 5 items. Select them,
then click the REVERSE button. Easier than clicking 115 times..... <g>


Select some items and mash the "ProduceReport" button.

---------------Disclaimer----------------
Guaranteed 100% bug free until you run the code.
Do not paste the code on a day ending in "y".
Not responsible for anything.
The code is slightly used.... I did a little testing.

:)
---
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Martin" wrote:

Hi,

The list box is called "ADName"
The list box does have all 120 variables
The form the list box is on is called "Main Menu"
Here is the SQL for "qrycompetitors":

SELECT [UKBB Regions].Area, Competitors.[Fascia Name] AS [Competitor Name],
Count(Competitors.ID) AS [No of Branches]
FROM Competitors INNER JOIN [UKBB Regions] ON Competitors.[Post District] =
[UKBB Regions].[Post District]
WHERE (((Competitors.[Branch Type])<>"Agents") AND (([UKBB
Regions].Area)=[Forms]![Main Menu]![ADName]) AND ((Competitors.[Fascia
Name])<>"ROYAL BANK" And (Competitors.[Fascia Name])<>"NATWEST" And
(Competitors.[Fascia Name])<>"POST OFFICE"))
GROUP BY [UKBB Regions].Area, Competitors.[Fascia Name]
ORDER BY Count(Competitors.ID) DESC;

I will sometimes want to report on 1 of the variables or all of the
variables. I can report on just one of the variables but it's the all
variables I can't get to work.

I am sorry, the Docmd.OpenQuery line should not be there. This was
something I was working on but is now obsolete.

Thank you for your help.

Martin

.