Validation Code for SubForm

[FranD]

I have a subform named frmReturnedBatches on a main form named frmCloseBatch. Basically, if a batch is being returned due to problems, the user must enter a date of return. The subform is a tabular form which allows users to enter several reasons for the return into a field named [Problem]. Therefore, the subform will only need to be filled in when the user enters a date the batch was returned. I added code to the frmCloseBatch BeforeUpdate Event Procedure as follows:

Dim strMessage As String
Dim intOptions As Integer
Dim bytChoice As Byte

If Not IsNull(DateProblemsReturned) And IsNull([Forms]![frmCloseBatch]![frmReturnedBatches].Form![Problem]) Then
strMessage = "You must enter a reason the batch is being returned."
intOptions = vbOKCancel
bytChoice = MsgBox(strMessage, intOptions, "Missing Information")
[Forms]![frmCloseBatch]![frmReturnedBatches].Form![Problem].SetFocus
Cancel = True
End If

What's happening is after I've tabed past the last field on the main form, my error message is generated and will not allow me to proceed past this point.

Help!!!

 

[Rich]

I think you may have two problems. I have had problems trying to reference data in date fields with IsNull and IsDate functions I've found it easier to refer to a specific date instead If Me.DateFld <#01/01/60# Then etc and secondly trying to reference the sub from the main. You might find it easier to use the before insert event of the sub to prevent entry untill DateProblemReturned is filled in and then deal with the [Problem].
[No pun intended]
HTH

 

[Pat Hartman]

The problem is with the subform reference. It will always refer to the current record (of the subform). If the record pointer of the subform is pointing to a "new" record, the Problem field will be null.

One way to solve the problem is to add a control to the footer of the subform that displays the count of records in the subform's recordset.

Now, on to another problem. It is impossible to force the user to enter a problem in the subform. The return record must be saved prior to the reasons being added in the subform. Therefore, at least for a short period in time, you have inconsistant information. No matter how many error messages you display, you can't prevent him from just pushing the off button on the PC or loosing his network connection, etc. So, I suggest that you run a query regularly to find returned batches that don't have any problems associated with them.

=Count(*)

This field may be hidden since there is no reason for it to be visible. Check the value of this field for >0 rather than checking the Problem field for null.