Full Version: move to record in continuous form
UtterAccess Forums > Microsoft® Access > Access Forms
Roland
Hi,
need to find a record on my subform without filtering it.
I would not like the subform to shrink to only display the found record.
I would like the subform to just scroll down (move) to the found record.
Appreciate any help.
schroep
CODE
  With Form.RecordsetClone
    .FindFirst "fieldname=criteria"
    If Not .NoMatch Then Form.Bookmark = .Bookmark
  End With

bviously, the usual rules for criteria apply.
Roland
Thanks very much Peter. That worked brilliant.
One more question. Once the record if found, is it possible to get the continuous form to respond to + & - keys to move the record next or previous ?
Appreciate the help
schroep
First, set the KEYPREVIEW property of the continuous form to YES.
ext you need some code in the KEYPRESS event of the FORM to handle those keypresses:
CODE
Private Sub Form_KeyPress(KeyAscii As Integer)
  On Error Resume Next
  If KeyAscii = 43 Then ' +
    DoCmd.GoToRecord , , acNewRec
    KeyAscii = 0
  ElseIf KeyAscii = 45 Then ' -
    DoCmd.GoToRecord , , acPrevious
    KeyAscii = 0
  End If
End Sub

The ON ERROR RESUME NEXT is in there because if you are on the first record and try to go to a "previous" one, or on the last (or new, if additions are allowed) record and try to go to the "next" one, an error will occur. If you'd like to avoid just blanketly turning off error-checking, it takes a little more code to check for all the conditions:
CODE
Private Sub Form_KeyPress(KeyAscii As Integer)
  If KeyAscii = 43 Then ' +
    If Not Me.RecordsetClone.EOF Then Me.RecordsetClone.MoveLast ' Ensure we have an accurate recordcount
    If Me.Recordset.AbsolutePosition + 1 = Me.RecordsetClone.RecordCount Then ' If on the last record
      If Me.AllowAdditions And Not Me.NewRecord Then DoCmd.GoToRecord , , acNewRec ' If additions allowed and not already on a new record
    Else ' Not on the last record
      DoCmd.GoToRecord , , acNext
    End If
    KeyAscii = 0
  ElseIf KeyAscii = 45 Then ' -
    If Me.Recordset.AbsolutePosition > 0 Then DoCmd.GoToRecord , , acPrevious ' If not on the first record
    KeyAscii = 0
  End If
End Sub

Note that by trapping + and -, your users will NOT be able to enter + or - characters in any of the controls on your form. If you need to allow this, either (a) we could switch to the KEYDOWN event and only look for the + and - keys on the numeric keypad, still allowing the ones on the main keyboard to be used to enter characters, or (b) use different keys (such as UPARROW/DOWNARROW) to do your navigation. Let me know if you need to do either of those.
Roland
Thanks again. Yes, in practice I would like to use the numeric +- . Would I have to change the ascii value in the code above ?
schroep
In that case, we use the KEYDOWN event, which can distinguish between the + and - on the main keyboard (next to the numbers) from the ones on the numeric keypad:
CODE
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
  On Error Resume Next
  If KeyCode = vbKeyAdd Then ' +
    DoCmd.GoToRecord , , acNewRec
    KeyCode = 0
  ElseIf KeyCode = vbKeySubtract Then ' -
    DoCmd.GoToRecord , , acPrevious
    KeyCode = 0
  End If
End Sub

or
!--c1-->
CODE
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
  If KeyCode = vbKeyAdd Then ' +
    If Not Me.RecordsetClone.EOF Then Me.RecordsetClone.MoveLast ' Ensure we have an accurate recordcount
    If Me.Recordset.AbsolutePosition + 1 = Me.RecordsetClone.RecordCount Then ' If on the last record
      If Me.AllowAdditions And Not Me.NewRecord Then DoCmd.GoToRecord , , acNewRec ' If additions allowed and not already on a new record
    Else ' Not on the last record
      DoCmd.GoToRecord , , acNext
    End If
    KeyCode = 0
  ElseIf KeyCode = vbKeySubtract Then ' -
    If Me.Recordset.AbsolutePosition > 0 Then DoCmd.GoToRecord , , acPrevious ' If not on the first record
    KeyCode = 0
  End If
End Sub

This way, only the + and - on the numeric keypad will do record scrolling; the regular + and - keys will actually enter those characters.
Roland
Thanks so much Peter
schroep
Glad to help.
Roland
Peter, the above code works great if user does not lose focus of the subform field
After each move (next or previous) using the numeric +-, I do some processing on the subform record "moved to".
The problem is that while the processing is being done the subform field "moved to" loses focus.
How can I return focus to the subform's field "moved to" - after processing that subform record "moved to" ?
subformControlName.Setfocus does not seem to work.
That way the subform is ready to receive numeric +- keydown commands after processing.
Thanks for your help !
schroep
Where is your processing code running? What event, and on which form?
If you are using the AFTERUPDATE event of the control on the subform to which you want to return focus, you have to do a 2-step process; first, set the focus to ANOTHER control, and then set the focus back to the first control.
Roland
Actually it's on subform record doubleclick that I run processing on that subform record.
schroep
If the code is running ON the subform itself, just set the focus to the control you want to have the focus:
ameofcontrol.SetFocus
Note that this is the name of a control ON the subform, not the name of the subform control (on the main form).
Roland
Thanks. In my case, the doubleclick event calls an external module function and I try & return to the subform control using forms![MyForm]![mySubForm]![myControl].setfocus (which does not work, BTW)

How should I setfocus in this case ?
schroep
Try this:
orms![MyForm]!nameofsubformCONTROL.Form.[myControl].setfocus
Roland
wow. this is more complicated than I thought. My subform is a subsubform and the reference to the control looks like this:
Forms![MyMainForm]![SubForm1].Form![subForm2].Form![MyControl].SetFocus
And it still doesn't get the focus...
No error though...
schroep
Is there any chance you can post enough of your MDB to demonstrate the problem?
It first glance, it's either a naming issue (for example, using the name of the saved subform, rather than the name of the control on the holding form, or there is something else somewhere affecting the focus.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.