BeforeUpdate Validate using Form and Subform

[mikkistr]

I've been using code to validate data on the same form. For example if one field contains data then another field must not be null or if field1 = 2 then field5 cannot = 4 etc. My question is how do I reference information on a subform. I want to do things like if the mainform's [field_x] is a 3 then at least one of the records on the subforms [field_y] must be a 9. I also want to force if the mainform's [field_w] is a 6 then there must be 2 or more related records in the subform. I think I may need some type of counter with that one.

Here is a sample of the code I've been using to validate other data. Maybe someone can help me modify it to do the other things I want.

Private Sub Form_BeforeUpdate(Cancel As Integer)
' Display message if Vehicle Type is Null and Section Type indicates Vehicle is Present.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

If IsNull(Me!COMBO_VEH_TYPE) And [SEC_TYPE] = "V" Then
strMsg = "You must pick a value for Vehicle Type if a vehicle is present!"
strTitle = "Point Of Impact Required"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Cancel = True
End If

Thanks to anyone who can help!

 

[BarkerD]

Your validation here can become quite complex.

It is pretty simple to test for a particular value in multiple records by looping through a recordset, testing for a condition, updating a Boolean variable flag, and then moving to the next record.

The second item can also be accomplished by using the same method, and updating a counter every time the condition is true.

Here is a sample of what you may need.

'I assume you have created a recordset that includes only Records in the sub-form that are related to the main form.

Dim bolConditionEncountered as Boolean

Do While rst.EOF=False
If rst![TestFieldName]=? Then
bolConditionEncountered=True
EndIF
rst.MoveNext
Loop

If bolConditionEncountered= False Then
'Your Error Message and validatino
EndIF

Duane Barker