Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Apr 2014
    Posts
    89

    Unanswered: make a field mandatory to my users

    First let me say very new here so any answers please explain them so I can learn I would appreciate this.

    I found this on another site and followed there instructions and it does not work. I put it in the Before update and when i go thru it as a new form nothing happens other then it saves. When I remove an address from a previous entry then the below pops up. I want this to pop up as soon as the person clicks out of the field so as they are tabbing thru if they skip something it says WOW come back and fill me out. I would also like to know if there is a cycle code so that if they click esc 2 times then it says your record is not able to be saved and exits out back to the main switchboard.

    I use a form and it has a save macro when the person is done.
    I am using access 2007
    Any help please

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.[address]) Then
    Cancel = True
    MsgBox "You must enter a value for 'Address Required'. Please make a valid entry or press ESC to Cancel"
    'You could set the focus on the specific control if your wish, change the background color, ...
    End If
    End Sub

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This works:
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
        If IsNull(Me.[Address]) Then
            Cancel = True
            MsgBox "You must enter a value for 'Address Required'. Please make a valid entry or press ESC to Cancel"
            Me.Address.SetFocus
        End If
        
    End Sub
    Check if the event handler is properly registered in the BeforeUpdate property of the form: Open the property window (F4), select the Event tab and check the Before Update line.
    Have a nice day!

  3. #3
    Join Date
    Apr 2014
    Posts
    89

    make a field mandatory to my users

    thanks how do i know if that is set up right?

    It works once then not again.. I also tried to add a 2nd one basically i have all these fields
    Business name/residence name
    address
    tws name
    incident

    residencial or commerciall
    trooper
    time
    fault
    no fault warning letter sent
    cited
    notes
    ntc#
    That I want to repeat that code for IDK maybe there is a better/easier way as well.. The prob i found with the one in the table forceing all to be required was it kept you a repeating loop with no way out
    Last edited by dlafko; 04-28-14 at 14:24. Reason: added more detail

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Sorry but I don't understand what you mean at all.
    Have a nice day!

  5. #5
    Join Date
    Apr 2014
    Posts
    89

    make a field mandatory to my users

    Ok Let me start at the begining LOL

    1. I want to make a series of fields madatory
    2. I want the people to get a notice if they did not enter a field ( prefferably as they are tabbing thru but at least at the end when they save it)
    3. The list above are all the fields I want to be mandatory

    ********************************************
    The equation above when I put it in the code builder it seems to work ok but then when i close out and reopent it, it no longer works so i try and redo it then nothing.. I am saving the form and such so IDK if i am putting the code in wrong in the code buider or what..

    I am doing it this way.. I go to design view on the form and right click to get the Address properties then I go to the before update click code builder and past that code in.. sometimes it works other times it does not. I then tried to add the same code for the business name (changing name) of course.

    Hope this helps

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so it sounds like you want to put your code in a controls before update event. of more than one control uses the same message then put the code in a function and call it from each relevant control's before update.

    but be very very wary of such a programming style, ferinstance its possible to leave one control for another make a change there and then have two controls both fighting for user input.

    Im also not certain that in terms of user interface the approach you are using is a good idea.. users when enterign data tend to want to bang the data in, then sort out errors afterwards. in your model, if they are not concentrating on the screen there is a risk of things going seriously wrong. Id suggest you study the people who will actually use the application and see what they prefer or how they actually do their job as opposed to how you as a system designer think they do their job.

    you can get the same effect by setting controls background colours.
    in the past Ive used Red for Must complete, Amber for warnings and green in certain cases. but be aware of colour blind issues
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2014
    Posts
    89

    make a field mandatory to my users

    Thanks SO We are dispatchers ( Me to ) Just doing this to try and make my life easier

    So the fear is that if someone starts entering the info and then gets a 911 call or something and moves away that they could close out of the program and the current form with out remembering to fill in all the needed fields Hence the reminder you need to fill in these fields... The very basic is to make a reminder to them what still needs to be done so when we have the time to go back we don't forget..

    As far as the code I think that is what is happening they all go in as embedded stuff so they are prob fighting each other which cancels them out all together so any help or thoughts on how to accomplish a notice of this is whats missing.. Could there be some text that pops up when they hit save that would just list the fields that need completed rather than trying to do all this individual coding?

    Thanks for your help.. I do have another question related to passwords Is it ok to ask here or make another thread?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    id also suggest you place some code in the forms on close event that stops the form closing if inadequate data is captured.

    essentially the event driven model of Access provides plenty of hooks to attach code to ensure the form meets the business requirememnt. but you have to do the work to implement YOUR rules.

    HOW you do that is up to you.

    I'd recommend that you identify who created the row (use the network API to capture the network logon and computer ID... google ashish network api)

    you have several options
    you could save the row as is, but flag it as incomplete for review later

    you could force completion before allowing the user to close the form, and or save the data

    one of the advantages of a windowing system is that users shgoudl be free to move to whatever elements they need to, unless there is a reason they shouldnt' move to another form (within access) or another application. so there is nothign stopping the users from just leaving the Access form open, and return to it when they have time.

    in terms of error handling there's lots of ways of doing this.

    persoanlly I'd suggest using the background colour change option as a quick, easy and effective way of alerting people to where the errors are. set each controls tooltip with the error message, they can mouseover the cotnrol to find the error message.

    you can stuff a series of error messages and display them using say code int he forms BEFORE UPDATE and dsisplay then is a messaqe box


    what you could do is write a function for each validation rule that returns an error message

    then call that function from each relevant event hook, which would be the controls AFTER UPDATE event and the forms BEFORE UPDATE


    each function should set the background colour of the control (say white background OK [no error], red [error]
    the function shoudl also set the control tip to the error message

    you need to clear all those controls when you add a new row

    you coudl display the current control's control tip in a status control on the foot of the form.

    you could poll all the control tip's to pull build an error message
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's what I would use:
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
        Const c_FNames As String = "Business name/residence name,address,tws name,incident,residencial or commerciall," & _
                                   "trooper,time,fault,no fault warning letter sent,cited,notes,ntc#"
                                   
        Dim var As Variant
        Dim strMsg As String
        Dim lngCnt As Long
        Dim i As Long
        
        var = Split(c_FNames, ",")
        For i = 0 To UBound(var)
            With Me.Controls(var(i))
                If IsNull(.Value) Then
                    .SpecialEffect = 0
                    .BorderColor = vbRed
                    .BorderStyle = 1
                    lngCnt = lngCnt + 1
                Else
                    .BorderColor = vbBlack
                    .BorderStyle = 0
                    .SpecialEffect = 2
                End If
            End With
        Next i
        Select Case lngCnt
            Case 1:         strMsg = "The field highlighted in red is empty. A value is mandatory for this field."
            Case Is > 1:    strMsg = "The fields highlighted in red are empty. A value is mandatory for these fields."
        End Select
        If lngCnt > 0 Then
            MsgBox strMsg, vbInformation, "Please supply all mandatory values"
            Cancel = True
        End If
        
    End Sub
    Notes:
    1. Please check the spelling of the names of the controls in the constant c_FNames, some of them seems to be suspect or misspelled (commerciall?).
    2. According to the style of the form, you'll perhaps need to change the BorderColor and SpecialEffect property values used in the example, specially in the Else part of the test.
    Have a nice day!

  10. #10
    Join Date
    Apr 2014
    Posts
    89

    make a field mandatory to my users

    ok thanks

    I put the code in teh code buider and getting a debug error on the else border color.. I changed it to 0 and then looked in the form propertieis but there is no property for border color and those three so what do i need to change it to, to get it to work.

    Thanks


    how do you set the part you were talking about stopping the form from being closed?

    I understand what you were talking about i think that was for passwords but we are in a state goverment and you can't do things that way they have it locked down..
    My question with passwords is this.. I have a button called Admin which requires a password when you enter the password you then go to the admin switchboard. I want to know is there any way, when you click admin and enter the password to get the navigation pane to open as well. Obviously i dont want the normal end user to have access to all that and wanted to know if there is any way to program that to open..

    Thanks again
    Last edited by dlafko; 04-29-14 at 07:13. Reason: added question

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by dlafko View Post
    I put the code in teh code buider and getting a debug error on the else border color.. I changed it to 0 and then looked in the form propertieis but there is no property for border color and those three so what do i need to change it to, to get it to work.
    What was the error (code + description)? Did this error occurred when you tried to compile the project or when you try to use the application (at run-time)?
    Every TextBox control in Access has a BorderColor property (see: TextBox.BorderColor Property (Access)). If you did not find it it means you were not looking at the right place. It's in the Property windows under the Format tab.
    Have a nice day!

  12. #12
    Join Date
    Apr 2014
    Posts
    89

    make a field mandatory to my users

    Run Time Error 438
    Object doesnt support this property or method.

    occured when i hit the save button

    I then debug and it moves to.border color= vbblack

    i did find the text box color and it is Background 1, Darker 35% for border color and the border style is solid..
    Last edited by dlafko; 04-29-14 at 08:29. Reason: updated

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. There is a difference between how the properties are presented in the Property window and how they can be addressed in VBA (see in the article pointed by the link I posted previously).
    2. Access sometimes report a wrong or unprecise error code for an error that actually exists. Did you check the spelling of the controls in the constant, as adviced previously?
    3. What version of Access do you use? The solution I proposed works both with Access 2003 and Access 2010 (see attached picture).
    Attached Thumbnails Attached Thumbnails ScreenShot036.jpg  
    Have a nice day!

  14. #14
    Join Date
    Apr 2014
    Posts
    89

    make a field mandatory to my users

    I use 2007 I did check the names and i redid them.. Does the fact that Business Name/ Residence Name cause any issues due to the spaces and such? I will look again and get back to you..

    thanks for your patients like I said I am new here.

  15. #15
    Join Date
    Apr 2014
    Posts
    89

    make a field mandatory to my users

    OK..I have started over deleted the code and changed the border color to an RBG code which it seems like you would need to put in ... I am uploading the database so you can look at it.. maybe i am missing something simple and your picture is perfect exactly what i would like to have happen
    Attached Files Attached Files
    Last edited by dlafko; 04-29-14 at 10:22. Reason: UPLOADED DATABASE

Tags for this Thread

Posting Permissions

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