RE: Required Fields in main form with subform

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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?
--
Dave Hargis, Microsoft Access MVP


"Tina Hudson" wrote:

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
--
Thanks,
Tina Hudson


"Klatuu" wrote:

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

--
Dave Hargis, Microsoft Access MVP


"Tina Hudson" wrote:

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.

--
Thanks,
Tina Hudson
.

Relevant Pages

  • Re: Print Macro
    ... the cancel option would cancel the print job entirely and didn't realize that ... Dim resp As Boolean ... Sub EditedPrintAll() ... Dim CurPrinter As String ...
    (microsoft.public.excel.misc)
  • Re: BringToFront
    ... Leban?s Access Rotate Text control 2, but I will come back to that later. ... Dim MouseXStart As Integer, MouseYStart As Integer ... Sub SaveCtlPosition ... Private Sub Angle_AfterUpdate ...
    (comp.databases.ms-access)
  • Re: BringToFront
    ... Leban?s Access Rotate Text control 2, but I will come back to that later. ... Dim MouseXStart As Integer, MouseYStart As Integer ... Sub SaveCtlPosition ... Private Sub Angle_AfterUpdate ...
    (comp.databases.ms-access)
  • RE: Required Fields in main form with subform
    ... "Tina Hudson" wrote: ... Private Sub Form_BeforeUpdate ... Dim aryCtl As Variant ... to Cancel Update", vbQuestion + vbYesNo) = vbYes Then ...
    (microsoft.public.access.modulesdaovba)
  • Re: Property Page is not loaded on Vista
    ... With managed code you don't have to have a separate OCX for the property page, an OCX is an ActiveX control, that's how it's done in unmanaged code. ... if you aren't using any special controls on your property page user control and not compiling a separate unmanaged OCX then those usual causes don't apply obviously. ... Public Sub ApplyImplements ... Dim userControlType As Type = GetType ...
    (microsoft.public.outlook.program_vba)