Pass SubForm name to a Function to check for empty fields

[mabrande]

I have a form called 'Main - Form'. This form has a middle section that will display another form which the user selects from a listbox (which is on the 'Main - Form'). Let's say the user selects the form 'account'

I need to create a function that checks each control on the 'account' form to see if it is empty. I use the controls' tag option to see what the requirement is. Based on the tag value, the background of the field should be red (tag = M) or orange (tag = O) if the field is empty.



On the account form in the Private Sub Form_Current() section I want to pass the the correct name of the account form to this funtion:

Public Sub CheckFields(strForm As String)
'Check if tag of field contains a value B,M,O or nothing and color background based on that value.
Dim ctl As Control

For Each ctl In Forms![Main - Form]![strForm].Form!Controls

If ctl.Tag = "M" and ctl.value = "" Then
Debug.Print ctl.name
ctl.Tag.BackColor = vbRed
End If
Next ctl

Somehow I can't get this to work.

[June7]

You have code that changes the SourceObject property of a subform container control?

The CheckFields function is in a general module? Why not behind [Main - Form] since it explicitly references that form?

If the Account form is opened as a subform on [Main - Form], then the code must reference the subform container control name. Try:

For Each ctl In Forms![Main - Form].subformcontainername.Form.Controls

BackColor is not a property of Tag so that line is not valid. Remove the reference to Tag.

ctl.BackColor = vbRed

Is the subform in continuous or datasheet view? The code won't work.

Really should use Conditional Formatting instead of VBA.

[mabrande]

Not sure what is: subformcontainername. Is this 'acct'? Tried that, does not work.

I have a combobox on [Main Form] which has the following code:

Me.subform.SourceObject = ListTest.value

One of the options is a form called 'acct', If I select that from the combobox, MS-Access will show that form in the detail section of [Main Form]

I need a function that checks if a value is empty for each control on the acct form. The idea is, that the same function can be used if a choose another item from the combobox.

[June7]

A subform is created by installing a subform container control on another form. The container control SourceObject can be a table, query, form, report. I always give container control a name different from the object it holds. In your case, you use code to change the SourceObject property. The container control name will not change. So what is the name of the container control?

An alternative to this listbox and code to change container control SourceObject is to put separate subforms on pages of a tab control.