Fortune and glory!
Private Sub btnRunAutoQuery_Click()
On Error GoTo btnRunAutoQuery_done
Dim SQLstring As String
Dim querytable As String
Dim numrecs As Integer
Dim Loopcount As Integer
Me.RecordSource = "AutoQueryList"
querytable = Me.RecordSource
DoCmd.SetWarnings False
' Set all Counts in the table to 0
SQLstring = "Update " & querytable & " Set Count = 0"
DoCmd.RunSQL SQLstring
' Get count of Queries to run.
numrecs = DCount("*", querytable)
' Go to first record in database.
DoCmd.GoToRecord , , acFirst
' Loop through all rows
For Loopcount = 1 To numrecs
Me!Count = DCount("*", Me!Name) ' Save query result count
DoCmd.GoToRecord , , acNext ' Go to next record
Next Loopcount
btnRunAutoQuery_done:
MsgBox "Check " & querytable & " table for results."
DoCmd.SetWarnings True
End Sub
Well, how would YOU have accomplished it? :-D
posted by Tim B. @ 3:49 PM
6 comments
6 Comments:
What is the Fortune and Glory about? Looks like a bunch of mumbo, jumbo to me.
Looks like mumbo jumbo to me too...
Was the solving of the problem the source of fortune and glory for you, maybe? In that event, woohoo!
It’s actually “automation”.
For over 21 years I made my living writing programs. Then I got laid off and found a new position which technically is not a programming job, but from time to time gives the opportunity to do some programming to solve problems (or in this case, automate tasks).
Last week I was asked to help on a database project. A number of reports were needed that would compare data to come up with a big list of difference between two systems. I wrote a demographic comparison “SQL query” and produced a spreadsheet of side-by-side differences, then added columns in Excel that compared pairs of data and put the word “NO” next to each pair of data (city, state, zip, etc) if the pair were different. This was a hit. Another lady on the team had a much more difficult task which required her to write 55 SQL queries.
A few days ago she asked me if I would run her queries and stroke a table with the results. The underlying data had been “refreshed” which required gathering the statistics again. The names of these queries were confusing to me and there was a definite order in which they had to be done. I did about half and was so confused that I gave up. The next day I wrote the Visual Basic program posted above.
I made a list of the query names and along with the list is a number that represents the number of the query (for which to run first, then next, and so on) and the resulting count from running the query. This works fabulously. Each query runs in order, requiring no user input. You click a button that runs the VB program and voila! She will have to run the queries perhaps once a week for a while, so it is a definite time saver.
It’s this type of stuff that saves time, will hopefully keep me employed, and gain me recognition in my new job.
All of this, makes no sense to me but I'm sure it does to you and the other employees.
If you keep on solving their problems and making it easier for the others, they will catch on to the talent you do have and keep you doing their problem solving for them.
I just hope the right people in the right jobs recognize what you are capable of doing and keep promoting you to the job you really want. Hopefully teaching people how to do their job more efficiently, and work smarter.
Are you kidding me??? I would have done it COMPLETELY differently! =)
Tarren, What would you have done?
:-)
Post a Comment
<< Home