[b]Use button to autopopulate a subform[/b] 1

[chiefywilliams]

I have a form with a subform which is in datasheet view. This is used to record archery scores and has six fields per row which will store values between 0 and 10. I have a set of buttons (10 - 0) on the main form and I would like to be able to merely click on the buttons and see the number generated in the field which has focus. Ideally, the focus would then pass to the next field and so my data entry would be very rapid. Once the last field of one record has been completed I would like the focus to pass to the first field of the next record.

So far I have been unable to work out an effective way of doing this. Tabbing normally achieves the movement between cells - is there a programmatic way of inputting a tab?

As for inputting data to the cells the following works to input a value to a specific field:

Me.frmShootingRecordSub![Arrow1] = 9

But I need a generic way of referencing a field and then transferring a value.

Help would be gratefully received

 

[MajP]

Private Sub cmd1_Click()
  Call setScore(1)
End Sub

Private Sub cmd2_Click()
  Call setScore(2)
End Sub

Private Sub cmd3_Click()
  Call setScore(3)
End Sub

Public Sub setScore(intScore As Integer)
  Dim frm As Access.Form
  Set frm = Me.subFrmCtl.Form
  frm.ActiveControl = intScore
  Select Case frm.ActiveControl.Name
    Case "fldOne"
      frm.Controls("fldTwo").SetFocus
    Case "fldTwo"
      frm.Controls("fldThree").SetFocus
    
    '..............
    Case Else
    
  End Select

 

[chiefywilliams]

Thank you very much this worked perfectly. I added the following to the last field so that it would drop down to next record:

Case "Arrow6"
DoCmd.GoToRecord , , acNext
frm.Controls("Arrow1").SetFocus

Is there an easy way to detect if it is the last record so that I do not get an error when entering a value into the last field of the last line?

 

[MajP]

not sure how you got the above to work. For me the docmd.gotorecord would not work since I call all of the code from the main form not the subform. This is how I did it.

      If Not frm.CurrentRecord = frm.RecordsetClone.RecordCount Then
          frm.Recordset.MoveNext
          frm.Controls("fldOne").SetFocus
      End If

 

[chiefywilliams]

Bingo - thank you once again.