Loop through current records to find null value in the Subform (1 Viewer)

Alhakeem1977

Registered User.
Local time
Today, 12:01
Joined
Jun 24, 2017
Messages
302
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.

Code:
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

I’m here to help
Staff member
Local time
Today, 01:01
Joined
Oct 29, 2018
Messages
13,239
Hi. Are you talking about making sure the subform is not empty?
 

Alhakeem1977

Registered User.
Local time
Today, 12:01
Joined
Jun 24, 2017
Messages
302
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

I’m here to help
Staff member
Local time
Today, 01:01
Joined
Oct 29, 2018
Messages
13,239
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

Registered User.
Local time
Today, 12:01
Joined
Jun 24, 2017
Messages
302
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

I’m here to help
Staff member
Local time
Today, 01:01
Joined
Oct 29, 2018
Messages
13,239
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

Registered User.
Local time
Today, 12:01
Joined
Jun 24, 2017
Messages
302
Yes, is it.

Code:
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

I’m here to help
Staff member
Local time
Today, 01:01
Joined
Oct 29, 2018
Messages
13,239
Yes, is it.

Code:
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

Super Moderator
Staff member
Local time
Today, 04:01
Joined
Feb 19, 2002
Messages
30,132
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:
INI:
Me.Parent.chkCompleteYN = ValidationCodeFunc()
 

Users who are viewing this thread

Top Bottom