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\C OMMON1\SHARED\D ISPATCH\Overtim e\Glenn Laudenslager\Ov erTime(Glenn).m db"

Dim db As New Access.Applicat ion
db.OpenCurrentD atabase (DbPath)
db.Visible = True
db.DoCmd.RunMac ro ("DeleteInpu t")
db.Quit

Next intMacro

Case 1 'Delete Heather
DbPath = "\\Stratford1\C OMMON1\SHARED\D ISPATCH\Overtim e\Heather Foster\OverTime (Heather).mdb"

db.OpenCurrentD atabase (DbPath)
db.Visible = True
db.DoCmd.RunMac ro ("DeleteInpu t")
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\C OMMON1\SHARED\D ISPATCH\Overtim e\Glenn Laudenslager\Ov erTime(Glenn).m db"

Dim db As New Access.Applicat ion
db.OpenCurrentD atabase (DbPath)
db.Visible = True
db.DoCmd.RunMac ro ("DeleteInpu t")
db.Quit

Next intMacro

Case 1 'Delete Heather
DbPath = "\\Stratford1\C OMMON1\SHARED\D ISPATCH\Overtim e\Heather Foster\OverTime (Heather).mdb"

db.OpenCurrentD atabase (DbPath)
db.Visible = True
db.DoCmd.RunMac ro ("DeleteInpu t")
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\C OMMON1\SHARED\D ISPATCH\Overtim e\Glenn Laudenslager\Ov erTime(Glenn).m db"

Dim db As New Access.Applicat ion
db.OpenCurrentD atabase (DbPath)
db.Visible = True
db.DoCmd.RunMac ro ("DeleteInpu t")
db.Quit

Next intMacro

Case 1 'Delete Heather
DbPath = "\\Stratford1\C OMMON1\SHARED\D ISPATCH\Overtim e\Heather Foster\OverTime (Heather).mdb"

db.OpenCurrentD atabase (DbPath)
db.Visible = True
db.DoCmd.RunMac ro ("DeleteInpu t")
db.Quit

Next intMacro

I made a couple of changes, try this code and see what happens:
[CODE=vb]
Function Delete()
Dim DbPath As String, db As Access.Applicat ion
Dim intMacro As Integer

For intMacro = 1 To 4
Select Case intMacro
Case 1 'Delete Glenn
DbPath = "\\Stratford1\C OMMON1\SHARED\D ISPATCH\Overtim e\Glen n Laudenslager\Ov erTime(Glenn).m db"
Set db = CreateObject("A ccess.Applicati on")
db.OpenCurrentD atabase (DbPath)
db.Visible = True 'why?
db.DoCmd.RunMac ro ("DeleteInpu t")
db.Quit
Case 2 'Delete Heather
DbPath = "\\Stratford1\C OMMON1\SHARED\D ISPATCH\Overtim e\Heat her Foster\OverTime (Heather).mdb"
Set db = CreateObject("A ccess.Applicati on")
db.OpenCurrentD atabase (DbPath)
db.Visible = True 'why?
db.DoCmd.RunMac ro ("DeleteInpu t")
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[/CODE]

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