By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,079 Members | 1,338 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,079 IT Pros & Developers. It's quick & easy.

Help Creating a loop

cori25
P: 83
I have 4 database's that I need to enter and run a macro in. The macro in each db is named the same. I am having difficulty creating a loop to go into one db run the macro exit and then go into another db run the macro exit, etc...

Any help appreciated


Function Delete()

Dim DbPath$
Dim intMacro As Integer

For intMacro = 0 To 4

Select Case intMacro

Case 0 'Delete Glenn
DbPath = "\\Stratford1\COMMON1\SHARED\DISPATCH\Overtime\Gle nn Laudenslager\OverTime(Glenn).mdb"

Dim db As New Access.Application
db.OpenCurrentDatabase (DbPath)
db.Visible = True
db.DoCmd.RunMacro ("DeleteInput")
db.Quit

Next intMacro

Case 1 'Delete Heather
DbPath = "\\Stratford1\COMMON1\SHARED\DISPATCH\Overtime\Hea ther Foster\OverTime(Heather).mdb"

db.OpenCurrentDatabase (DbPath)
db.Visible = True
db.DoCmd.RunMacro ("DeleteInput")
db.Quit

Next intMacro
Feb 7 '08 #1
Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,679
I have 4 database's that I need to enter and run a macro in. The macro in each db is named the same. I am having difficulty creating a loop to go into one db run the macro exit and then go into another db run the macro exit, etc...

Any help appreciated


Function Delete()

Dim DbPath$
Dim intMacro As Integer

For intMacro = 0 To 4

Select Case intMacro

Case 0 'Delete Glenn
DbPath = "\\Stratford1\COMMON1\SHARED\DISPATCH\Overtime\Gle nn Laudenslager\OverTime(Glenn).mdb"

Dim db As New Access.Application
db.OpenCurrentDatabase (DbPath)
db.Visible = True
db.DoCmd.RunMacro ("DeleteInput")
db.Quit

Next intMacro

Case 1 'Delete Heather
DbPath = "\\Stratford1\COMMON1\SHARED\DISPATCH\Overtime\Hea ther Foster\OverTime(Heather).mdb"

db.OpenCurrentDatabase (DbPath)
db.Visible = True
db.DoCmd.RunMacro ("DeleteInput")
db.Quit

Next intMacro
You have 4 Databases, but the For...Next is being executed 5 times. Change to:
Expand|Select|Wrap|Line Numbers
  1. For intMacro = 1 To 4
Feb 9 '08 #2

Minion
Expert 100+
P: 108
You have 4 Databases, but the For...Next is being executed 5 times. Change to:
Expand|Select|Wrap|Line Numbers
  1. For intMacro = 1 To 4
Good catch. Also make sure that when you change your loop from 1 to 4 (or 0 to 3) that you make sure to adjust your Select Case to match.

- Minion -
Feb 9 '08 #3

ADezii
Expert 5K+
P: 8,679
I have 4 database's that I need to enter and run a macro in. The macro in each db is named the same. I am having difficulty creating a loop to go into one db run the macro exit and then go into another db run the macro exit, etc...

Any help appreciated


Function Delete()

Dim DbPath$
Dim intMacro As Integer

For intMacro = 0 To 4

Select Case intMacro

Case 0 'Delete Glenn
DbPath = "\\Stratford1\COMMON1\SHARED\DISPATCH\Overtime\Gle nn Laudenslager\OverTime(Glenn).mdb"

Dim db As New Access.Application
db.OpenCurrentDatabase (DbPath)
db.Visible = True
db.DoCmd.RunMacro ("DeleteInput")
db.Quit

Next intMacro

Case 1 'Delete Heather
DbPath = "\\Stratford1\COMMON1\SHARED\DISPATCH\Overtime\Hea ther Foster\OverTime(Heather).mdb"

db.OpenCurrentDatabase (DbPath)
db.Visible = True
db.DoCmd.RunMacro ("DeleteInput")
db.Quit

Next intMacro
I made a couple of changes, try this code and see what happens:
Expand|Select|Wrap|Line Numbers
  1. Function Delete()
  2. Dim DbPath As String, db As Access.Application
  3. Dim intMacro As Integer
  4.  
  5. For intMacro = 1 To 4
  6.   Select Case intMacro
  7.     Case 1 'Delete Glenn
  8.       DbPath = "\\Stratford1\COMMON1\SHARED\DISPATCH\Overtime\Glen n Laudenslager\OverTime(Glenn).mdb"
  9.       Set db = CreateObject("Access.Application")
  10.         db.OpenCurrentDatabase (DbPath)
  11.         db.Visible = True   'why?
  12.         db.DoCmd.RunMacro ("DeleteInput")
  13.           db.Quit
  14.     Case 2 'Delete Heather
  15.       DbPath = "\\Stratford1\COMMON1\SHARED\DISPATCH\Overtime\Heat her Foster\OverTime(Heather).mdb"
  16.       Set db = CreateObject("Access.Application")
  17.         db.OpenCurrentDatabase (DbPath)
  18.         db.Visible = True   'why?
  19.         db.DoCmd.RunMacro ("DeleteInput")
  20.           db.Quit
  21.   Case 3    'additional code needed here
  22.   Case 4    'additional code needed here
  23.   Case Else
  24.     'drop thru code, won't happen, but
  25. Next intMacro
  26. End Function
