Sub Form record search

[Xenix]

I have a problem. I have a main form for a work order database then a sub form with trays that are filled and going out to outworkers. I want to close the job when all the trays have a complete date but I do not know how to select the next record on the subform to check until end of records?

Can anyone help me?

This is the code I check in each record searched:


If IsNull(Forms!production!TrayDetailsSub3!CompleteDate) Then
Me.complete = False
x = False
'MsgBox "Sorry I can't do that until you enter the Actual Return Date"
Else
'Me.complete = True
x = True
End If
If Forms!production!TrayDetailsSub3!Reworked = True And IsNull(Forms!production!TrayDetailsSub3!ReworkReturn) Then
Me.complete = False
y = False
'MsgBox "Sorry I can't do that until the job has come back from rework and date is entered in Rework Return Date"
Else
y = True
End If
If x = True And y = True Then
Me.complete = True
Else
MsgBox "Sorry I can't do that until the return date has been completed"
End If
DoCmd.Requery
End Sub

Private Sub Form_Activate()
DoCmd.Maximize
Me.Refresh
End Sub

Private Sub Command26_Click()
On Error GoTo Err_Command26_Click

Dim stDocName As String

stDocName = "Production"
DoCmd.OpenReport stDocName, acNormal

Exit_Command26_Click:
Exit Sub

Err_Command26_Click:
MsgBox Err.Description
Resume Exit_Command26_Click
End Sub

I was thinking of using a bit of code like this wraped around the whole lot above:

DoCmd.GoToRecord "TrayDetailsSub3", acNext
with a Do..While. But I just can't get this to work

Thank you in advance
Mike

 

[Fizzio]

When do you want this to be checked and do you want the job automatically closed when all fields are complete?
You may have more success using a recordsetclone and checking for null values in the recordset then looping through the records that way.

 

[Xenix]

It would be nice to auto close when all records are complete yes

To close the work order I just set the field in the main form called "Complete" to true.

The only problem I have is when I run this branch of if statements above they only check the current subform record selected and not all the others there might or might not be


Thank you

Mike

 

[Fizzio]

I'm assuming here that you have a field for job closed in your jobs table and likewise on your main form. Try this on the after_update of the subform

Sub SubformName_After_Update()
Dim rst as dao.recordset 'DAO must be referenced in Tools/References in code window

set rst = me.recordsetclone
if rst.recordcount >0 then 'make sure that there are trays for a job
rst.movelast
rst.movefirst
Do until rst.eof
'check all fields here eg
if not isnull(rst![NameofField1],rst![NameofField2], etc)then
Forms!MainForm!JobClosed = True
end if
rst.movenext
loop
end if
set rst = nothing
end sub

I'm not 100% sure if the IsNull statement is correct though. If not, you cane use AND eg
if not isnull(rst![NameofField1]) AND not isnull(rst![NameofField2 and etc

 

[Xenix]

Thank you very much

Thank you Fizzio that works 100% perfect.
your one cool programmer

Thank you again
Mike

My final code for reference:
Private Sub Check22_AfterUpdate()
Dim x, y As Boolean
Dim rst As DAO.Recordset 'DAO must be referenced in Tools/References in code window

Set rst = Me.RecordsetClone
If rst.RecordCount > 0 Then 'make sure that there are trays for a job
rst.MoveLast
rst.MoveFirst
Do Until rst.EOF
'check all fields here eg
If IsNull(Forms!production!TrayDetailsSub3!CompleteDate) Then
Me.complete = False
x = False
'MsgBox "Sorry I can't do that until you enter the Actual Return Date"
Else
'Me.complete = True
x = True
End If
If Forms!production!TrayDetailsSub3!Reworked = True And IsNull(Forms!production!TrayDetailsSub3!ReworkReturn) Then
Me.complete = False
y = False
'MsgBox "Sorry I can't do that until the job has come back from rework and date is entered in Rework Return Date"
Else
y = True
End If
If x = True And y = True Then
Me.complete = True
Else
MsgBox "Sorry I can't do that until the return date has been completed"
End If
rst.MoveNext
Loop
End If
Set rst = Nothing
DoCmd.Requery
End Sub