Mandatory fields on subform (1 Viewer)

[Chumpalot]

Hi guys,

I've built a form that lets users enter generic details for a purchase order (Customer name, description etc). Within this form is a continuous sub-form where product lines are entered.

On clicking the Save & Close button I'd like to check for mandatory fields. I have managed to get this working successfully for the fields on the main form. I also have it working for the fields on the sub-form, however, when I enter a product line in the continuous form the next row is displayed and I cannot exit the form as it picks up the blank fields.

How can I get around this? I'd like for the main form to have at least ONE record entered into the sub-form before a user is allowed to close it.

Thanks very much
David

-------------
edit

Here is the code I'm using to check the fields:

Private Sub btncloseform_Click()
   Dim txtMessage As String
  On Error GoTo Err_btnSave_Click
    ' init error message
    txtMessage = ""
    ' Check fields in reverse order to set focus to the first
    If Not Len(Nz(Me.Customer)) > 0 Then
        txtMessage = "'Customer' cannot be left blank" & vbCrLf
        Me.lblmandatory.Visible = True
        Me.Customer.SetFocus
    Else
        Me.Customer.BackColor = vbWhite
    End If
    
    If Not Len(Nz(Me.EventDescription)) > 0 Then
        txtMessage = "'Event Description' cannot be left blank" & vbCrLf
        Me.lblmandatory1.Visible = True
        Me.EventDescription.SetFocus
    Else
        Me.EventDescription.BackColor = vbWhite
    End If
   
   If Not Len(Nz(Forms![frm_consumption]![Child15].Form![ProdID])) > 0 Then
       txtMessage = "Please enter some product lines" & vbCrLf
       Forms![frm_consumption]![Child15].Form![lblmandatory2].Visible = True
       'Forms![frm_consumption]![Child15].Form![ProdID].SetFocus
      Else
       Me.EventDescription.BackColor = vbWhite
      End If
      
        If Not Len(Nz(Forms![frm_consumption]![Child15].Form![TransQty])) = 0 Then
       txtMessage = "Please enter a Quantity" & vbCrLf
       Forms![frm_consumption]![Child15].Form![lblmandatory3].Visible = True
       'Forms![frm_consumption]![Child15].Form![ProdID].SetFocus
      Else
       Me.EventDescription.BackColor = vbWhite
      End If
      
         If Not Len(Nz(Forms![frm_consumption]![Child15].Form![CostPerItem])) = 0 Then
       txtMessage = "Please enter a Cost Per Item" & vbCrLf
       Forms![frm_consumption]![Child15].Form![lblmandatory4].Visible = True
       'Forms![frm_consumption]![Child15].Form![ProdID].SetFocus
      Else
       Me.EventDescription.BackColor = vbWhite
      End If
    
          ' Check error found
    If Len(txtMessage) > 0 Then
        MsgBox txtMessage
        Exit Sub
    End If
    
    DoCmd.Close
 
Exit_btnSave_Click:
    Exit Sub
 
Err_btnSave_Click:
    MsgBox Err.Description
    Resume Exit_btnSave_Click
End Sub

 

[Minty]

You should count the number of sub form records. If <1 then you don't have a order line.
On your sub form you can check if you are on a new record by using the form's Current event to check for .NewRecord property

If me.NewRecord then
'now we at new record
end if

 

[Chumpalot]

Thank you for your reply.

I am unsure on this. The issue isn't with whether there isn't a subform record, it's to do with when I select a combo box value a second row is generated in the continuous subform. These are then included in the field checker code above.

Apologies, I probably wasn't very clear in my original post.

Thanks

 

[Chumpalot]

Do you mean something like this...?

if isNull(Forms!SubOne!somefield) then
action...
else
action..
end if

 

[Minty]

Sorry - I think I've not grasped your question correctly. I think you are on the correct track with your second if isnull(somethingon the sub form) process.
Can I ask why you would have the combo box on the main form insert something into the sub form? My logical process is struggling with that bit?

 

[Chumpalot]

Please see the attached, hopefully this clears it up.

The fields marked with the asterisk are the fields I'd like to check. When I enter a product name another row is inserted (a characteristic of the cont. form). This new row is then checked but I don't want it to be. As long as there is 1 row in the subform (Product name + Qty + Price) then the user is allowed to close the form down.

Thanks again
David

 

[Minty]

Okay - I would guess that the Product combo will be null on the new record. If you check for that before applying your other field checks you should be on the right path.

 

[Chumpalot]

I'll give it a crack and post back here with my findings. Thank you again.

 

[Chumpalot]

Thank you for pointing me in the right direction. The form works much better this way.

Here is the code I used. I'll adapt it to include a check of one of the main form controls but otherwise it's working well.

If [Child15].Form.RecordsetClone.RecordCount < 1 Then
MsgBox "Please enter a product"
Else
DoCmd.Close