Page 1 of 2 12 LastLast
Results 1 to 15 of 19

SetFocus after MsgBox

  1. #1
    NISMOJim is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    200

    SetFocus after MsgBox

    Here's a quickie. If there's no easy fix, I'll do without.
    If a user tries to save a form before the required information is entered, they get a message box telling them to finish. I would like to be able to set the focus to the control that needs to be filled out after the user closes the message box. Here's what I tried...

    If Me.txtXacnGroup = " " Or IsNull(Me.txtXacnGroup) Then
    MsgBox "Please enter a Test Group.", vbOKOnly
    Forms!frmXacn!txtXacnGroup.SetFocus
    Exit Sub
    End If

    After clicking OK I get a runtime error 2110 ...can't move the focus to that control. Do I need to make a form instead of a message box to be able to set the focus where I want it, or am I about to learn something new?

    Thanks

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,937
    How about posting all of the code for that procedure so we can determine what you are doing. In what event is this code?
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    NISMOJim is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    200
    OK. There's a lot going on here, but that is the only part that isn't working. The code is on the "on click" event of the "Save" command button.



    Private Sub cmdSave_Click()
    'Save form and open to a blank form


    Dim strForm As String
    Dim strName As String
    Dim DrivPass As Integer
    Dim strSQL1 As String
    Dim strSQL2 As String
    Dim strSQL3 As String
    Dim strSQL4 As String

    strForm = "frmXacn"
    strName = "'" & Me.txtXacnEmpName & "'"
    DrivPass = Nz(DLookup("OnTrackID", "tblOnTrack", "OnTrackName = " & strName))

    'Check for missing data
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~
    If Me.txtXacnEmpName = " " Or IsNull(Me.txtXacnEmpName) Then
    MsgBox "Please enter a Driver name.", vbOKOnly
    Forms!frmXacn!txtXacnEmpName.SetFocus
    Exit Sub
    End If
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~
    If Me.txtXacnGroup = " " Or IsNull(Me.txtXacnGroup) Then
    MsgBox "Please enter a Test Group.", vbOKOnly
    Forms!frmXacn![txtXacnGroup].SetFocus
    Exit Sub
    End If
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~
    If Me.txtXacnEntEx = " " Or IsNull(Me.txtXacnEntEx) Then
    MsgBox "Please enter a selection for Enter/Exit.", vbOKOnly
    Forms!frmXacn!txtXacnEntEx.SetFocus
    Exit Sub
    End If
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~
    If Me.txtXacnEntEx = "Entered" And Me.txtXacnCourse = " " Or Me.txtXacnEntEx = "Entered" And IsNull(Me.txtXacnCourse) Or Me.txtXacnEntEx = "Exited" And Me.txtXacnCourse = " " Or Me.txtXacnEntEx = "Exited" And IsNull(Me.txtXacnCourse) Then
    MsgBox "Please select a Test Course.", vbOKOnly
    Forms!frmXacn!txtXacnCourse.SetFocus
    Exit Sub
    End If
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~
    If Me.txtXacnEntEx = "Entered" And Me.txtXacnType = " " Or Me.txtXacnEntEx = "Entered" And IsNull(Me.txtXacnType) Or Me.txtXacnEntEx = "Exited" And Me.txtXacnType = " " Or Me.txtXacnEntEx = "Exited" And IsNull(Me.txtXacnType) Then
    MsgBox "Please select a Test Type.", vbOKOnly
    Forms!frmXacn!txtXacnType.SetFocus
    Exit Sub
    End If
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~
    If Me.txtXacnEntEx = "Entered" And DrivPass = 0 Then
    Select Case MsgBox("This person is not logged into the TC Gate as a driver. Do you want to continue?", vbYesNo)
    Case vbYes
    DoCmd.Save
    Case vbNo
    Me.txtXacnEmpName = ""
    Me.txtXacnGroup = ""
    Me.txtXacnEntEx = ""
    Me.txtXacnCourse = ""
    Me.txtXacnType = ""
    Me.txtXacnStatus = ""
    Me.txtXacnAuth = ""
    Me.txtXacnCom = ""
    Forms!frmXacn!txtXacnEmpName.SetFocus
    End Select
    End If
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~
    If Me.txtXacnStatus = "Yellow" And Me.txtXacnAuth = " " Or Me.txtXacnStatus = "Yellow" And IsNull(Me.txtXacnAuth) Or Me.txtXacnStatus = "Red" And Me.txtXacnAuth = " " Or Me.txtXacnStatus = "Red" And IsNull(Me.txtXacnAuth) Then
    MsgBox "Please select who authorized the status change.", vbOKOnly
    Forms!frmXacn!txtXacnAuth.SetFocus
    Exit Sub
    End If

    'Write transactions to tblOnTrack
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~
    If Me.txtXacnEntEx = "In T/C Gate" Then
    DoCmd.SetWarnings False
    strSQL1 = "INSERT INTO tblOnTrack (OnTrackName) "
    strSQL1 = strSQL1 & "VALUES (" & strName & "); "
    DoCmd.RunSQL strSQL1
    DoCmd.SetWarnings True
    End If
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~
    If Me.txtXacnEntEx = "Out T/C Gate" Then
    DoCmd.SetWarnings False
    strSQL2 = "DELETE * FROM tblOnTrack WHERE OnTrackName = " & strName
    DoCmd.RunSQL strSQL2
    DoCmd.SetWarnings True
    End If

    'Write status changes to tblStatus & save a reference number in tblXacn
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~
    If Me.txtXacnStatus = "Yellow" Or Me.txtXacnStatus = "Red" Then
    DoCmd.SetWarnings False
    strSQL3 = "INSERT INTO tblStatus (Status, StatCourse, StatName, StatReason)"
    strSQL3 = strSQL3 & "VALUES ('" & Me.txtXacnStatus & "', '" & Me.txtXacnCourse & "', '" & Me.txtXacnEmpName & "', '" & Me.txtXacnType & "'); "
    DoCmd.RunSQL strSQL3
    DoCmd.SetWarnings True
    End If
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~
    If Me.txtXacnStatus = "Green" Then
    DoCmd.SetWarnings False
    strSQL4 = "DELETE * FROM tblStatus WHERE StatName = " & strName
    DoCmd.RunSQL strSQL4
    DoCmd.SetWarnings True
    End If
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~
    If Me.txtXacnEntEx = "Entered" And (Me.txtXacnStatus = "Yellow" Or Me.txtXacnStatus = "Red") Then
    Me.txtXacnStatIn = Me.txtXacnID
    End If
    If Me.txtXacnEntEx = "Exited" And Me.txtXacnStatus = "Green" Then
    Me.txtXacnStatOut = DMax("[XacnStatIn]", "[tblXacn]", "[XacnEmpName] = " & strName)
    End If
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~


    DoCmd.Close
    DoCmd.OpenForm strForm, , , , acFormAdd

    End Sub

    The message box portion is the last thing I added. It works great, it just puts the focus on the first control of the form when closed instead of where I would like it.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,937
    I should start by letting you know that:
    Len(YourControl & "") = 0
    ...is a better way to check for no entry in a control.
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,937
    If this code is on the frmXacn Form then it is much easier and would let the intellisense help if you referenced your controls with the Me. Preface.
    Instead of: Forms!frmXacn![txtXacnGroup].SetFocus
    You would write: Me.txtXacnGroup.SetFocus
    Notice I used a Dot (.) instead of a Bang (!).
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  6. #6
    Missinglinq's Avatar
    Missinglinq is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    1,723
    In addition to Allan's suggestion on the easier way to check for both Nulls and Zero-Length Strings, you've got some other problems.

    First off,

    DoCmd.Save

    does not Save a Record, it saves, any Design Changes made to, in this case, the Form itself!

    To force a Save of a Record you'd use either

    DoCmd.RunCommand acCmdSaveRecord

    or

    If Me.Dirty Then Me.Dirty = False


    Next, you have

    Code:
    DrivPass = Nz(DLookup("OnTrackID", "tblOnTrack", "OnTrackName = " & strName))

    With names of OnTrackName and strName, Iím guessing that these are defined as Text Fields. If this is true, the bit

    "OnTrackName = " & strName

    needs to be

    "OnTrackName = '" & strName & "'"

    You need to do this throughout your code wherever strName is used in a WHERE Clause/Criteria.

    Lastly, this type of Validation Code needs to be in the Form_Update event, not the OnClick event of a Command Button. The only code in the 'Save' button should be one of the bits I gave you above to force a Save.

    By placing the Validation Code in the Form_Update event, you can use

    Cancel = True

    to stop Access from Saving the Record until all Fields are filled in. Placed where you have it, the Record is going to be Saved, even if one or more Controls are empty!

    There may be other problems, but these need to be addressed first.

    Linq ;0)>
    The Devil's in the Details!

  7. #7
    NISMOJim is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    200
    Thanks for all of the advice, I'll put it into practice.
    I tried the Me.txtXacnGroup.SetFocus method first, but it didn't work, instead placing focus back to the first control on the form. I figured the message box must act as a seperate form, making the frmXacn not the active form, that's why i tried Forms!frmXacn![txtXacnGroup].SetFocus. Unfortunately, that does exactly the same thing, placing the focus on the first control.

  8. #8
    Missinglinq's Avatar
    Missinglinq is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    1,723
    Quote Originally Posted by NISMOJim View Post

    ...I tried the Me.txtXacnGroup.SetFocus method first, but it didn't work...]
    In what event did you try it in?

    Linq ;0)>
    The Devil's in the Details!

  9. #9
    NISMOJim is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    200
    I tried putting all of the code on the After Update, then the Before Update events of the form like you suggested (there isn't an event on the form that just says Update), but it didn't save the form, open a new form, or do anything at all. So I put it back on the On Click to finish some changes, and it really does seem to be working good on that event (except for the .SetFocus thing). Since it has to get through all of the If statements to get to the bottom where it closes the form, thus saving the record, wouldn't it be ok to leave it there?

  10. #10
    Rod
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Here's another thought. I tend to separate all pre-update validation into a separate procedure. Here's an example:

    Code:
    Private Function Valid() As Boolean
        On Error GoTo Valid_Error
        Valid = False
        If Len(Trim(Nz(Me.cboManufacturer, ""))) <= 0 Then
            MsgBox "Please select or enter a manufacturer.", vbCritical + vbOKOnly, Me.Caption
            Me.cboManufacturer.SetFocus
            GoTo Exit_Procedure
        ElseIf IsNull(Me.txtEngineType) Then
            MsgBox "Please select an engine type.", vbCritical + vbOKOnly, Me.Caption
            Me.txtEngineType.SetFocus
            GoTo Exit_Procedure
        ElseIf mfrmDatePrompt.PeriodStatus <> "Open" Then
            MsgBox "Please select an acquisition date in an open period.", vbCritical + vbOKOnly, Me.Caption
            Me.sfrDatePrompt.SetFocus
            GoTo Exit_Procedure
        ElseIf Me.txtCost <= 0 Then
            MsgBox "Please enter a cost greater than zero.", vbCritical + vbOKOnly, Me.Caption
            Me.txtCost.SetFocus
            GoTo Exit_Procedure
        ElseIf Me.txtDepreciation <= 0 Or Me.txtDepreciation >= 0.25 Then
            MsgBox "Please enter a depreciation rate greater than zero and less than 25%.", vbCritical + vbOKOnly, Me.Caption
            Me.txtDepreciation.SetFocus
            GoTo Exit_Procedure
        ElseIf Not IsNull(Me.txtDateLastLTO) Then
            If Me.txtDateLastLTO > Date Or Me.txtDateLastLTO < DateAdd("yyyy", -2, Date) Then
                MsgBox "Please enter a registration date within the last two years.", vbCritical + vbOKOnly, Me.Caption
                Me.txtDateLastLTO.SetFocus
                GoTo Exit_Procedure
            End If
        ElseIf Not IsNull(Me.txtDateLastEmission) Then
            If Me.txtDateLastEmission > Date Or Me.txtDateLastEmission < DateAdd("yyyy", -2, Date) Then
                MsgBox "Please enter an emission test date within the last two years.", vbCritical + vbOKOnly, Me.Caption
                Me.txtDateLastEmission.SetFocus
                GoTo Exit_Procedure
            End If
        End If
        Valid = True
    Exit_Procedure:
        On Error GoTo 0
        Exit Function
    Valid_Error:
        Err.Source = "Form_frm06NewTricycle"
        ErrorManager.HandleError "Valid"
        Resume Exit_Procedure
        Resume
    End Function
    (Yes, I know a length cannot be less than zero - it's just a belt and braces thing.)

    Then all I have to do in the form's Before Update event is code:

    Code:
    If Not Me.Valid Then Cancel = True
    Personally I find this elegant and easier to debug.

    PS Ha! Just seen a bug - and this has been around for more than three years! The final two 'ElseIfs' should be separate If - End If constructs.

  11. #11
    NISMOJim is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    200
    Thanks for the advice Rod. Back to the original problem though, why does your code set the focus to a specific control on the form after the messagebox, and mine say that you cannot set the focus to a control on the form? Does it have something to do with the Me.Caption at the end of the messagebox line? I don't know what that is. Again, I also had tried the Me.textboxname.SetFocus method before trying the Forms!FormName!textboxname.SetFocus method, with the same results.

  12. #12
    Rod
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Jim,

    I really don't know. Your code looks OK but without having the complete db it's hard to determine what's going on. From memory, the only times I get the 'can't set focus' or similar is when the target control is disabled or hidden - I presume this does not apply in your case. Also some control types cannot receive the focus such as labels - but again I presume this does not apply in your case.

    I notice you have seven different .SetFocus methods. Do you get the error on all seven? That is, is it something to do with a specific control or something to do with the setup/environment/implementation? I also notice you speak of the focus going to the first control on the form as if there was no error message but the wrong control was activated. Is this also happening?

    Try (on a copy of the form if necessary but preferrably on the production copy) including a new command button in the same section as the existing button. For the click event of the new button: Forms!frmXacn!txtXacnEmpName.SetFocus See what happens when you click the new button.

    I guarantee it is not anything to do with whether you use the 'Me' object or whether you name the form explicitly.

    No, Me.Caption just specifies the form's caption as the caption for the message box.

  13. #13
    NISMOJim is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    200
    You are correct, the controls aren't hidden or disabled, and they are combo boxes (even though they are named txtXacnGroup. Could that be the problem? [see last sentence of this post]). Also, I do get the same error on any of the controls after the message box if they are left empty. I tried the following to keep the error message from popping up in front of the user...

    If Len(Me.txtXacnGroup & "") = 0 Then
    MsgBox "Please enter a Test Group.", vbOKOnly
    On Error Resume Next
    Me.txtXacnGroup.SetFocus
    Exit Sub
    End If

    ...but the focus still always goes to the first control on the form, even though the If statement should be directing focus to (in this case) the second control. I also tried moving the .SetFocus line between the If line and the MsgBox line, but then I get the same error message with the .SetFocus line hilighted when I click Debug. If I click End instead, it's back to that first control. I also tried cutting out the message box altogether, with the same results. When I tried the extra command button like you suggested, it sends the focus to the control with no problem at all.

    Any thoughts before I give up completely?

    P.S. I also noticed your code says GoTo Exit_Procedure where mine says Exit Sub. Anything to that?

  14. #14
    Rod
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Jim,

    No,nothing to do with control names. My code simply uses a label, 'Exit_Procedure' so that the procedure is always exited through the same place.

    Remind me, where are you placing your code? If it is in the BeforeUpdate event of a control then I believe VBA/Access refuses to move away from that control until the update is either performed or cancelled. Trying to set the focus elsewhere would cause a can't go to control type of error.

    Otherwise you need to publish your db.

  15. #15
    NISMOJim is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    200
    For now, the whole procedure is on the "On Click" event of the Save command button. Hopefully I attached the db correctly.
    Attached Files Attached Files

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. setfocus on an textbox different way
    By white_flag in forum Access
    Replies: 0
    Last Post: 09-21-2011, 04:04 AM
  2. setfocus on an textbox
    By white_flag in forum Access
    Replies: 2
    Last Post: 09-21-2011, 03:16 AM
  3. SetFocus problem
    By JvdP in forum Forms
    Replies: 0
    Last Post: 03-16-2011, 07:55 AM
  4. SetFocus Problem
    By ColPat in forum Programming
    Replies: 2
    Last Post: 06-21-2010, 03:43 AM
  5. SetFocus Issue
    By Sinjin in forum Access
    Replies: 0
    Last Post: 02-14-2008, 05:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums