Loop through current records to find null value in the Subform

[Alhakeem1977]

Hi all,
How can I loop through current records to find if the subform contains a null value in the data entry form then return an error message?

Please find below my code which works fine if I navigate to a record not contains a null value but for other records will not catch it while closing the form.

Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next
 If Status = 2 Or Status = 3 And Status <> 10 And IsNull([ImagesSubform].Form![Path]) Then
  Beep
   MsgBox "You must scan & attach the payment request first!", vbExclamation, "No Attachment"
  Cancel = True
End If
If Status = 1 And Status <> 10 And IsNull([ImagesSubform].Form![Path]) Then
  Beep
   If MsgBox("Are you sure you want to exit without dispatching the Payment(s) details?" & vbCrLf & _
    "As the status must be changed to (Submitted)", vbYesNo, "Exit Confirm") = vbNo Then
       Cancel = True
    Me.Status.SetFocus
   End If
End If
End Sub

Appreciate your kind help. Thanks in advance!

 

[theDBguy]

Hi. Are you talking about making sure the subform is not empty?

 

[Alhakeem1977]

Hi. Are you talking about making sure the subform is not empty?

Hello how are you?
Thanks for your prompt response, yes, that's what I want.

 

[theDBguy]

Hello how are you?
Thanks for your prompt response, yes, that's what I want.

Okay, then I would recommend using the Form's BeforeUpdate event to make sure the user cannot leave the current record without entering all the required information.

 

[Alhakeem1977]

Okay, then I would recommend using the Form's BeforeUpdate event to make sure the user cannot leave the current record without entering all the required information.

Unfortunately, it will be a bit difficult from the user perspective as he or she will be entering the records and gets references across multi-users, then will scan and upload with typed that references on the papers before the scanning process.
Is there a way to amend the code as Do While statement to loop through the subform?

 

[theDBguy]

Unfortunately, it will be a bit difficult from the user perspective as he or she will be entering the records and gets references across multi-users, then will scan and upload with typed that references on the papers before the scanning process.
Is there a way to amend the code as Do While statement to loop through the subform?

Is that code in the main form?

 

[Alhakeem1977]

Yes, is it.

Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next
 If Status = 2 Or Status = 3 And Status <> 10 And IsNull([ImagesSubform].Form![Path]) Then
  Beep
   MsgBox "You must scan & attach the payment request first!", vbExclamation, "No Attachment"
  Cancel = True
End If
If Status = 1 And Status <> 10 And IsNull([ImagesSubform].Form![Path]) Then
  Beep
   If MsgBox("Are you sure you want to exit without dispatching the Payment(s) details?" & vbCrLf & _
    "As the status must be changed to (Submitted)", vbYesNo, "Exit Confirm") = vbNo Then
       Cancel = True
    Me.Status.SetFocus
   End If
End If
End Sub

 

[theDBguy]

Yes, is it.

Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next
If Status = 2 Or Status = 3 And Status <> 10 And IsNull([ImagesSubform].Form![Path]) Then
  Beep
   MsgBox "You must scan & attach the payment request first!", vbExclamation, "No Attachment"
  Cancel = True
End If
If Status = 1 And Status <> 10 And IsNull([ImagesSubform].Form![Path]) Then
  Beep
   If MsgBox("Are you sure you want to exit without dispatching the Payment(s) details?" & vbCrLf & _
    "As the status must be changed to (Submitted)", vbYesNo, "Exit Confirm") = vbNo Then
       Cancel = True
    Me.Status.SetFocus
   End If
End If
End Sub

Okay then, from the main form, you can loop through the subform records by using a RecordsetClone of the Subform object. For example:

Set rs = Me.SubformControlName.Form.RecorsetClone

Then just loop through the records in the rs object.

 

[Pat Hartman]

It is wrong to validate individual records this way. Validation of individual records should be done in the subform's BeforeUpdate event. Do not allow the record to be saved if it has invalid or missing values.

In the case of the main form, the issue is more complex. Again, you have to validate the data fields of the main form in the main form's BeforeUpdate event but if one of the business rules is that there must be at least one subform record or the subform amousts must total to the amount stored in the main record for the batch, etc. Then you should have a CompleteYN field on the main form. ALL your queries except the one for the update form should EXCLUDE records where Complete = No (No should be the default).

The BeforeUpdate event of the subform is where your "completeness" check has to run. In the AfterUpdate event of the subform, run a query that counts the subform records or sums them or whatever you want to do. If the criteria matches what you want, change the value of the complete flag in the mainform record. Although you run the validation routine from the subform's AfterUpdate event, it will be simpler to update the main form if you put the code into a function that returns either True or False depending on whether the criteria is satisfied or not. Then the code to update the main form record would be:

Me.Parent.chkCompleteYN = ValidationCodeFunc()