Field Tab on values

[hoppo]

I have a form with input fields.

The valid values are 1 - 19 inclusive.

I would like to jump to the next field when the single digits 0 ,2,3,4,5,6,7,8,9 are entered. When 1 is entered wait for the next digit for the values 10 - 19 then jump to next field or press tab to select a single 1

Hope someone can help please

 

[DCrake]

Why? What benefit are you going to get from this apart from a keypress.

You are not giving the user the option of correction of data. Takes more keystrokes to get back to the field to edit the record to revise the data.

What happens if you increase you options to 25?

 

[hoppo]

The max value will always be 19, but, 99% will be less than 9. I have a considerable number of fields on this form and I would like to save keystrokes. I can live with the amendment problem.

 

[DCrake]

You can use the keypress/keydown event of the control to test for which key was pressed and if matches your condition use the DoCmd.SetFocus method to move the cursor to the next field on the form.

Will the user always step through every field on the form sequentially or will they need to skip fields based on what is entered into a previous field?

 

[hoppo]

All fields will be sequential with no dependency on previous fields. I know I can do this with autotab for a field length of two, it is working out how to intercept the single digit input and can I combine code and autotab.

 

[DCrake]

Autotab only works with input masks and the mask has to be complete so entering one digit will not fire the autotab unless they used 01,02,03

 

[missinglinq]

And since "99% will be less than 9," you'd be adding a keystroke in order to 'save' a keystroke, using AutoTab!

Assuming that your users understand that they have to physically tab out of the field when the entered value is intended to be 1 (one), this code should do the job.

Where TargetControl is the control in question and ControlToMoveTo is the next control to be filled in:

Private Sub TargetControl_Change()
 
 If Nz(Me.TargetControl.Text, "") <> "" Then
 
 If Left(Me.TargetControl.Text, 1) <> 1 Then
  Me.ControlToMoveTo.SetFocus
 Else
  If Len(Me.TargetControl.Text) = 2 Then
   Me.ControlToMoveTo.SetFocus
  End If
 End If
 
End If
End Sub

And while this does work, doing it for a large number of controls would be labor intensive.

Linq ;0)>

 

[Galaxiom]

And while this does work, doing it for a large number of controls would be labor intensive.

That would be a lot simpler by creating a more general sub and running it from each control's OnChange event. (AirCode)

Private Sub NextControl ()
 
' Requires a naming pattern of the controls with Prefix and Sequence number
'  leading zeros required in sequence section.
 
Const Prefix = "whatever"    'enter your Prefix
Const MaxSeq = "27"    'enter sequence number for the last control
 
Dim LenSeq As Integer
Dim FormatSeq As String
Dim CurrentSeq As Integer
 
Dim TargetControl as Control
Dim ControlName As String
Dim ControlText As String
Dim NextName As String
 
   TargetControl = Screen.ActiveControl
   ControlName = TargetControl.Name
   ControlText = Nz(TargetControl.Text, "")
 
   If Left(ControlText, 1) <> 1 Or Len(ControlText) = 2 Then
      LenSeq = Len(MaxSequence)
      CurrentSeq = Right(ControlName, LenSeq)
 
      If CurrentSeq < CInt(MaxSeq) Then
         FormatSeq = Replace(Space(LenSeq)," ", "0")
         NextName = Prefix & Format(CurrentSeq + 1, FormatSeq)
         Me.Controls(NextName).SetFocus
      End If
   End If
 
End Sub

LenSeq and FormatSeq could of course be entered as constants to save the calculations. However it is a good idea to calculate such things so the procedure is easily changed by just altering the Prefix and MaxSeq constants.

If using these calculations, the procedure could be made slightly more efficient by using modulewide constants and variables then calculating them OnLoad rather than on every change.

BTW I am sure someone here once posted a way to associate an action with a custom class so that the calling procedure did not have to be applied to each control individually. Any ideas how that is done?

 

[missinglinq]

A function would certainly be easier, assuming that the OP really means that he has a large number of fields all of which have the same range of 1-19 for valid values. I didn't really consider that as a probability, but anything is possible, of course! If it is actually true, I would have to wonder about the table design here.

Linq ;0)>

 

[Galaxiom]

A function would certainly be easier, assuming that the OP really means that he has a large number of fields all of which have the same range of 1-19 for valid values.

Would not have to be all the fields. Only those that call the function and fit the Prefix would get involved.

My posting was somewhat acedemic in response to the suggestion that it would be a lot to impliment. I am a great believer in reusable code hence the example calculated the length rather than using fixed values. Although the exact code might not be particularly useful it did demonstrate principles that someone might find interesting.

If it is actually true, I would have to wonder about the table design here.

Most definitely.

 

[hoppo]

Many thanks for your solutions.

I have already adapted the first suggestion and created a module, my field names are already in the format fld01, fld02 etc so was easy to implement.

I would love to give you the full story on this but I am unable to at this time.

I will say that the figures quoted 1-19 etc are and always will be the requirements. This is an intensive data collection exercise and this solution will reduce input time dramatically

Many thanks to those who contributed.

 

[gemma-the-husky]

i think maybe this is trying to be an old style 3GL look and feel - each field has a number ref, and you offer the user a choice of field to goto and amend

so the easiest way may be to have an unbound "home" field

text: enter field to amend

and then process the entered number with a select statement - it means that after you enter detail in a field, the after update event needs to set the focus back to the "home" edit

it's ok for editing existing data, especially when you have lots of fields, and it means users get keyboard use, rather than having to keep swapping between mouse and keyboard - but less comfortable for entry.


try this example - it's called db3 - just have a look at form1

View attachment db3.zip