Results 1 to 14 of 14
  1. #1
    Join Date
    May 2006
    Posts
    178

    Unanswered: How do you make a field "textbox" mandatory to fill before saving?

    Hey,

    I need some help please. I wanted to know on a form, when a person hits the save command button, i want it to prompt up that such and such txtbox needs information before you can save.

    Many thanks

    Aboo

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    If IsNull(suchandsuchtextbox) then MsgBox "Such and such must be filled in, please fill it in now you neglectful user!!"

    Something like that?!

    Just put that in your code for your save command button and you should be going in the right direction again
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    May 2006
    Posts
    178
    Hey Trekky,

    Thanks for your reply, can i put a multitude of text boxes within the the parentheses? or do i need to create several If statments?

    Thanks

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    You could always cycle through all the controls on your form, look at their tag property and proceed accordingly, mostly off the top of my head something like:
    Code:
    Private Function RequiredBoxesComplete() As Boolean
        
        On Error GoTo ErrorHandler
        DoCmd.Hourglass True
        
        'instantiate variables
        Dim ctl As Control, _
            frm As Form, _
            lngHighlight As Long, _
            lngDefault As Long
        
        'Set defaults
        RequiredBoxesComplete = True
        Set frm = Forms!frmName        'SET THIS TO YOUR FORM NAME
        lngHighlight = RGB(255, 180, 180)
        lngDefault = vbWhite
        
        'cycle through controls and check
        For Each ctl In frm.Controls
            If ctl.ControlType = acTextBox Then
                If ctl.Tag = "Required" Then
                    If Nz(ctl, "") = "" Then
                        ctl.BackColor = lngHighlight
                        RequiredBoxesComplete = False
                    Else
                        ctl.BackColor = lngDefault
                    End If
                End If
            End If
        Next
        
    ExitFunction:
        DoCmd.Hourglass False
        DoCmd.Echo True
        DoCmd.SetWarnings True
        Exit Function
    ErrorHandler:
        RequiredBoxesComplete = False
        MsgBox "You suck!", vbCritical, "ERROR!"
        GoTo ExitFunction
        
    End Function
    Just set the Tag property of each required text box to "Required". Better yet, call this code from the Form's OnLoad event and from the OnChange event of each required box so the boxes change dynamically with user's input. Then call it during the Save button click to make sure, like (again, just shooting from the hip):

    Code:
    If RequiredBoxesComplete Then
        msgbox "You forgot to fill in stuff.  Tsk, tsk, tsk.", _
                    vbExclamation, _
                    "You fail!  F--"
        Exit Sub
    End If
    HTH!


    *EDIT*
    Just tested code and found I forget a "Then" and had an extra variable, fixed it.
    Last edited by nckdryr; 01-20-09 at 01:39.
    Me.Geek = True

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    My God nckdryr, let's not try making mountains out of molehills? I don't like that solution at all. Not only is it a waste of a free property, but it is also longwinded, overly complex and buckets all fields into one. The poor user won't know which fields they missed.

    Quote Originally Posted by aboo
    can i put a multitude of text boxes within the the parentheses? or do i need to create several If statments?
    Just use multiple if statements.

    You can also go to a control (such and such) and then open the properties for it and set the Required property to Yes. You don't get to control the message thrown at the poor user, but it is a quick/easy way to ensure fields are entered.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by StarTrekker
    My God nckdryr, let's not try making mountains out of molehills? I don't like that solution at all. Not only is it a waste of a free property, but it is also longwinded, overly complex and buckets all fields into one. The poor user won't know which fields they missed.
    I beg to differ; if the user isn't already using the property, than it's a perfectly good use of an otherwise unused property. Furthermore, it's entirely dynamic, so if you add more boxes later on then, as long as the Tag property is set, they'll update as well. On top of that, I would argue that the User has a better idea of which fields they missed as they can get a visual check as they go, and not have to wait to click a button to see if they are successful or not, and then scrutinize the form to find which field they missed. Besides, nearly all the work has already been done for aboo, he just needs to call the function I wrote.

    And now aboo has two good options to choose from, and it is ultimately aboo's choice which approach he likes better; one of the perks of being the guy to program the database
    Me.Geek = True

  7. #7
    Join Date
    May 2006
    Posts
    178
    Trekker/nckdryr,

    Thanks for your help, im gonna give it a shot.

  8. #8
    Join Date
    Feb 2014
    Location
    Pittsburgh, USA
    Posts
    28

    mandatory field problem

    Hi guys,

    I used the IfNull() then MessageBox else thing and it worked for 3 of the 4 fields I want mandatory. However, for some reason, one field (which is a combobox) is being totally ignored. I thought it might be because it is a combobox but then I changed another to become a combobox with similar conditions, and it worked. I don;t know why this particular field is not being checked... This is so frustrating and makes absolutely no sense.. Any ideas?

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

    ...My God nckdryr, let's not try making mountains out of molehills...it is also longwinded, overly complex
    Quote Originally Posted by nckdryr View Post

    ...I beg to differ...
    I have to agree with nckdryr, here! Both of these are valid, accepted options; it's all a matter of how many Fields you need to check on! If you only have one or two to check, go ahead and write separate code, but why write separate code for 10 Fields when, in Form Design View, you can select all of the Controls, go to Properties - Other, enter a Tag, then write a piece of code to cycle thru them and check for data?

    As for it being a 'waste of a free property,' don't understand that at all. If the Tag Property is needed for something else, you can always place multiple values in it, and check for the appropriate one using InStr().

    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

  10. #10
    Join Date
    Feb 2014
    Location
    Pittsburgh, USA
    Posts
    28
    Ok, figured it out. The field was called Application. When I changed its name to App, it worked.. Straaaange

  11. #11
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by RandaAlameh View Post
    Ok, figured it out. The field was called Application. When I changed its name to App, it worked.. Straaaange
    Not Straaaange at all! Application is a Reserved Word in Access, and the use of these words, as Field/Control names, frequently lead to problems!

    http://www.databasedev.co.uk/ms-acce...ved-words.html

    Glad you got it working!

    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

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Application is a reserved word in Access, that's probably why.
    Have a nice day!

  13. #13
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by nckdryr View Post
    You could always cycle through all the controls on your form, look at their tag property and proceed accordingly, mostly off the top of my head something like:
    Code:
    Private Function RequiredBoxesComplete() As Boolean
        
        On Error GoTo ErrorHandler
        DoCmd.Hourglass True
        
        'instantiate variables
        Dim ctl As Control, _
            frm As Form, _
            lngHighlight As Long, _
            lngDefault As Long
        
        'Set defaults
        RequiredBoxesComplete = True
        Set frm = Forms!frmName        'SET THIS TO YOUR FORM NAME
        lngHighlight = RGB(255, 180, 180)
        lngDefault = vbWhite
        
        'cycle through controls and check
        For Each ctl In frm.Controls
            If ctl.ControlType = acTextBox Then
                If ctl.Tag = "Required" Then
                    If Nz(ctl, "") = "" Then
                        ctl.BackColor = lngHighlight
                        RequiredBoxesComplete = False
                    Else
                        ctl.BackColor = lngDefault
                    End If
                End If
            End If
        Next
        
    ExitFunction:
        DoCmd.Hourglass False
        DoCmd.Echo True
        DoCmd.SetWarnings True
        Exit Function
    ErrorHandler:
        RequiredBoxesComplete = False
        MsgBox "You suck!", vbCritical, "ERROR!"
        GoTo ExitFunction
        
    End Function
    Just set the Tag property of each required text box to "Required". Better yet, call this code from the Form's OnLoad event and from the OnChange event of each required box so the boxes change dynamically with user's input. Then call it during the Save button click to make sure, like (again, just shooting from the hip):

    Code:
    If RequiredBoxesComplete Then
        msgbox "You forgot to fill in stuff.  Tsk, tsk, tsk.", _
                    vbExclamation, _
                    "You fail!  F--"
        Exit Sub
    End If
    HTH!


    *EDIT*
    Just tested code and found I forget a "Then" and had an extra variable, fixed it.
    I have to admit for data entry i love this idea. Where do you put the initial code though, as a public function?

  14. #14
    Join Date
    Apr 2016
    Posts
    1
    Whilst I appreciate the "Function" approach to this request it presents three issues;

    1) as previously stated, lumps all fields into the one process;
    2) can only trigger the same fields on the 1 form (so if you want to set alternative fields triggered off another event you can't) and probably the biggest problem;
    3) The field names need to be bastardised to make intelligent messages other wise you get this "field AnyDataItem is required"

    I prefer the long winded approach where you have far more control over the entire process.
    Just my 2c worth,
    Cheers,
    A.

Posting Permissions

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