Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Require Fields on the form (2003)

    Hi,

    I am placing the following codes to the BeforeUpdate event of a form. I also selecting the controls I want to Require Data for and then updating the .Tag property to "RequiredData" (without quotes).

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strRequired As String
    strRequired = RequiredData(Me) 'Line Updated, it must pass the reference to ME
    If Len(strRequired) Then
    Cancel = True
    MsgBox "Can not save record because the following field(s) can not be Null" & vbCrLf & vbCrLf & strRequired,vbCritical, "Required Data is Missing"
    Me.Controls(Split(Required, vbCrLf)(0)).SetFocus
    End If
    End Sub

    I also place the Function RequiredData() below into a standard module:

    Function RequiredData(frm As Form) As String
    Dim ctl As Control
    For Each ctl In frm.Controls
    If Me.Tag = "RequiredData" Then
    If IsNull(ctl.Value) Then RequiredData = RequiredData & ctl.Name & vbCrLf
    End If
    Next ctl
    End Function

    Everything works very well. When the user attempts to save the record it runs the function RequiredData() that returns a list of all fields that I Tagged as Required that are presently Null.

    However, I also have some fields on the sub form that need to be required fields too. But for some reason, those codes doesn't work for subform. I tried to put the same codes to the BeforeUpdate event of subform, but no message pops up to stop user if there's no entry on require field on the subform.

    Any ideas?

    Thanks in advance.

    Regards

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Require Fields on the form (2003)

    In the function RequiredData, the line

    If Me.Tag = "RequiredData" Then

    should be

    If ctl.Tag = "RequiredData" Then

  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Require Fields on the form (2003)

    Sorry, Hans. I post the wrong codes. Below is the codes that I really using and doesn't apply it to subform:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strRequired As String
    strRequired = RequiredData(Me)
    If Len(strRequired) Then
    Cancel = True
    MsgBox "Can not save record because the following field(s) can not be Null" & vbCrLf & vbCrLf & strRequired, vbCritical, "Required Data is Missing"
    Me.Controls(Split(strRequired, vbCrLf)(0)).SetFocus
    End If

    End Sub

    The codes below in a module:

    Function RequiredData(frm As Form) As String
    Dim ctl As Control
    For Each ctl In frm.Controls
    If frm(ctl.Name).Tag = "RequiredData" Then
    If IsNull(ctl.Value) Then RequiredData = RequiredData & ctl.Name & vbCrLf
    End If
    Next ctl
    End Function


    Sorry about that. Thanks again.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Require Fields on the form (2003)

    1) Have you tried changing

    If frm(ctl.Name).Tag = "RequiredData" Then

    to

    If ctl.Tag = "RequiredData" Then

    2) Have you made sure that you have set the Tag property of the controls on the subform that you want to check to RequiredData?

  5. #5
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Require Fields on the form (2003)

    Yes, Hans.

    I have changed it to

    If ctl.Tag = "RequiredData" Then

    Also I have double check that I have set the Tag property of the controls on the subform that I want to check to RequiredData.

    Any idea why the codes doesn't apply to subform?

    Thanks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Require Fields on the form (2003)

    Sorry, no idea. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Require Fields on the form (2003)

    Hi Hans,

    Please find the attached database.

    Thanks again.

    Regards
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Require Fields on the form (2003)

    You have forgotten the line

    End If

    above the line

    End Sub

    in the Form_BeforeUpdate event procedure of the subform.

  9. #9
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Require Fields on the form (2003)

    Thanks, Hans.

    But I the codes won't apply to subform after I add the "End If."

    Please advise.

    Thanks

    Regards

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Require Fields on the form (2003)

    It does work for me. See attached version.
    Attached Files Attached Files

  11. #11
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Require Fields on the form (2003)

    Thanks, Hans.

    I just re-try it and the codes only work if I have entried at lease one field on the subform. If I don't entry anything on the subform, then click "Add Record" button. The data is going to save.

    How can I modify the codes if user don't entry anthing on the subform, the data won't be saved.

    Thanks again.

    Regards

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Require Fields on the form (2003)

    The Before Update event only fires if the user has entered something, so the code works as expected.

    What you want is not possible - records in the subform cannot be saved before the record in the main form has been saved.

  13. #13
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Require Fields on the form (2003)

    Thanks, Hans.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •