SetFocus after MsgBox

[NISMOJim]

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

[RuralGuy]

How about posting all of the code for that procedure so we can determine what you are doing. In what event is this code?

[NISMOJim]

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.

[RuralGuy]

I should start by letting you know that:
Len(YourControl & "") = 0
...is a better way to check for no entry in a control.

[RuralGuy]

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 (!).

[Missinglinq]

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

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)>

[NISMOJim]

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.

[Missinglinq]

...I tried the Me.txtXacnGroup.SetFocus method first, but it didn't work...]

In what event did you try it in?

Linq ;0)>

[NISMOJim]

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?

[Rod]

Here's another thought. I tend to separate all pre-update validation into a separate procedure. Here's an example:

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:

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.

[NISMOJim]

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.

[Rod]

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.

[NISMOJim]

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?

[Rod]

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.

[NISMOJim]

For now, the whole procedure is on the "On Click" event of the Save command button. Hopefully I attached the db correctly.