SubForms - Before Update Problem

[Eka1618]

Hello,

This is kind of related to the problem I had yesterday in the post "Stop
Record from Inserting"...But that thread got too confusing I think...

I have a function (CheckFormValues) that checks the controls to see which
ones were left blank/Null. I call this function in the BeforeUpdate event of
a form. I want this function to be called only when a user has started to
enter data into the form.

In my case, my problem right now lies with some subforms that call this
function. For whatever reason, when the parent form loads, only some of the
sub forms are being triggered as if someone has begun to enter data into the
forms. I imagine that this is being caused by default values that I have
declared for certain fields, but I do not know for sure.

There are between 11-17 subforms that all call this function in the
BeforeUpdate event. 8 of them have the same control source (tblTest). Of
these 8, 4 of them are being triggered as if someone has entered data into
the form, which I guess is causing the event to be fired. The 8 forms all
have a simmilar format, and all use default values for different fields. I
cannot seem to understand why the event is being fired for some, and not all
of them. In fact, I didn't have this problem until yesterday. In addition,
when the parent form loads, these 8 subforms are hidden...so it is not
possible for anyone to enter data.

The BeforeUpdate event for the subforms is being fired when teh parent form
closes. So if a person opens a form, then decides they do not want to use
that form... they press close, and this is when these subform events fire.

Below is the CheckFormValues function (I have it placed in a module). If
anyone knows what is causing the events to be fired, please let me know.
Thank You!

--
~Erica~

Public Function CheckFormValues(frmCheck As Form) As String
'Pass the name of the OPEN form to check
'Returns vbNullString if no errors found
'Returns the name of the control that fails
Dim ctl As Control
Dim lngCtlCount As Long
Dim lngX As Long

CheckFormValues = vbNullString
For Each ctl In frmCheck.Controls
With ctl
lngX = .ControlType
If Nz(Switch(lngX = acTextBox, True, _
lngX = acComboBox, True, _
lngX = acListBox, True)) Then
If frmCheck.Controls(ctl.Name).Enabled = True Then
' If Not IsNull(.Value) Then
' .BackColor = vbWhite
' End If

If Nz(.Value, vbNullString) = vbNullString Then
CheckFormValues = .Name
.BackColor = RGB(246, 166, 166)
Else
.BackColor = vbWhite
End If
End If
End If
End With
Next ctl
End Function

 

[Rick Brandt]

which ones were left blank/Null. I call this function in the
BeforeUpdate event of a form. I want this function to be called only
when a user has started to enter data into the form.

In my case, my problem right now lies with some subforms that call
this function. For whatever reason, when the parent form loads, only
some of the sub forms are being triggered as if someone has begun to
enter data into the forms. I imagine that this is being caused by
default values that I have declared for certain fields, but I do not
know for sure.

If you are really using BeforeUpdate then you must have a macro or code that
is actually setting at least one value in those subforms. Default Values
alone will not cause a form to be dirtied. Either the app or the user must
make an entry or else BeforeUpdate would not be firing.

 

[Eka1618]

I have tried some methods such as defaulting the subforms to be locked and
disabled when the parent form loads, but that does not work. I've also tried
setting the page they are in to be disabled when the parent form loads as
well since these particular forms are inside tab a tab control. This does not
work either. I do not understand why though... just does not make sense to me.

 

[Eka1618]

I am using BeforeUpdate to call the function...

I can check to see if I have a macro by mistake. I have default values set
in the table and I have default values set inside the form... but they vary
from form to form. I did not think that it was the default values, but I can
not tell, I have some error handling, but no error pops up. What I am seeing
is a message that I created in the case a person does not complete a form.
Here is the BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)

strValid = CheckFormValues(Me)

If strValid <> "vbNullString" Then
MsgBox "All fields must be filled before Continuing"
Cancel = True
End If
End Sub

Thanks for the suggestion. I am going to look into it, hope thats the
problem

 

[Eka1618]

In the last post I put quotes around vbNullString by mistake...

 

[Eka1618]

Well, I have found the source of the problem... it is a LostFocus Event on a
field in my code that's causing the problem...

I am gonna work on this because I have same event in other subforms which do
not produce the error. It may be because I have this particular field tab
index set at 0...