Checking a field on a subform when changing a field on the main form. (1 Viewer)

[Dwight Todd]

I have a database that is used to track work assignments. There is a field on the main form indicating the status of a work assignment. When the work assignment status is changed to 'Closed' I want to check a subform that contains all of the employees that have worked on the assignment to make sure that they have completed their task of the assignment before the work order can be closed. The employees have a check box as a field that is checked when they have completed their task. Inadvertently these employees are not checking the box and therefore the manager of the database is closing the work order before all of the check boxes have been checked. Can anybody help with a solution to check that all of the employees check boxes on the subform have been selected before a work order status can be set to 'Closed'.

Thanks

 

[BarkerD]

Here is one solution you may be able to use.

on the OnClick event for the checkbox, add the following code.

if me.checkboxname.value=true then
else

dim strSQL as string
dim db as DAO.Database
dim rcd as DAO.Recordset

'Use the fielname that is the join criteria for your subform.
strSQL="SELECT * FROM [SubformTableName] WHERE [subformtablefieldname] = " & [mainformcontrolname] & ";"

set db=currentdb
set rcd=db.openrecordset(strSQL)

'This should return a recordset of all employee work records that match the Work Order Number.

'Now you need to loop through the recordset and check to make certain that all the tasks are complete.

dim intAllChecked as integer

do until rcd.eof
if rcd![tableCheckBoxName].value=true then

else
intAllChecked=false
endif
rcd.movenext

loop

rcd.close

'Destroy objects
set rcd=nothing
set db=nothing

'If all the boxes were checked, then continue with checking box
if intAllChecked = true then
else
docmd.cancelevent
msgbox$("You can't check box. Not all Employee Work Assignments are completed")
endif
endif

This procedure should work for you. If you have any problems, give me a line

Regards.
Duane Barker



[This message has been edited by BarkerD (edited 03-08-2000).]