Coding for testing data and setting focus back to the incorrect value field

[Pumpk1n]

I am relatively new to access forms and have a question. I have a bound subform which I would like to have data edits and bring the focus back to the incorrect field.. As an example, if I leave the award field as '0', the error message displays "Must enter award amount". When I click "OK", I want to bring the user back to the award field on the form. Right now after I click OK - it flows through to "ready to save this record? Yes/No".

Private Sub Command17_Click()
Dim strLocation As String
Me.Modified_by = Environ("USERNAME")
Me.Modified_on = Format(Now(), "yyyy-MM-dd hh:mm:ss")
Me.AwardType = "Sales - Top Award"

If [Award] = 0 Then MsgBox ("Must enter award amount.") ' when there is an error, how to set focus back in the form to this field after they click ok?
If [Year_Earned] = 0 Then MsgBox ("Must enter year.") Else ' same -- how to set focus back to year-earned after they click ok?
Const cstrprompt As String = "Ready to save this record? Yes/No" ' if they select no, then I want to stay on the form and set focus on a specific field?"
If MsgBox(cstrprompt, vbQuestion + vbYesNo) = vbNo Then
Cancel = False
End If
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close
DoCmd.OpenForm FormName:="frm_advisorhomepage"
exit_cmdcommand17_click:
Exit Sub
End Sub

 

[bugmonsta]

You could add multiple If's, and tell it to exit per line or you could have a look at this example Checking all textbox controls on userform for null value

From the above I built (a while ago) this, which checks all my textboxes and comboboxes for a value preset in the variable dt or if they have the text required (its sets this on load if the field is empty/or the user empties the field), if it finds any it will exit the sub

Dim RQ As Control
    [COLOR="#008000"] 'Check if any fields on this form have the text "required" and change them to a black background, and exit the sub
[/COLOR]        For Each RQ In MainForm.MultiPage1.page1.Controls
          If TypeName(RQ) = "ComboBox" Or TypeName(RQ) = "TextBox" Then
            If RQ.Value = dt Or RQ.Value = "REQUIRED" Then
            MsgBox "You have fields on the applicants tab which are required"
                       RQ.SetFocus
            RQ.BackColor = vbBlack
            RQ.ForeColor = vbWhite
             Exit Sub
            Else
End If
End If
     Next RQ

 

[Micron]

Simply invoke the correct control reference Me.Award (not just [Award] and skip the square brackets when not needed and set the focus, then exit the sub or function. Don't use parentheses with the Msgbox function when variables are not involved.

If Me.Award = 0 Then 
 MsgBox "Must enter award amount."
 Me.Award.SetFocus
 Exit Sub
End If

As suggested, there are better ways to do this if you have a whole bunch of them. I reviewed the link and wouldn't suggest using that code in Access. I also don't tend to use default values in a table field, especially if I have to code for not using them. Doesn't make sense.

For Access, I use a custom function that you place in a standard module so that you can call it from anywhere in your project. This makes it so versatile that in fact you can reference that function as you would any other library in the list of vb editor references. You should test for Null and empty strings just to cover both possibilities. Here's one I use. The actual control on the form is passed to the function and its contents are evaluated. The result is passed back to the code that calls it. This presumes one isn't trying to invalidate the use of default values. Obviously, this doesn't apply to checking for zero.

Public Function IsNullEmpty(ctl As Control)

IsNullEmpty = False
If IsNull(ctl) Or ctl = "" Then IsNullEmpty = True

End Function

As an example of a call:

If IsNullEmpty(Me.Award) = 0 Then MsgBox
'do something
End If

If you need to iterate over a large collection of controls, there are better ways than writing many statements like the one I posted.

 

[xenou]

Another link on validating fields, generally speaking:
Validate Text Box Entries | Database Solutions for Microsoft Access | databasedev.co.uk

Set focus is fine but other possibilities would be changing background color or making a small label visible that might say (in red) "*Required". Or you might have an asterisk beside all required fields to begin with so validation messages will not be needed too often).

 

[james_lankford]

check it on each field's BeforeUpdate event

 

[Micron]

Another link on validating fields, generally speaking:
Validate Text Box Entries | Database Solutions for Microsoft Access | databasedev.co.uk

Set focus is fine but other possibilities would be changing background color or making a small label visible that might say (in red) "*Required". Or you might have an asterisk beside all required fields to begin with so validation messages will not be needed too often).

Those are fine ideas, but I suspect the wish was to have the cursor jump to a control rather than having to click on it. Maybe the OP will do both!

At the first link, the message will include the control name. IMHO, not very clear when that is something like txtCust. As one who believes control names and field names should not be the same, it's better to ensure the label is associated/attached to the control and use that text instead. In these cases, I grab the label caption of each offending control and append it to a bulleted list and present that in the message. This prevents the user from having to suffer the same message for each missing value.