Loop statements

[Guest]

I have some code that takes a variable from a list box (an internal area
name) in a form, applys that variable to a query, runs the query and outputs
this to MS Excel. It also copies the Excel template
and renames it the same name as the variable.

This works fine although there are 120 different variables so I woudl like
to programmatically do this for each of the 120 variables. I know I need a
statement that does this once, focuses on the
list box, moves to the next record and then loops the code until it reaches
the last record.

Can anyone help with this code? I think I need a do loop function and a
move to next record comand but I cannot get either to work.

Any help or guidance would be very much appreciated. i have tried several
times to do this myself but I am getting to the point where I can't see the
wood for the trees!

Thank you for your time.

Martin

Here is my code so far:

_______________________________________________________

Private Sub ProduceReport_Click()
Dim SourceFile, DestinationFile
Dim AreaName As String

AreaName = Forms![Main Menu]![ADName]

DoCmd.TransferSpreadsheet acExport, 8, "qryCompetitors", "c:\Temp\AD
Pack", True, ""
DoCmd.OpenQuery "qryTimeOpenedCalc", acViewNormal

SourceFile = "c:\Temp\AD Pack.xls"
DestinationFile = "c:\temp\" & AreaName & ".xls"
FileCopy SourceFile, DestinationFile

End Sub

 

[Guest]

Are all 120 variables always constent? If so you could make it a name space
and then set the name space as a record set and loop though it using a
counter.

 

[Guest]

Hi Martin,

Not too hard. But first some questions (always with the questions :

What is the name of the list box?
Does the list box have all 120 variables n it?
What is the name of the form the list box is on?
What is the SQL of the query "qryCompetitors"?
Will you *ALWAYS* want to export using the 120 different variables?

Why do you open the query "qryTimeOpenedCalc" after exporting the query
"qryCompetitors" to a spreadsheet?

 

[Guest]

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

Hi Martin,

Not too hard. But first some questions (always with the questions :

What is the name of the list box?
Does the list box have all 120 variables n it?
What is the name of the form the list box is on?
What is the SQL of the query "qryCompetitors"?
Will you *ALWAYS* want to export using the 120 different variables?

Why do you open the query "qryTimeOpenedCalc" after exporting the query
"qryCompetitors" to a spreadsheet?

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

I have some code that takes a variable from a list box (an internal area
name) in a form, applys that variable to a query, runs the query and outputs
this to MS Excel. It also copies the Excel template
and renames it the same name as the variable.

This works fine although there are 120 different variables so I woudl like
to programmatically do this for each of the 120 variables. I know I need a
statement that does this once, focuses on the
list box, moves to the next record and then loops the code until it reaches
the last record.

Can anyone help with this code? I think I need a do loop function and a
move to next record comand but I cannot get either to work.

Any help or guidance would be very much appreciated. i have tried several
times to do this myself but I am getting to the point where I can't see the
wood for the trees!

Thank you for your time.

Martin

Here is my code so far:

_______________________________________________________

Private Sub ProduceReport_Click()
Dim SourceFile, DestinationFile
Dim AreaName As String

AreaName = Forms![Main Menu]![ADName]

DoCmd.TransferSpreadsheet acExport, 8, "qryCompetitors", "c:\Temp\AD
Pack", True, ""
DoCmd.OpenQuery "qryTimeOpenedCalc", acViewNormal

SourceFile = "c:\Temp\AD Pack.xls"
DestinationFile = "c:\temp\" & AreaName & ".xls"
FileCopy SourceFile, DestinationFile

End Sub

 

[Guest]

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.)

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

 

[Guest]

Steve,

Thank you very much for the detailed response, I have got it working and
more importantly I understand the code and how it works which will help me in
the future.

Once again, thank you, I was expecting some pointers or guidance rather than
actual code I can use.

I must tell you that your disclaimer is incorrect. You can accept full
responsibility for my hapiness today in making this work. What a star!

Martin

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

Hi Martin,

Not too hard. But first some questions (always with the questions :

What is the name of the list box?
Does the list box have all 120 variables n it?
What is the name of the form the list box is on?
What is the SQL of the query "qryCompetitors"?
Will you *ALWAYS* want to export using the 120 different variables?

Why do you open the query "qryTimeOpenedCalc" after exporting the query
"qryCompetitors" to a spreadsheet?

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

I have some code that takes a variable from a list box (an internal area
name) in a form, applys that variable to a query, runs the query and outputs
this to MS Excel. It also copies the Excel template
and renames it the same name as the variable.

This works fine although there are 120 different variables so I woudl like
to programmatically do this for each of the 120 variables. I know I need a
statement that does this once, focuses on the
list box, moves to the next record and then loops the code until it reaches
the last record.

Can anyone help with this code? I think I need a do loop function and a
move to next record comand but I cannot get either to work.

Any help or guidance would be very much appreciated. i have tried several
times to do this myself but I am getting to the point where I can't see the
wood for the trees!

Thank you for your time.

Martin

Here is my code so far:

_______________________________________________________

Private Sub ProduceReport_Click()
Dim SourceFile, DestinationFile
Dim AreaName As String

AreaName = Forms![Main Menu]![ADName]

DoCmd.TransferSpreadsheet acExport, 8, "qryCompetitors", "c:\Temp\AD
Pack", True, ""
DoCmd.OpenQuery "qryTimeOpenedCalc", acViewNormal

SourceFile = "c:\Temp\AD Pack.xls"
DestinationFile = "c:\temp\" & AreaName & ".xls"
FileCopy SourceFile, DestinationFile

End Sub