Check all records in a subform based on cbo of main form

[kingnothingz]

Hi All,

I have a form with 22 subforms (in about 11 tabs). Each one of these subforms has a controlling field in the main form (table) that dictates if the associated subform needs to comply with data entry rules or not.

For example:
Field in main form cboMed (Yes; No)
Subform: frmSubMeds has a list of medications

I want to be able to loop through all the records of the subform and determine if they are complete.

This check is run from a command button; that should also consider the value in the combo box. i.e. Check only if the combo has a value of "Yes"

Since i have 22 subforms and have to run the check on everyone of them, i have used the tag property to check if the record is blank or not. This is what i have so far

For Each ctl In Me.Controls
 Select Case ctl.Properties("ControlType")
    Case acSubform ' only look for subforms
    If ctl.Enabled Then
    If Not (IsNull(ctl.Properties("SourceObject"))) Then 'if it is a subform type
        For Each subCtl In ctl.Form.Controls
            If subCtl.ControlType = acTextBox Or subCtl.ControlType = acComboBox Then
                If subCtl.Tag = "Required" And subCtl.Enabled Then
                    If IsNull(subCtl) Or subCtl = "" Or subCtl.Value = 0 Then
                           checkRecords = False
                        If subCtl.Controls.Count <> 0 Then
                          strBlank = "- " & Replace(subCtl.Controls.Item(0).Caption, ":", "") & Chr(13) & Chr(10) & strBlank
                                Else
                            strBlank = "- " & subCtl.Name & Chr(13) & Chr(10) & strBlank
                        End If
                    End If
                End If
            End If
        Next

The problem with this is that it only looks for the selected record in the subform and doesnt look for ALL the records.

I have no idea how to proceed further, any help in this regard will be appreciated.

-kingnothingz

 

[spikepl]

The logic of having validation rules that apply only sometimes escapes me. Can you explain?

Normally validation rules would be applied record by record when you enter data, not post facto.

In your logic, a simple mistake in setting your "controlling field" can result in many subform records already having been saved while not complying with the validation rules.

 

[kingnothingz]

Hi spikepl

Thanks for your reply.

This isn't a validation rule as such, but is used later for a compliance report on the quality of data. Yes, i know it is better to ensure the quality by adding validation rules, but unfortunately, this is how the end user wants it.

The "controlling field" only drives the compliance report on which subforms need to be checked.

Hope it makes sense.

-kingnothingz

 

[spikepl]

Hope it makes sense.

Not really. Sometimes people want to shoot themselves in the head too

Seriously, this entire approach is just plain silly; either you want to secure your data to some standard, in which case this approach sux big time or you don't. You'd normally not leave data validation to human memory (remember to tick that box blah bla).

Update: Blame your advisor- say that the expert you conferred with declared the random validation approach silly

Your case is complicated by that the requirement to validate is attached to controls but your data sits in a dataset.

You can loop thtough dataset of a form or subform like this:

Dim rst as DAO.Dataset

DIm myForm as Form

Set myForm =myCurrentForm OR Set myForm=myCurrentFrom.MysubformControl.Form

Set rst = Me.RecordsetClone
With rst
   While Not .Eof Do
       For Each ctl In Me.Controls
       ' the rest as you have it, but, the value in the recordset ism if you name the controls the same as the fields,         rst(ctlName)
       rst.Next
   Loop
End With

Set rst=Nothing

 

[kingnothingz]

Not really. Sometimes people want to shoot themselves in the head too

Believe me, that's exactly how I feel right now.

Anyway,

I'll try this approach of looping through the dataset and see how I go.

Thanks again

-kingnothingz

 

[kingnothingz]

Works like a charm thanks.