Feb 9 '08 #4

Jim Doherty
Expert 100+
P: 897
If you're doing what I think your are doing it occurs to me that you have a consistency of folder path and a naming convention for your mdb files that basically provides one overtime mdb per person.

Have a look at this merely as 'food for thought' in that if you get any more people added to the overtime path folder having their own mdb file then simply adding their name to the array and resizing it will provide for examining future mdb files and running the macro in each one of their mdb files as well.

You can enhance this routine of course , but no point unless you have a variable number of people etc etc...error handling needs addressing too.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Function fn_DeleteInputs()
  3. Dim db As New Access.Application
  4.     Dim sharedpath As String, filename As String
  5.     Dim strperson(1) As String
  6.     'define the various elements of the zero based array
  7.     strperson(0) = "Glenn Laudenslager"
  8.     strperson(1) = "Heather Foster"
  9.     'if and only if the folder location is fixed and the mdb file naming convention is consistent
  10.     'then define a path commensurate with the person name and pin the name of the
  11.     'mdb to it BUT ONLY if we can parse first name from surname and concatenate the file extension
  12.     'Loop through the elements of the array during 'one' instance of the Access
  13.     'application (resource preservation)and run the macro in each database.
  14.     'Close off and quit after loop finishes
  15.     For Each element In strperson
  16.         sharedpath = "\\Stratford1\Common1\Shared\Dispatch\Overtime\" & element & "\"
  17.         filename = "Overtime(" & Trim(left(element, InStr(element, " "))) & ").mdb"
  18.         Debug.Print sharedpath & filename
  19.         db.OpenCurrentDatabase (sharedpath & filename)
  20.         db.DoCmd.RunMacro ("DeleteInput")
  21.         db.CloseCurrentDatabase
  22.     Next
  23.     db.Quit
  24. End Function
  25.  
Regards

Jim :)
Feb 10 '08 #5

ADezii
Expert 5K+
P: 8,679
If you're doing what I think your are doing it occurs to me that you have a consistency of folder path and a naming convention for your mdb files that basically provides one overtime mdb per person.

Have a look at this merely as 'food for thought' in that if you get any more people added to the overtime path folder having their own mdb file then simply adding their name to the array and resizing it will provide for examining future mdb files and running the macro in each one of their mdb files as well.

You can enhance this routine of course , but no point unless you have a variable number of people etc etc...error handling needs addressing too.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Function fn_DeleteInputs()
  3. Dim db As New Access.Application
  4.     Dim sharedpath As String, filename As String
  5.     Dim strperson(1) As String
  6.     'define the various elements of the zero based array
  7.     strperson(0) = "Glenn Laudenslager"
  8.     strperson(1) = "Heather Foster"
  9.     'if and only if the folder location is fixed and the mdb file naming convention is consistent
  10.     'then define a path commensurate with the person name and pin the name of the
  11.     'mdb to it BUT ONLY if we can parse first name from surname and concatenate the file extension
  12.     'Loop through the elements of the array during 'one' instance of the Access
  13.     'application (resource preservation)and run the macro in each database.
  14.     'Close off and quit after loop finishes
  15.     For Each element In strperson
  16.         sharedpath = "\\Stratford1\Common1\Shared\Dispatch\Overtime\" & element & "\"
  17.         filename = "Overtime(" & Trim(left(element, InStr(element, " "))) & ").mdb"
  18.         Debug.Print sharedpath & filename
  19.         db.OpenCurrentDatabase (sharedpath & filename)
  20.         db.DoCmd.RunMacro ("DeleteInput")
  21.         db.CloseCurrentDatabase
  22.     Next
  23.     db.Quit
  24. End Function
  25.  
Regards

Jim :)
Nice catch, Jim, and more efficient too. Well, another one that went right over-my-head! (LOL).
Feb 10 '08 #6

cori25
P: 83
Thanks for the advise, will try and see how it goes!
Nice catch, Jim, and more efficient too. Well, another one that went right over-my-head! (LOL).
Feb 11 '08 #7

cori25
P: 83
Thanks so much! The loop ran perfectly!

Nice catch, Jim, and more efficient too. Well, another one that went right over-my-head! (LOL).
Feb 11 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.