machine use hours

[eb1mom]

I am trying to replace a dry erase whiteboard with an
access form. Small very low tech family operated shop.
Machine hours are tracked for maintenance purposes. Each
machine has three squares taped out on board. Machine
name,number of hours and date. Each time a machine is run
the old hours and old dates are erased and new hours total
and date are written in. Some machines are used daily, some
weekly, some rarely. After required maintenance is done
total hours are erased. Updating the date field is not a
problem but sum of hours is giving me problems. I want to
create a "new hours box" so machinists would simply have to
enter new hours and total would be calculated. I tried to
use three text boxes one for total,one for sub total and
one to enter new hours used. Didn't work at all. Any
suggestions on how to create a text box to enter new hours
and then have it clear or erase? But still show total of
all hours that were entered. Any ideas on a better way to
approach this would be appreciated. Thank-you

 

[Guest]

I think you are trying to do too many things with one form, at least in terms of how Access utilizes them. The key issue you are fighting is trying to display and add multiple records at once while also displaying aggregated data.

The sum of total hours is an aggregate function, and from the looks of it you want to show all the machines at once. This would require a Continuous Forms view. And, since you are dealing with an aggregate function, the record set would not be editable

Adding a record for machine usage is an operation that would need to be done one at a time, and obviously the record set would need to be editable

Assuming the data table stores MachineID, DateWorked, and HoursWork fields, you could base a form on the aggregate query and display the results in continous forms. (I'm leaving out some detail here about getting the aggregate query to ignore the records prior to the last overhaul - you didn't really provide enough detail about how the information is stored for me to address that issue.

To add another Machine Hours data point, you might try adding a couple of unbound text boxes to the detail section (one to hold the new date (default = Date()?) and the other to hold the new Hours) and put a button labeled "Add" next to them. This button would have to be coded to read the data values from the unbound textboxes (and the MachineID field) and execute an APPEND to place the new data into the table (either by executing a stored query or by running some SQL programmatically)

There are probably a number of other approaches, too. Hopefully this will get you moving in the right direction

Rob

 

[eb1mom]

Yes I think I was trying to do too much on a single form.
I have been working with your suggestions and think I will
now be able to come up with a user friendly form. Executing
an append works for updating info and the other little bugs
can now be worked out. Thank-you for taking the time to
help me work this out. EB1mom

-----Original Message-----
I think you are trying to do too many things with one

form, at least in terms of how Access utilizes them. The
key issue you are fighting is trying to display and add
multiple records at once while also displaying aggregated
data.

The sum of total hours is an aggregate function, and from

the looks of it you want to show all the machines at once.
This would require a Continuous Forms view. And, since
you are dealing with an aggregate function, the record set
would not be editable.

Adding a record for machine usage is an operation that

would need to be done one at a time, and obviously the
record set would need to be editable.

Assuming the data table stores MachineID, DateWorked, and

HoursWork fields, you could base a form on the aggregate
query and display the results in continous forms. (I'm
leaving out some detail here about getting the aggregate
query to ignore the records prior to the last overhaul -
you didn't really provide enough detail about how the
information is stored for me to address that issue.)

To add another Machine Hours data point, you might try

adding a couple of unbound text boxes to the detail section
(one to hold the new date (default = Date()?) and the other
to hold the new Hours) and put a button labeled "Add" next
to them. This button would have to be coded to read the
data values from the unbound textboxes (and the MachineID
field) and execute an APPEND to place the new data into the
table (either by executing a stored query or by running
some SQL programmatically).

There are probably a number of other approaches, too.

Hopefully this will get you moving in the right direction.