Help Creating a loop

[cori25]

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

[ADezii]

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:

For intMacro = 1 To 4

[Minion]

You have 4 Databases, but the For...Next is being executed 5 times. Change to:

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 -

[ADezii]

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:

Function Delete()
Dim DbPath As String, db As Access.Application
Dim intMacro As Integer
 
For intMacro = 1 To 4
  Select Case intMacro
    Case 1 'Delete Glenn
      DbPath = "\\Stratford1\COMMON1\SHARED\DISPATCH\Overtime\Glen n Laudenslager\OverTime(Glenn).mdb"
      Set db = CreateObject("Access.Application")
        db.OpenCurrentDatabase (DbPath)
        db.Visible = True   'why?
        db.DoCmd.RunMacro ("DeleteInput")
          db.Quit
    Case 2 'Delete Heather
      DbPath = "\\Stratford1\COMMON1\SHARED\DISPATCH\Overtime\Heat her Foster\OverTime(Heather).mdb"
      Set db = CreateObject("Access.Application")
        db.OpenCurrentDatabase (DbPath)
        db.Visible = True   'why?
        db.DoCmd.RunMacro ("DeleteInput")
          db.Quit
  Case 3    'additional code needed here
  Case 4    'additional code needed here
  Case Else
    'drop thru code, won't happen, but
Next intMacro
End Function

[Jim Doherty]

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.

 
Function fn_DeleteInputs()
Dim db As New Access.Application
    Dim sharedpath As String, filename As String
    Dim strperson(1) As String
    'define the various elements of the zero based array
    strperson(0) = "Glenn Laudenslager"
    strperson(1) = "Heather Foster"
    'if and only if the folder location is fixed and the mdb file naming convention is consistent
    'then define a path commensurate with the person name and pin the name of the
    'mdb to it BUT ONLY if we can parse first name from surname and concatenate the file extension
    'Loop through the elements of the array during 'one' instance of the Access
    'application (resource preservation)and run the macro in each database.
    'Close off and quit after loop finishes
    For Each element In strperson
        sharedpath = "\\Stratford1\Common1\Shared\Dispatch\Overtime\" & element & "\"
        filename = "Overtime(" & Trim(left(element, InStr(element, " "))) & ").mdb"
        Debug.Print sharedpath & filename
        db.OpenCurrentDatabase (sharedpath & filename)
        db.DoCmd.RunMacro ("DeleteInput")
        db.CloseCurrentDatabase
    Next
    db.Quit
End Function
 

Regards

Jim :)

[ADezii]

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.

 
Function fn_DeleteInputs()
Dim db As New Access.Application
    Dim sharedpath As String, filename As String
    Dim strperson(1) As String
    'define the various elements of the zero based array
    strperson(0) = "Glenn Laudenslager"
    strperson(1) = "Heather Foster"
    'if and only if the folder location is fixed and the mdb file naming convention is consistent
    'then define a path commensurate with the person name and pin the name of the
    'mdb to it BUT ONLY if we can parse first name from surname and concatenate the file extension
    'Loop through the elements of the array during 'one' instance of the Access
    'application (resource preservation)and run the macro in each database.
    'Close off and quit after loop finishes
    For Each element In strperson
        sharedpath = "\\Stratford1\Common1\Shared\Dispatch\Overtime\" & element & "\"
        filename = "Overtime(" & Trim(left(element, InStr(element, " "))) & ").mdb"
        Debug.Print sharedpath & filename
        db.OpenCurrentDatabase (sharedpath & filename)
        db.DoCmd.RunMacro ("DeleteInput")
        db.CloseCurrentDatabase
    Next
    db.Quit
End Function
 

Regards

Jim :)

Nice catch, Jim, and more efficient too. Well, another one that went right over-my-head! (LOL).

[cori25]

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

[cori25]

Thanks so much! The loop ran perfectly!

Nice catch, Jim, and more efficient too. Well, another one that went right over-my-head! (LOL).