Controlling flow of text boxes

[NightWalker]

I have a form that has 3 textboxes. I have the tab index set for the flow to go from box 7 to 14 then to 21. What i am trying to figure out is how to basically loop through the text boxes until they are all full. For example, a user inputs info in 14 and 21 but not 7. I would like to have the focus go to box 7 when the user hits enter key. If any of the boxes are empty go to the next empty box in the loop 7-14-21. Then if all boxes are full go to the submit button. I wrote the following code for one of the boxes but i am not sure if i am on the right track. Would this be easier in a select case format? Thank you for your help. -Walker

Private Sub txtCranSelfResFreq14_AfterUpdate()
    If Trim(txtCranSelfResFreq7 & "") And Trim(txtCranSelfResFreq14 & "") And Trim(txtCranSelfResFreq21 & "") <> "" Then
        If Trim(txtCranSelfResFreq21 & "") = "" Then
            txtCranSelfResFreq21.SetFocus
        Else
            txtCranSelfResFreq21.SetFocus
        End If
    Else
        btnSubmit.SetFocus
    End If
End Sub

[June7]

And how is that code working for you - error message, wrong result, nothing happens?

Use form BeforeUpdate event to validate data and if user has not completed entry, return them to appropriate control.

[NightWalker]

June,

So far it seems to be working ok. I was just wondering if there was a better way to accomplish this. What do you mean by using the before update to validate the data? Thank you.

[June7]

What do you not understand about the suggestion?

Use code in form BeforeUpdate event to check if controls have appropriate data. Cancel record update if not and return user to control that needs input.

This is a very common topic.

[Missinglinq]

One thing you can almost be sure of is that your end users will have their own ideas on how they want to fill in a Record...and it frequently won't be the way you would do so!

Often the 1st and 3rd Controls' data will come from one hard-copy form, while the 2nd and 4th Controls' data may come from another hard-copy sheet...and so they'll input the 1st and 3rd Controls then the 2nd ad 4th.

Some users like using the keyboard for navigation...while others are more mouse-oriented....so you'll probably keep your users happier by letting them decide what order to enter the data in.

So you really need to concentrate on validating that all, in your case, Controls have been populated, using the Form_BeforeUpdate event as @June7 suggested.

There are a number of ways to approach data validation...for a few Controls, you can do this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If Nz(Me.Control1,"") = "" Then
   MsgBox "Control1 Must Not Be Left Blank!"
   Cancel = True
   Control1.SetFocus
   Exit Sub
 End If

If Nz(Me.Control2, "") = "" Then
   MsgBox "Control2 Must Not Be Left Blank!"
   Cancel = True
   Control2.SetFocus
   Exit Sub
 End If
End Sub

and so forth.

You could loop through some or all Controls and do the same thing.

If the number of Controls makes the above too time consuming, given your situation, this will loop through all Textboxes and all Comboboxes and check that they're populated:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control
Dim CName As String

For Each ctl In Me.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox
            If Nz(ctl, "") = "" Then
              CName = ctl.Controls(0).Caption
              MsgBox "Following field is required: " & vbCrLf & vbCrLf & CName
              Cancel = True
              ctl.SetFocus
              Exit Sub
             End If
    End Select
Next ctl
End Sub

You could also use the Tag Property to mark certain Controls, and then loop through all Controls but only check on/address the status of these 'marked' Controls.
To set the Tag Property for multiple Controls, all at once:

1. Go into Form Design View
2. Holding down <Shift> and Left clicking on each Control in turn.
3. Go to Properties – Other and enter Marked in the Tag Property (just like that, no Quotation Marks)

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim ctl As Control
Dim CName As String
For Each ctl In Me.Controls
  If ctl.Tag = "marked" Then
     If Nz(ctl, "") = "" Then
       CName = ctl.Controls(0).Caption
       MsgBox "Following field is required: " & vbCrLf & vbCrLf & CName
       Cancel = True
       ctl.SetFocus
       Exit Sub
     End If
   End If
Next ctl
End Sub

Notice that if Validation in the Form_BeforeUpdate event fails, you set Cancel = True, which aborts the Save, and tell the user where they've gone wrong, setting Focus back to the offending Control.

You could simply mark the Fields as 'Required,' either at the Table or Form level, but most experienced developers avoid this, as the error messages Access gives the users, when Required Fields are left empty can be, shall we say, less than helpful! Validating thru the Form_BeforeUpdate event allows you to pop up custom messages that will actually mean something to your users.

Linq ;0)>

[NightWalker]

June,

Thank you for the suggestion about using the before update event to validate my data. This is not something i have done before. That is why i didn't understand what you were suggesting. I googled it and learned a little more about it. Thank you for your help.

Linq,

Thank you for the detailed explanation. I was not thinking about how the users may perceive or want to enter their data. You have given me a new way to think about things as i move forward. Thank you also for the example code to be able to adapt to my database.

[Missinglinq]

Glad we could help!

Linq ;0)>