Loop or Array Help Needed Please

[graviz]

I have about 10 queries I will need to run daily for 20 different offices. As of right now I go into each query and change the "office" criteria for each query 20 times. I've heard of loops and arrays but I don't know anything about them. I've tried searching for infomation on them but I still don't understand how I can use them to help my sitution. This is just one of those concepts I'm having a very hard time grasping Does anyone have any good examples or tutorials on how to use on of them to do something like what I'm trying to do? Again my experience level with loops and arrays is 0.

 

[ajetrumpet]

I have about 10 queries I will need to run daily for 20 different offices. As of right now I go into each query and change the "office" criteria for each query 20 times. I've heard of loops and arrays but I don't know anything about them. I've tried searching for infomation on them but I still don't understand how I can use them to help my sitution. This is just one of those concepts I'm having a very hard time grasping Does anyone have any good examples or tutorials on how to use on of them to do something like what I'm trying to do? Again my experience level with loops and arrays is 0.

I assume these queries are action queries? If they are, you can use runsql of the docmd to run them instead of just opening them. that way, you can use a dynamic sql line. Personally, I would store your sql lines in a table and loop through it everytime you need to run queries for the offices. that way, one function run and you're done!

 

[graviz]

I assume these queries are action queries? If they are, you can use runsql of the docmd to run them instead of just opening them. that way, you can use a dynamic sql line. Personally, I would store your sql lines in a table and loop through it everytime you need to run queries for the offices. that way, one function run and you're done!

May need to open another post as I do not know anything about sql. : ) The queries basically pull different metrics for the offices from different tables. (most of them are make table queries) The end result I am going for is to output them to excel spreadsheets which I do know how to do. The problem is it's a very manual process as I don't know how to change the office names automatically. I could create 200 queries but I think there must be an easier way.

 

[ajetrumpet]

May need to open another post as I do not know anything about sql. : ) The queries basically pull different metrics for the offices from different tables. (most of them are make table queries) The end result I am going for is to output them to excel spreadsheets which I do know how to do. The problem is it's a very manual process as I don't know how to change the office names automatically. I could create 200 queries but I think there must be an easier way.

as I said before, store you complete sql lines from the queries as they are written now in a table field. do you know how to loop a table field? I'm guessing not. Maybe I'll upload a sample to ya. how would that be?

 

[graviz]

as I said before, store you complete sql lines from the queries as they are written now in a table field. do you know how to loop a table field? I'm guessing not. Maybe I'll upload a sample to ya. how would that be?

Not so much. An example would be great. Thanks for your help!

 

[ajetrumpet]

this will create 10 tables for ya. it's probably the same thing you are looking to do. welcome.

 

[boblarson]

1. You don't need make table queries to export to Excel. You can just output the query to Excel.

