Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2012
    Posts
    126

    Unanswered: Regain focus during on lost focus

    I have a date field that has to be entered. This a text box on a form and it seems theres no way to make it required in the settings. Im using VBA to create and IF statement with msgbox saying that the date is required but after they hit OK the cursor goes to the next field. How do I get the cursor to stay in the text box that lost focus?

    Here is my code
    Private Sub MasterDate_LostFocus()
    If Me.MasterDate <> 0 Then
    Forms![Frmdashboard]![frmEmployeeActivity].Form.Requery
    Else
    MsgBox "Must Enter a Date to Proceed", vbOKOnly
    Forms![frmdashboard].MasterDate.SetFocus
    End If

    End Sub
    Basically saying if the date field is less than/greater than 0 refresh my subform. If not open a message box saying that field is required.
    Last edited by Syrch; 04-23-13 at 11:22.

  2. #2
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    You can do this without VBA.

    1. Open the Form in Design View.
    2. Click on the Date Field to select it.
    3. Press F4 to display the Property Sheet.
    4. Select the Data Tab on the Property Sheet and change the following Property Values as shown below:
      • Input Mask: 99\-99\-0000;;_
      • Validation Rule: >0
      • Validation Text: Enter a Valid Date
    5. Save the Form and Open it in Normal View.
    6. Try pressing Tab Key to move out of the field, leaving the field empty.


    But, if you like to do it with VBA then you need some more code to accomplish that. Do the following:

    Declare a Boolean Variable (say Dim Flag_Err As Boolean) on the top of the Module in the Global Area.
    Modify your Code to set the Flag_Err variable to True as shown below:
    Code:
    Private Sub MasterDate_LostFocus()
    If Me.MasterDate <> 0 Then
    Forms![Frmdashboard]![frmEmployeeActivity].Form.Requery
    Else
    MsgBox "Must Enter a Date to Proceed", vbOKOnly 
      Flag_Err = True
    End If
    
    End Sub
    When Focus is lost from the MasterDate field the focus moves to the next field (based on the next Tab Index Number). You should write a small procedure on the On Got Focus Event procedure of this field to validate the Flag_Err variable and move the focus back on the MasterDate field, if Flag_Err value is set as True.

    Sample procedure is given below. Let me name this next field as myNextField.

    Code:
    Private Sub myNextField_GotFocus()
    If Flag_Err Then
       Flag_Err = False
       Me.MasterDate.SetFocus
    End If
    End Sub
    Last edited by apr pillai; 04-23-13 at 13:22.
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  3. #3
    Join Date
    Aug 2012
    Posts
    126
    actually neither suggestion works if I select the next field with my mouse or tab over. I think this is b/c the masterdate is on the main form and the employeeID is on a subform? I would have imagined the validation rule method would have still worked though.

    I tried to put this code on the on focus event of both the subform and the subforms field (employeeID)

    If Forms![frmdashboard].MasterDate = 0 Then
    Forms![frmdashboard].MasterDate.SetFocus
    End If
    And that doesn't work either ;/


    On my main form i have a time internval of 1000 that flashes text saying the date has to be selected. Could that be interfering?
    Last edited by Syrch; 04-23-13 at 13:57.

  4. #4
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    Check the MasterDate field Validation Settings, disable the Timer Interval settings temporarily and try it out. It should work.
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  5. #5
    Join Date
    Aug 2012
    Posts
    126
    IF you don't mind take a look at the pic. Maybe you see something I'm missing, i've disabled all code related to the time interval and that txtbox.

    I really appreciate the help.
    Attached Thumbnails Attached Thumbnails not validating.png  

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You cannot Validate whether or not a Control is populated using events tied to that Control! All you have to do is simply ignore the Control, i.e. not bother to enter it, and your Validation would be worthless!

    This type of Validation has to be done in the Form_BeforeUpdate event:
    Form_BeforeUpdate event:
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
     If Nz(Me.MasterDate,"") = "" Then
       MsgBox "The Master Date Must Not Be Left Blank!"
       Cancel = True
       MasterDate.SetFocus
       Exit Sub
     End If
    
    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

  7. #7
    Join Date
    Apr 2013
    Posts
    1
    Have you thought about making the field required in the table design? If that is done, then use the error procedure to return him to the date textbox when he tries to move to the next record.

  8. #8
    Join Date
    Aug 2012
    Posts
    126
    Thank you for the replies guys. Linq I have no idea why but Im not even getting the msg saying it needs a date. I did change it to masterdate > 0 then and still no luck.

    parsonshere - this is a control not driven by a form or anything. Making this a table really isn't what i want to do.

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Is this a Bound Form? Just noticed in your posted graphic that MasterDate has no Control Source! Perhaps an explanation of the purpose of this date field is called for.

    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
    Aug 2012
    Posts
    126
    Quote Originally Posted by Missinglinq View Post
    Is this a Bound Form? Just noticed in your posted graphic that MasterDate has no Control Source! Perhaps an explanation of the purpose of this date field is called for.

    Linq ;0)>
    Fair enough question. The reason I need the date field. This database is to track employee productivity at night. The supervisor of the department (i have four of these that feed into a master) will open this up at the beginning of their shift (say 4-24-13 at 8pm) when they open the DP I have append queries that are filling in the names and date (that they entered on another form whch is driven by a table). When they open the EmployeeActivity form (the one you see in the screen shot the employee and date is already filled out so they do not need to put that information in every single night. Now if they have an employee that comes to there department at 4-25-13 at 1am they need to have all records reflect the same date. This is where the date field comes in. I use that date field as the default record for all new records the user will be putting in. They may or may not need to put new records in but I want the system to force them to enter a date into that field so they can't screw it up later.

    In short.

    Employee opens DB enters date
    date gets put into a masterdate table and on enter vba goes to next record (so its stored) and old date gets deleted
    that date and employee names for that department are appended to a local employee activity table
    A form opens that has a header and a date field on it. A subform that is driven by the local employee activity table is also opened and refreshed
    On close the new data gets sent to the master database on the network for management to see and reports to be ran

    All new records need to reflect the same date as the one in the masterdate table.


    Edit: I could make a new dateform from my masterdate table that is hidden and refers to it
    Last edited by Syrch; 04-24-13 at 10:56.

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

    ...Employee opens DB enters date...

    ...date gets put into a masterdate table...
    Sorry, too complicated a scenario for me to really follow, not knowing your job, but a pertinent question is how? How does the MasterDate get put into the MasterDate Table, when, per your attachment, MasterDate is not Bound to a Field/Table, but is, in fact, an Unbound Control?

    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
    Aug 2012
    Posts
    126
    Quote Originally Posted by Missinglinq View Post
    Sorry, too complicated a scenario for me to really follow, not knowing your job, but a pertinent question is how? How does the MasterDate get put into the MasterDate Table, when, per your attachment, MasterDate is not Bound to a Field/Table, but is, in fact, an Unbound Control?

    Linq ;0)>
    I attached a new picture to this message that should hopefully better clear that up. A picture is worth a thousand words right?

    If you look at the picture the top form is what opens when the DB is opened. They have the option to enter new data by selecting a date or looking at old data again by selecting the date. When they select the new data that date is put in for each employee record on the employeeactivity table but not for any new record added to that table. To make it so the employee only has to enter the date one additional time for all new employees put into his department for that night I added an unbound control (masterdate) that is the default value for all new records.
    Attached Thumbnails Attached Thumbnails departmentDB.png  

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

    ...To make it so the employee only has to enter the date one additional time for all new employees put into his department for that night...
    The simplest way to do this is to use the AfterUpdate event of the Control holding your date to set the DefaultValue for the Field. From that time forward, until you either manually change the data or close your form, the data will be entered automatically in each New Record.

    Code:
    Private Sub YourControlName_AfterUpdate()
       Me.YourControlName.DefaultValue = """" & Me.YourControlName.Value & """"
    End Sub

    The user can simply enter the date in the first Employee Activity Record of the night, and as above, it will be entered in each New Record until the user changes the date, the Form is closed or, itself, is closed.

    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

  14. #14
    Join Date
    Aug 2012
    Posts
    126
    yup this works but doesn't make it mandatory for the user to enter a date. Getting the date to popular correctly below is done. Making it so the user can not edit/add any records to the employee activity sub form until the date is entered above is where im having my issue

Posting Permissions

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