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
7 1973 ADezii 8,834
Recognized Expert Expert
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: Minion 108
Recognized Expert New Member
You have 4 Databases, but the For...Next is being executed 5 times. Change to:
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 8,834
Recognized Expert Expert
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: -
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
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 8,834
Recognized Expert Expert
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).
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).
Thanks so much! The loop ran perfectly!
Nice catch, Jim, and more efficient too. Well, another one that went right over-my-head! (LOL).
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: ArShAm |
last post by:
Hi there
Please help me to optimize this code for speed
I added /O2 to compiler settings
I added /Oe to compiler settings for accepting register type request , but
it seems that is not allowed and if I remove register type for "l" , time of
generating codes doesn't change
the original code makes some files , but I removed that section to make it
simple for you to read
please help me to optimize it for faster running
|
by: Dom Boyce |
last post by:
Hi
First up, I am using MS Access 2002.
I have a database which records analyst rating changes for a list of
companies on a daily basis. Unfortunately, the database has been set
up (by my predecessor, I hasten to add) so that each day it creates a
copy of the record for each company, changes the date to today's date,
and prompts the user for any changes of ratings on that day. The
resulting data table grows by approx 600 records per...
|
by: Sarah |
last post by:
Hi,
I have an Access database of about 1000 records. I am trying to use
asp to loop through the record set one at a time, creating the html
code for a web page. I would like to save the actual html output as a
html file and then loop through it again creating a new web page and
saving it, etc...
I have web pages that compare prices of products from on-line stores.
These prices change weekly. I need a quick way to be able to update
|
by: steven |
last post by:
I am creating some financial reports in Access 2000, and I am having
problem that I can't resolve.
The report sorts information first by vendor, then by budget line
number. It generates the information I need accurately, but I am
having problems with the layout.
-The budget line header contains information about the vendor--it looks
great.
-The budget line footer contains the sum, and I am not having any
problems there.
|
by: Jason |
last post by:
Hi,
I was wondering if any could point me to an example or give me ideas on how
to dynamically create a form based on a database table? So, I would have a
table designed to tell my application to create certain textboxes, labels,
and combo boxes? Any ideas would be appreciated.
Thanks
| |
by: Tarun Mistry |
last post by:
Hi all, I have posted this in both the c# and asp.net groups as it applies
to both (apologies if it breaks some group rules).
I am making a web app in asp.net using c#. This is the first fully OO
application I will be making, also my first .NET application, so im looking
for any help and guidance.
Ok, my problems are todo with object and database abstraction, what should i
do.
|
by: mslyman |
last post by:
Hi,
I could do with some help. I have this XML.
<region>
<region_code>567</region_code>
<store>
<store_code>345</store_code>
<dept>
<dept_code>32</dept_code>
</dept>
|
by: kberry |
last post by:
I am clearing Textboxes on a form... this is loop I have came up with
but was wondering if it can be shorter or not as long... Can anyone
help?
Dim controlOnForm As Control 'Places a control on the form
Dim controlOnTab As Control 'Places a control on the tab
Dim controlTabPage As Control 'Places a control on the tab
page
Dim controlGroupBox As Control 'Places a control on the group
box
|
by: gunimpi |
last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431
********************************************************
VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help
wanted
********************************************************
For this teeny job, please refer to:
http://feeds.reddit.com/feed/8fu/?o=25
|
by: bcap |
last post by:
Hello,
I really would apprciate help! =)
What I want to do is be able to change the status of mulitple records
using a drop down and a checkbox.
I have a drop down called "ChangeStatus" with the values to change the
status to Pending, Accepted, Declined, Cancelled, Completed.
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |