G
Garret
Hello,
I've been going in circles and out of my mind in the last few days
trying to figure this out, so I'm now kindly asking for any assistance
that you can offer. The following is long and somewhat descriptive,
but it is clear if you read it all and not too difficult to understand.
Here is the situation:
I have tblMachines with fields :
MachineID (PK), Department, some other irrelevent fields
I have tblMaintenanceInfo with fields:
MaintenanceNo (PK), MachineID, Task, DateCompleted, CompletedBy,
Interval
tblMachines and tblMaintenanceInfo have a One-Many Relationship
connected by MachineID.
That means, that there are Machines. These Machines have to be
maintenanced, and the tblMaintenanceInfo is the list of Tasks that go
for each machine. These Tasks can be completed on a date
(DateCompleted) by a person (CompletedBy). Interval is a numeric field
that tells how many months there are between maintenance tasks. I have
a form where the form is based on tblMachines and a subform where that
is based on tblMaintenanceInfo. All fine and dandy up to here.
Now, I wanted to add a Yes/No feature that signified whether the Task
needed to be done as of today or not (ex: Task was done 1/1/2006, with
4 month interval, and since today is 8/2/2006 it is far overdue for a
maintenance. So a checkbox on the form would be unchecked, whereas the
opposite of this situation would have a checked checkbox). After much
research, I found I could do this with a Calculated Field in a Query.
So I made a Query, qryMaintenanceInfo, with the calculated field
UpToDate.
UptoDate: (Date()<=(DateAdd("m",[Interval],[LastCompleted])))
I also added added all fields from tblMaintenanceInfo to this so I
could use this query in place of the table for the
frmMaintenanceInfoSubform's Record Source to be based on. UpToDate is
now a checkbox on the form that is checked depending on the Interval
and the DateCompleted. Good. Here is where the problems come in.
1) When I go to create a new Record in the subform (new Task of
maintenance), it gives me the error, "Field Cannot be Updated". This
is due to the fact that Interval and DateCompleted are blank, so
UpToDate cannot do the calculation yet until those fields are filled
it. I don't see a way of how to fix this problem.
2) I need a report that shows a machine name and its list of Tasks that
are not UpToDate so the report can be given to a worker to do those
Tasks. This report is based on the qryMaintenanceInfo so it can use
UpToDate field. However, the qryMaintenanceInfo only contains all the
fields from tblMaintenanceInfo and UpToDate, and it needs information
from tblMachines to display the MachineID and Department. So I added
all fields from tblMachines to the query, but now I also get the
following error when I try to add a new record to the subform (in
addition to the "Field cannot be updated"):
"The object doesn't contain the Automation object 'tblMaintenanceInfo'
You tried to run a Visual Basic procedure to set a property or method
for an object. However, the component doesn't make the property or
method available for Automation operations.
Check the component's documentation for information on the properties
or methods it makes available for Automation operations."
I have no idea what this means, but it now new records in the subform
are not "attatched" to the Machine on the main form (I discovered this
by added the MachineID field, usually a hidden field, onto the
subform).
So!...I tried to solve this problem by creating a new query purposely
for solely the report, being just like the other query except
containing all fields from tblMachines. This is my code that opens the
report:
DoCmd.OpenReport "rptMaintenance", acViewPreview, ,
"tblMachines.MachineID = '" & Forms!frmMaintenance!MachineID & "' AND
qryMaintenanceReport.UptoDate = 0"
Now it opens fine, but the checkboxes on the form are greyed out...
This isn't a big deal but it might be nice to have empty white ones so
that people using the printed report can check off the task when it is
done. I just realized this is because I have the UptoDate checkbox on
the subform locked, so that it can only be checked/unchecked due to the
calculated field instead of manually. Also, this seems like a big mess
using all these queries and loopholes, so if anyone knows a cleaner way
I'm all for it.
Thanks for reading! I really appreciate it if you can help me.
I've been going in circles and out of my mind in the last few days
trying to figure this out, so I'm now kindly asking for any assistance
that you can offer. The following is long and somewhat descriptive,
but it is clear if you read it all and not too difficult to understand.
Here is the situation:
I have tblMachines with fields :
MachineID (PK), Department, some other irrelevent fields
I have tblMaintenanceInfo with fields:
MaintenanceNo (PK), MachineID, Task, DateCompleted, CompletedBy,
Interval
tblMachines and tblMaintenanceInfo have a One-Many Relationship
connected by MachineID.
That means, that there are Machines. These Machines have to be
maintenanced, and the tblMaintenanceInfo is the list of Tasks that go
for each machine. These Tasks can be completed on a date
(DateCompleted) by a person (CompletedBy). Interval is a numeric field
that tells how many months there are between maintenance tasks. I have
a form where the form is based on tblMachines and a subform where that
is based on tblMaintenanceInfo. All fine and dandy up to here.
Now, I wanted to add a Yes/No feature that signified whether the Task
needed to be done as of today or not (ex: Task was done 1/1/2006, with
4 month interval, and since today is 8/2/2006 it is far overdue for a
maintenance. So a checkbox on the form would be unchecked, whereas the
opposite of this situation would have a checked checkbox). After much
research, I found I could do this with a Calculated Field in a Query.
So I made a Query, qryMaintenanceInfo, with the calculated field
UpToDate.
UptoDate: (Date()<=(DateAdd("m",[Interval],[LastCompleted])))
I also added added all fields from tblMaintenanceInfo to this so I
could use this query in place of the table for the
frmMaintenanceInfoSubform's Record Source to be based on. UpToDate is
now a checkbox on the form that is checked depending on the Interval
and the DateCompleted. Good. Here is where the problems come in.
1) When I go to create a new Record in the subform (new Task of
maintenance), it gives me the error, "Field Cannot be Updated". This
is due to the fact that Interval and DateCompleted are blank, so
UpToDate cannot do the calculation yet until those fields are filled
it. I don't see a way of how to fix this problem.
2) I need a report that shows a machine name and its list of Tasks that
are not UpToDate so the report can be given to a worker to do those
Tasks. This report is based on the qryMaintenanceInfo so it can use
UpToDate field. However, the qryMaintenanceInfo only contains all the
fields from tblMaintenanceInfo and UpToDate, and it needs information
from tblMachines to display the MachineID and Department. So I added
all fields from tblMachines to the query, but now I also get the
following error when I try to add a new record to the subform (in
addition to the "Field cannot be updated"):
"The object doesn't contain the Automation object 'tblMaintenanceInfo'
You tried to run a Visual Basic procedure to set a property or method
for an object. However, the component doesn't make the property or
method available for Automation operations.
Check the component's documentation for information on the properties
or methods it makes available for Automation operations."
I have no idea what this means, but it now new records in the subform
are not "attatched" to the Machine on the main form (I discovered this
by added the MachineID field, usually a hidden field, onto the
subform).
So!...I tried to solve this problem by creating a new query purposely
for solely the report, being just like the other query except
containing all fields from tblMachines. This is my code that opens the
report:
DoCmd.OpenReport "rptMaintenance", acViewPreview, ,
"tblMachines.MachineID = '" & Forms!frmMaintenance!MachineID & "' AND
qryMaintenanceReport.UptoDate = 0"
Now it opens fine, but the checkboxes on the form are greyed out...
This isn't a big deal but it might be nice to have empty white ones so
that people using the printed report can check off the task when it is
done. I just realized this is because I have the UptoDate checkbox on
the subform locked, so that it can only be checked/unchecked due to the
calculated field instead of manually. Also, this seems like a big mess
using all these queries and loopholes, so if anyone knows a cleaner way
I'm all for it.
Thanks for reading! I really appreciate it if you can help me.