Required Fields in main form with subform

[Tina Hudson]

Good morning,

I have a data entry form with 2 sub forms. 3 fields on the main form are
set to required in the table design. If a user clicks on a field in the
subform before entering data in any of the 3 required fields, I get a 3314
error:

"The field 'fieldname' cannot contain a null value because the required
property is set to True. Enter a value for this field."

where 'fieldname' is one of the 3 required fields on the main form.

Is there anyway around this? I don't want the user to see the error message
as it will confuse them.

 

[Klatuu]

When you move from a main form to its subform, the main form's data will be
updated. That is why you are getting the error. You can't just hide the
error, because you will not be able to move off the main form until the
fields have data.

I would suggest you use the form's Before Update event to check the required
fields for valid data before you allow the update. Typically, you will show
the user a message box telling them what the problem is so they can correct
the error before proceeding. If there are errors, cancel the update. This
example (untested air code) will allow the user to correct the error or
cancel any changes made:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim aryCtl As Variant
Dim lngCtr As Long
Dim ctl As Control

aryCtl = Array("txtBox1","txtBox5", "tstBox11")

With Me
For lngCtr = 0 To 2
Set ctl = .Controls(aryCtl(lngCtr))
If IsNull(ctl) Then
Cancel = True
If MsgBox ctl & " Is Required " & vbNewLine & _
"Click Yes to Correct Or No to Cancel Update", _
vbQuestion + vbYesNo) = vbYes Then
.ctl.SetFocus
Else
Me.Undo
End If
Exit For
End If
End With

End Sub

 

[Tina Hudson]

I'm getting error message: "method or data member not found" on the line:

..ctl.SetFocus

I wasn't sure about the Array("txtBox1","txtBox5", "tstBox11"), but I
substituted with the 3 fields that are required - First, Last and Pin.

Here is my complete code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim aryCtl As Variant
Dim lngCtr As Long
Dim ctl As Control

aryCtl = Array("First", "Last", "PIN")

With Me
For lngCtr = 0 To 2
Set ctl = .Controls(aryCtl(lngCtr))
If IsNull(ctl) Then
Cancel = True
If MsgBox(ctl & "Is Required" & "Click Yes to Correct or No
to Cancel Update", vbQuestion + vbYesNo) = vbYes Then
.ctl.SetFocus
Else
Me.Undo
End If
Exit For
End If
End With

End Sub

 

[Klatuu]

Your assumption on the array is correct. You use the names of the controls.
Assuming all three are text boxes, I don't see a problem.

When you get the message box, is the name of the control the first thing in
the message?

 

[Tina Hudson]

No. I get "Compile error:" Then on the next line, "Method or data member
not found."

 

[Klatuu]

My bad. This line:
.ctl.SetFocus
Should be
ctl.SetFocus

Also, you can remove the Me from this line:

Me.Undo
So it is just
.Undo

Told you it was untested.
Also, I would suggest you add this line:

Set ctl = Nothing

as the last line (just before End Sub)
It isn't necessary, but it is good practice.

 

[Tina Hudson]

lol

Okay, changed. Now I get another compile error: End with without with.

Any ideas?

 

[Klatuu]

Okay, what did you do with it?
It was in my original code.
End With should be the line after the last End If

(Copied from the code you sent me)

 

[JimBurke via AccessMonster.com]

Unless I'm mistaken, I don't think you want to set a form control to Nothing:

Set ctl = Nothing

I would think that should be

Set ctl = Null

My bad. This line:
.ctl.SetFocus
Should be
ctl.SetFocus

Also, you can remove the Me from this line:

Me.Undo
So it is just
.Undo

Told you it was untested.
Also, I would suggest you add this line:

Set ctl = Nothing

as the last line (just before End Sub)
It isn't necessary, but it is good practice.

No. I get "Compile error:" Then on the next line, "Method or data member
not found."

[quoted text clipped - 89 lines]

 

[Klatuu]

Yes you do.
Any time you use an object variable to reference an object, it is good
practice to set it to Nothing when you are done with it.

In Theory, it would not be necessary in the case, because it will be
destroyed when the Sub is completed; however, it is a good idea to do it
anyway. It is self documenting and you never know if something might cause
it not to release its memory.
--
Dave Hargis, Microsoft Access MVP

Unless I'm mistaken, I don't think you want to set a form control to Nothing:

Set ctl = Nothing

I would think that should be

Set ctl = Null

My bad. This line:
.ctl.SetFocus
Should be
ctl.SetFocus

Also, you can remove the Me from this line:

Me.Undo
So it is just
.Undo

Told you it was untested.
Also, I would suggest you add this line:

Set ctl = Nothing

as the last line (just before End Sub)
It isn't necessary, but it is good practice.

No. I get "Compile error:" Then on the next line, "Method or data member
not found."

[quoted text clipped - 89 lines]

Is there anyway around this? I don't want the user to see the error message
as it will confuse them.

 

[JimBurke via AccessMonster.com]

I was thinking like ctl was actually the same as the form control itself - I
guess in this case it's not, it's a new control that has the form control's
contents copied to it. My mistake.

Yes you do.
Any time you use an object variable to reference an object, it is good
practice to set it to Nothing when you are done with it.

In Theory, it would not be necessary in the case, because it will be
destroyed when the Sub is completed; however, it is a good idea to do it
anyway. It is self documenting and you never know if something might cause
it not to release its memory.

Unless I'm mistaken, I don't think you want to set a form control to Nothing:

[quoted text clipped - 27 lines]

 

[Klatuu]

You are pretty close to right. It is not a copy of the control, it is a
reference to the control. You can think of it as a By Value or By Reference
concept. It would be By Reference.

There at least two reason to do such a thing in the first place. One, it
executes faster because it doesn't have to walk through the objects to find
it. And, it is much easier to code. In this case you would have to write
the same code 3 times with a different control name. As you can see, this is
less code and if you ever add another required field, you just add the name
to the array and increase the To value in the For Next Loop.



--
Dave Hargis, Microsoft Access MVP

I was thinking like ctl was actually the same as the form control itself - I
guess in this case it's not, it's a new control that has the form control's
contents copied to it. My mistake.

Yes you do.
Any time you use an object variable to reference an object, it is good
practice to set it to Nothing when you are done with it.

In Theory, it would not be necessary in the case, because it will be
destroyed when the Sub is completed; however, it is a good idea to do it
anyway. It is self documenting and you never know if something might cause
it not to release its memory.

Unless I'm mistaken, I don't think you want to set a form control to Nothing:

[quoted text clipped - 27 lines]

Is there anyway around this? I don't want the user to see the error message
as it will confuse them.

 

[Tina Hudson]

It's there.

Here's what you just sent - see the last line?

(Copied from the code you sent me)

Here's the complete code again:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrorHandler

Dim aryCtl As Variant
Dim lngCtr As Long
Dim ctl As Control

aryCtl = Array("First", "Last", "PIN")

With Me
For lngCtr = 0 To 2
Set ctl = .Controls(aryCtl(lngCtr))
If IsNull(ctl) Then
Cancel = True
If MsgBox(ctl & "Is Required " & vbNewLine & "Click Yes to
Correct Or No to Cancel Update", vbQuestion + vbYesNo) = vbYes Then
ctl.SetFocus
Else
.Undo
End If
Exit For
End If
End With

Set ctl = Nothing

End Sub