Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jun 2010
    Posts
    10

    Unanswered: Make all fields in form mandatory

    Hello,

    I have a fairly complex database (I think so anyway), that I cannot get all the fields entered to be a mandatory data entry. I would like it that they cannot skip any field before continuing on to the next data field. Everything I've tried so far does not seem to work. I've tried locking them and placing in some code, but nothing worked so far.

    I've added some code on the BeforeUpdate, locked the fields, but nothing worked yet. I'm not a programmer, but can add VB code to the form.

    On the form I have the PrtID as a combo box with the following code in the BefroeUpdate loaded, so I'm not sure if that's part of it.

    code;
    Private Sub PrtID_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.PrtID) Then Cancel = True: Exit Sub
    Me.RecordsetClone.FindFirst "PrtID = " & PrtID & " AND StatusID = 1"

    'if a matching record was found, then move to it
    If Not Me.RecordsetClone.NoMatch Then
    Me.Undo
    Cancel = True
    Me.Bookmark = Me.RecordsetClone.Bookmark
    MsgBox "This part number has already been started", , "Part number already started"
    End If
    End Sub

    Please see the attached for fieldss that I need to have control with;
    Attached Thumbnails Attached Thumbnails currentjobs.JPG  

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    first off
    change the table design for each column to required: yes
    that means users cannot store new rows unless there is a value. you'd need to do this in Access if you allow your users to enter data directly (int he table or via a datasheet). you can get (sort of) the same effect programatically in a form.

    you can add to the form's BEFORE UPDATE event any validation code you require
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2010
    Posts
    10

    Changed updated

    Hi, I have changed the following needed fields to be required.

  4. #4
    Join Date
    Jun 2010
    Posts
    10

    Charged to reuired - Thank you!

    I'm not sure why I didn't do this previously since I knew this and have used it in the past.

    I was hoping that I could do it with VBA also.

    Thank you,

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by KellyR View Post

    ...I was hoping that I could do it with VBA also...
    You can do that, leaving the Required Property at the Table-level to No. Instead of having it Required at the Table-level, you don't allow a Control to contain a Null or a Zero Length String.

    There are a number of ways to approach this; for making a couple of Controls/Fields mandatory, you can use something like this:
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
     If Nz(Me.Control1,"") = "" Then
       MsgBox "Control1 Must Not Be Left Blank!"
       Cancel = True
       Control1.SetFocus
       Exit Sub
     End If
     
    If Nz(Me.Control2, "") = "" Then
       MsgBox "Control2 Must Not Be Left Blank!"
       Cancel = True
       Control2.SetFocus
       Exit Sub
     End If
    
    End Sub


    You could loop through all or some Controls and do the same thing. If the above is too onerous, given your situation, this will loop through all Textboxes and all Comboboxes and check that they're populated
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    Dim ctl As Control
    Dim CName As String
    
    
    For Each ctl In Me.Controls
        Select Case ctl.ControlType
            Case acTextBox, acComboBox
                If Nz(ctl, "") = "" Then
                  CName = ctl.Controls(0).Caption
                  MsgBox "Following field is required: " & vbCrLf & vbCrLf & CName
                  Cancel = True
                  ctl.SetFocus
                  Exit Sub
                 End If
        End Select
    Next ctl
    
    End Sub


    You could also use the Tag Property to mark certain Controls, and then loop through all Controls but only check on/address the status of these 'marked' Controls.

    To set the Tag Property for multiple Controls, all at once:
    1. Go into Form Design View
    2. Holding down <Shift> and Left clicking on each Control in turn.
    3. Go to Properties Other and enter Marked in the Tag Property (just like that, no Quotation Marks)

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    Dim ctl As Control
    Dim CName As String
    
    
    For Each ctl In Me.Controls
      If ctl.Tag = "marked" Then
         If Nz(ctl, "") = "" Then
           CName = ctl.Controls(0).Caption
           MsgBox "Following field is required: " & vbCrLf & vbCrLf & CName
           Cancel = True
           ctl.SetFocus
           Exit Sub
         End If
       End If
    Next ctl
    
    End Sub


    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    looks good... but given that most Access developers don't lock out tble access or allow users to use datasheets then do both.

    the VAB code handles the error(s) more gracefully, the enforcement at table level stops users circumventing form based logic checking
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2010
    Posts
    10

    VBA Code

    Hello,

    Thank you for the help.

    I have changed all of the table data fields on the form to not be required.

    I then tried all (3) of the examples for the VBA code and it stops on the ctl.SetFocus every time. It also comes up with an error message stating that the nextfield is required ie; ProductName

    I am starting with the code at the Quantity since I already have some code in the first field for the BeforeUpdate event.

    Private Sub Quantity_BeforeUpdate(Cancel As Integer)

    Dim ctl As Control
    Dim CName As String


    For Each ctl In Me.Controls
    If ctl.Tag = "marked" Then
    If Nz(ctl, "") = "" Then
    CName = ctl.Controls(0).Caption
    MsgBox "Following field is required: " & vbCrLf & vbCrLf & CName
    Cancel = True
    ctl.SetFocus
    Exit Sub
    End If
    End If
    Next ctl

    End Sub


    Thank you,
    Kelly

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's the name and the type of the control when the error occurs? To know it, open the immediate window (Ctrl+G) and type
    Code:
    ? ctl.Name
    followed by the Enter key. Knowing the name you should be able to retrieve the type.
    Have a nice day!

  9. #9
    Join Date
    Jun 2010
    Posts
    10

    Immediate Window

    Hello, when I enter this ? "ctl.Name" in the immediate windows it shows "Quantity"

    no quotes though.

    This is the field that I skipped without enter anything to see what error would appear.

    Thank you,

    Kelly

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Right! An what type of control is the one named Quantity (that was the second part in my question)?

    If an error happens when trying to use the SetFocus method on a control it most likely means that there is no SetFocus method for this control. It happens if, for instance, you try to set the focus to a Label (Label controls can't receive the focus then have no SetFocus method).
    Have a nice day!

  11. #11
    Join Date
    Jun 2010
    Posts
    10

    Control Type

    Hello,
    When you say control type, I'm not sure what you're meaning by that. It's a text box "number field"

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you can't set the focus (= use the SetFocus method) on a control, this control cannot be a TextBox, unless it is disabled (ctl.Enabled = False).
    Have a nice day!

  13. #13
    Join Date
    Jun 2010
    Posts
    10
    Quote Originally Posted by Sinndho View Post
    If you can't set the focus (= use the SetFocus method) on a control, this control cannot be a TextBox, unless it is disabled (ctl.Enabled = False).

    I'm not sure what you mean? I tried to use ctl.Enabled = False in the VBA, but it comes up with the expected function or variable when I compile the script.

    Private Sub Quantity_BeforeUpdate(Cancel As Integer)

    Dim ctl As Control
    Dim CName As String


    For Each ctl In Me.Controls
    If ctl.Tag = "marked" Then
    If Nz(ctl, "") = "" Then
    CName = ctl.Controls(0).Caption
    MsgBox "Following field is required: " & vbCrLf & vbCrLf & CName
    Cancel = True
    ctl.SetFocus = False
    Exit Sub
    End If
    End If
    Next ctl


    End Sub

    thank you,
    Last edited by KellyR; 05-03-14 at 08:41. Reason: updated

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Code:
    ctl.SetFocus = False
    Where does that come from?

    The SetFocus method of a control does not accept any argument. The syntax is:
    Code:
    Control.SetFocus
    See: SetFocus Method [Access 2003 VBA Language Reference].

    Moreover, all controls cannot accept the focus, either because of their type (e.g. you cannot set the focus to a control of type Label), or because a control is disabled (i.e. the Enabled property of the control is set to False).
    Have a nice day!

  15. #15
    Join Date
    Jun 2010
    Posts
    10

    ctl.focus

    "If you can't set the focus (= use the SetFocus method) on a control, this control cannot be a TextBox, unless it is disabled (ctl.Enabled = False)."

    I thought that I was supposed to place that into the code from the response above.

    sorry about that.

    I'm not sure what to do now...

Posting Permissions

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