2. You can have a FORM to select the criteria you want (you should not be needing to go into a query to change it's criteria). A multi-select list box could be the ticket here.

I haven't seen Adam's sample so I don't know if it has all you need. But, it should not be neccessary to create tables to export the data to Excel.

 

[graviz]

this will create 10 tables for ya. it's probably the same thing you are looking to do. welcome.

I think I'm getting closer. I looked at your code and I want to make sure I understand it. I went ahead and put comments under each line. Could you let me know if I'm off or answer the couple questions I had below?


Function runthisfunction()
DoCmd.SetWarnings False
'Turn off warnings
Dim i As Integer
'declaring a variable
Dim rs As Recordset
'declaring a variable Is a Recordset just a declairation for a table or query?
Set rs = CurrentDb.OpenRecordset("tbl", dbOpenDynaset)
'does setting "rs" to mean open the table "tbl"? What does dbOpenDynaset mean?
rs.MoveLast
'Does this mean goto the last record in the table? If so why do you do this?
rs.MoveFirst
'Does this mean goto the first record in the table? If so why do you do this?
i = 1
'Setting the variable i = 1
While Not rs.EOF
'Do the process below until it reaches the last record in the table?
DoCmd.RunSQL rs!line1 & " " & CStr(i) & " " & rs!line2
'I know you're running SQL but I have no idea what that means above

rs.MoveNext
'Selects the next record?

i = i + 1
' adds 1 to the variable i so now it will become 2? Why would you do this?
End
'Ends the process
Set rs = Nothing
'removes the table from memory? If so what happens if you do not?
End Function

 

[graviz]

1. You don't need make table queries to export to Excel. You can just output the query to Excel.

2. You can have a FORM to select the criteria you want (you should not be needing to go into a query to change it's criteria). A multi-select list box could be the ticket here.

I haven't seen Adam's sample so I don't know if it has all you need. But, it should not be neccessary to create tables to export the data to Excel.

Bob-

1. Very true and I think I may be doing some of what you said and some I believe I do a make table query and output the table. I probably just need to clean it up and do one or the other.

2. I'm trying to stay away from forms as I would like to set this up as an auto job and it would be nice to just have the list of offices in a table and update them as needed.

Thanks for the response as well.

 

[ajetrumpet]

if you still need me, here are some notes:

Function runthisfunction()
DoCmd.SetWarnings False
'Turn off warnings
Dim i As Integer
'declaring a variable
Dim rs As Recordset
'declaring a variable Is a Recordset just a declairation for a table or query? - it's the shell object of them
Set rs = CurrentDb.OpenRecordset("tbl", dbOpenDynaset)
'does setting "rs" to mean open the table "tbl"? What does dbOpenDynaset mean? - dynaset is just a recordset type. most commonly used. has other purposes, but I don't know of them
rs.MoveLast
'Does this mean goto the last record in the table? If so why do you do this?
rs.MoveFirst
'Does this mean goto the first record in the table? If so why do you do this? - above two lines are needed to capture all records that are in the table. if you don't do it, the recordcount will return 0.
i = 1
'Setting the variable i = 1
While Not rs.EOF
'Do the process below until it reaches the last record in the table? - yes
DoCmd.RunSQL rs!line1 & " " & CStr(i) & " " & rs!line2
'I know you're running SQL but I have no idea what that means above - the above is running the SQL commands I have stored in the table. look at the table to see what commands it is running. this is a concatenation.

rs.MoveNext
'Selects the next record? - goes to next record so you can make a new query with the new sql statement in that record's fields

i = i + 1
' adds 1 to the variable i so now it will become 2? Why would you do this? - this is not necessary. this is for example only. this is part of my concatenation in the example I gave you.
End
'Ends the process
Set rs = Nothing
'removes the table from memory? If so what happens if you do not? - if not, you have a great chance of application or object corruption, because the memory is not dumped. it's just hanging out there.
End Function

 

[gemma-the-husky]

it depends how you write your query

lets say your query needs to pick up details for 1 depot

so in your query, have a criteria that picks up this depot

something like

forms!mainform!depotid

-------------
and now, in your main form, you need something like

for each depot
depotid = depot
run the query
next



so the problem is really how you set up and control this loop

 

[boblarson]

Bob-

1. Very true and I think I may be doing some of what you said and some I believe I do a make table query and output the table. I probably just need to clean it up and do one or the other.

2. I'm trying to stay away from forms as I would like to set this up as an auto job and it would be nice to just have the list of offices in a table and update them as needed.

Thanks for the response as well.

Here's a sample of mine which shows how you can have code to send exactly what you need to Excel without a make table or even without saved queries.

Check it out and post back any questions. If you just go into the VBA window and then go to the first function (ExportQueryToExcelForCustomers)and put your cursor in and press RUN > RUN SUB/USER FORM it will run and create the files (make sure you have a folder of C:\Temp\ so the code will send the files there).

 

[graviz]

Here's a sample of mine which shows how you can have code to send exactly what you need to Excel without a make table or even without saved queries.

Check it out and post back any questions. If you just go into the VBA window and then go to the first function (ExportQueryToExcelForCustomers)and put your cursor in and press RUN > RUN SUB/USER FORM it will run and create the files (make sure you have a folder of C:\Temp\ so the code will send the files there).

Bob-

I attached your database and made a couple of changes. How would I change the code if I only want the query the names that are in the "New Customer" table. I don't want to change the orderes table (because that's similar to my sitution as there are more offices(customers in thie case) but I only need a few) I hope that makes sense.

 

[boblarson]

See the change in the SQL statement's FROM clause just under:


'####### CHANGE TABLE NAME TO WHAT YOU WANT ###################