update equipment mileage or hours

[fleetman]

Hi, all:

I am new to Access2007 and I need some guidance on a data integrity and time saving idea.

I have a 120 day old data base that I use to track fleet maintenance. There are tables for Vendors, Parts, Vehicles and workorders. Currently I am updating field manually in each table. after entering 278 work orders I would like to make the program more efficient.

The actions needed to do this are: The mileage or hours are entered on the work order on each repair or maintenence. When the work order is closed it ( a button field segregate open work orders from closed ones) would be real handy if the mileage or hours fields in the vehicle master were updated. (Along the same lines it would be if adl (PM_A date last done) and aul (PM_A miles or hours last done)

tbl UNITS
tbl WORKORDERS
frm workorders

thanks..

John(Fleetman)

 

[JamesMcS]

You can make a button run a query, and in the query you can refer to controls on the form as your criteria, would that be any good?

 

[fleetman]

Thanks for the advise. I am so new, I'll have figure out what you mean and then figure out how to write/test it.

 

[JamesMcS]

Good luck! My suggestions would be:

segregate open work orders from closed ones

I wouldn't do this, but leave all WO in one table, with a field to say 'closed' or 'not closed' (a yes/no field would be good for this (unless you've got stages of closed-ness, in which case a number field might be more appropriate - 1pen, 2:almost closed, 3:closed or whatever))

would be real handy if the mileage or hours fields in the vehicle master were updated

an update query would do this no problems.

If you're doing this with a command button, look up docmd.runquery and docmd.runsql in the Access VBA help.

 

[fleetman]

Hi James:

All work orders are in the same table. The closed work order button is a yes know field. I have two different menu buttons 1 that opens only open workorders y/n=n and a second that opens only closed workorders y/n=y

This saves time processing open work orders. old DB has 21000 closed work orders this new one will also some day.

I have learned some "easy" parts of access quickly. I guess I am a slow hard part learner. It seems to me that once the mileage data is entered in work order table it should be possible to update the units table. The reason I need to update the units table is that the preventive maintnance scheduling is calculated from the last service date and mileage and the "current" mileage that is collected from the PetroVend fuel management system. Getting access to read the mileage data in the PVdata file was simple.

I have ordered a book and as soon as I recieve it I work on this problem until I solve it.

thanks..

John.

 

[JamesMcS]

Have a look at update queries in the book or in the help file and see if that's what you need. If you intend to create a new record with the updated mileage you'll want an append query.

 

[fleetman]

Well the book arrived. VBA Programming for Microsoft Office Project. The 1st ninty pages I think I can use. I am not sure about the other 360 pages though.

Rod Gill is the author.

After my nap I will begin reading it. I'll figure this out one way or the other.