MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Work order tracking

  Asked By: Lucas    Date: Mar 25    Category: MS Office    Views: 1435

I work for a small packaging company in the maintenance
department. currently our work orders,machine maintenance and
preventative maintenance are logged by hand into one of six separate
three ring binders. Needles to say this can be quite cumboursome if
one needs to find specific information easily,quickly or accurately.
If possible I would like to develope a workbook in excel to track all
of this information as a searchable database. People here are
familiar with useing excel and extreemly resistant to change if it
envolves something completely new.

I can see the following titles and discriptions as they would be used
in the spread sheet.

A:WO# required information automaticaly generated in succession as
information is added to a line once saved this information cannot be

B:Origination date required information autogenerated from current
date once saved this information cannot be changed

C: originator not required information

D: short discription 50 char or less required information canno be
changed once saved

E: assigned to/ completed by not required for origination but
required when WO is closed once saved cannot be changed

F: Machine not required unless PM or sanitation only specific entrys
accepted from a dropdown list once saved cannot be changed

G: Type not required unless PM or sanitation only specific entrys
accepted from a dropdown list PM SAN REP PRO SAF SUP HSK once saved
cannot be changed

H: Due date not required if entered cannot be changed once saved more
than a week green less than a week more than three days orange less
than three days red.

I: close Y performs final save and removes row from alteration

J: Date closed auto generate when col I=Y

K: time not required (yet) to keep track of hours spent on WO

L: Comments open to alteration untill closed used to notify and
record progress.

It has been suggested that Access would be better suited for this but
I am sure that I would be left to my writers cramp and the six
binders already in use.

I realize that a conflict may occour if more than one user tries to
access the workbook at the same time if there is no other solution
than to create a workorder sheet and email all entrys to one person
for data entry a macro could be written to search the sheet and input
the information from each individualy or in sucsession couldn't it.

Is the above possible? potentialy unstable? I have one more twist
that would be useful. For the PM's and Sanitation schedule there are
Weekly, Monthly, Quarterly, Semianual and Anual. Would it be possible
to autogenerate these as one was closed establishing a heirarchy so
that at the end of the year only the anual and not one of each was
included in the list of work orders to be performed. The information
in the comments section would also autogenerate according to
requirements for that type of WO.



1 Answer Found

Answer #1    Answered By: Sultana Tabassum     Answered On: Mar 25

Yes, it's possible, but not quite as easy as you think. There will be a lot
of coding. I've never done this in Excel, because I rarely need to use
Excel. But I've created many Word docs that have form fields or bookmarks
that I associate with an access  database. It actually works out great
because, as you already know, people are more apt to using the program they
already know.

To connect to an Access DB, you need only create  one and use ADO, ActiveX
Data Object to make a call to the DB, open  it and run code queries that
create a temporary RecordSet...from which you access your data. You can see
many samples/info of my projects doing this with Word here:

You'll just need to adapt the coding and design to Excel versus my samples
that I adapted to Word.

I do have a sample Word database  connectivity article out there with a free
code sample. So if you know ANYTHING about Word, you can download the sample
and get the idea and code to see how it works.

Then, rather than putting info in form fields, as I did in Word, you'll
connect the referenced data  to the cell name in your sheet. In other words,
when I say in Word to make the form field called "myBookmark1" equal to the
data in whatever field in Access...you would do a similar thing, except
you'd make it equal to day "B21."

You can find  my sample for Word w/code download to connect to an Access DB,
here: http://www.mousetrax.com/techpage.html#autoform Scroll down to the
PLEASE FILL OUT THIS FORM series and open article # 5 for Database

Then you can go here to get the connection string to use for Excel:

Didn't find what you were looking for? Find more on Work order tracking Or get search suggestion and latest updates.