Subform Navigation

[Funger]

Subform Navigation >Bump<

Hi All.
I have a data entry subform that is in continuous subform view. I would like the user to be able to use the standard navagation buttons. (Arrow Keys, Enter Keys, Tab, And Page Up and Down)

What is the best way to do this?

Here's What my form looks like:

Here is the code that I wrote to try and handle the arrow keys and the enter keys:

Private Sub Form_MoveNext()
____Dim rst1 As Recordset

____Me.Refresh

____Set rst1 = Me.RecordsetClone
____If Not rst1.EOF Then
________rst1.MoveNext
____End If
____If Not rst1.EOF Then
________Me.Bookmark = rst1.Bookmark
____End If
____rst1.Close
End Sub

Private Sub Form_MovePrevious()
____Dim rst1 As Recordset

____Me.Refresh

____Set rst1 = MeRecordsetClone
____If Not rst1.BOF Then
________rst1.MovePrevious
____End If
____If Not rst1.BOF Then
________Me.Bookmark = rst1.Bookmark
____End If
____rst1.Close
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
____Dim ctl As Control

____Set ctl = Screen.ActiveControl

____If KeyCode = 40 Or (KeyCode = 13 And Shift = 0) Then
________Form_MoveNext
____End If

____If KeyCode = 38 Or (KeyCode = 13 And Shift = 1) Then
________Form_MovePrevious
____End If

____If KeyCode = 39 Or (KeyCode = 9 And Shift = 0) Then
________Select Case ctl.Name
____________Case "Score_Q1": Score_Q2.SetFocus
____________Case "Score_Q2": Score_E1.SetFocus
____________Case "Score_E1": Score_Q3.SetFocus
____________Case "Score_Q3": Score_Q4.SetFocus
____________Case "Score_Q4": Score_E2.SetFocus
________End Select
____End If

____If KeyCode = 37 Or (KeyCode = 9 And Shift = 1) Then
________Select Case ctl.Name
____________Case "Score_Q2": Score_Q1.SetFocus
____________Case "Score_E1": Score_Q2.SetFocus
____________Case "Score_Q3": Score_E1.SetFocus
____________Case "Score_Q4": Score_Q3.SetFocus
____________Case "Score_E2": Score_Q4.SetFocus
________End Select
____End If
End Sub



This causes a few problems. First, when I use the up arrow to go to the top record, and then hit the up arrow again... I have to hit the down arrow twice before it will go to the next record. The same is true with the bottom record and the up arrow.

Second, the page up and page down buttons seem to work, but then when I use the arrow keys, the focus jumps back to the last record that had the focus before the pageup key was used.

Third changing the record using the mouse causes the same effect as the page up and page down buttons.

What am I doing wrong?

Also, how can I insert code into a post without losing my indenting?

-Funger

 

[Funger]

No one has any ideas? I would really like this to work in this latest release.

 

[Kevin_S]

Funger - can you wait a day on this?

Reason I ask as I was just re-reading a VBA programming book working on a few solutions and I came across a section detailing how, by use of a macro (and only a macro) you can set any key or combination of keys to preform tasks in Access. There were some pretty detailed steps regarding the naming of the macros and such but I know it works as I tested it a while back... I can look it up again this evening and post tomorrow morning if your interested...

I do indeed know its possible though so don't give up hope

HTH,
Kev

 

[Funger]

Yes, I can wait a day. Thank you for your assistance.

I'm just confused as to why access reacts this way. It's as though when you click on a different record, or use the page up/down keys that it doesn't update the bookmark of the form's recordset.

Also, I don't understand why it navagates to a nonexistant record when I push the up arrow too many times, yet it works just fine if I push the up key just enough times to move it to the top record.

-Funger

 

[Kevin_S]

Short answer is that I don't think there was any/enough error handling nor programming considered with those keys. I think you better off to use he macro solution to trap keystrokes - then write your own event when the keystoke it triggered...

just my 2 cents but I hear your frustration... I'll post the macro code tomorrow (side note* this is the ONLY thing I have found you need macros for but this is another story...)


EDIT* I just reread your last post. The reason Access moves to a nonexistant/new record is that is supposed to function that way i.e. by moving to the end of a recordset the next click will move to an emplty record for data entry same as when you use the navigations buttons standard on each form...

Kev

 

[Fernando]

I'm sorry if its a bad idea to bump and ages old treat, i don't know either if this was answered someday. I had this problem and i have a kind of solution of this.
I created a Module (modFrmRecNav) with these sub procedures

Option Compare Database
Option Explicit
'Facitlitates the navigations of records in a form

Public Sub subPreviousRecord(frmForm As Form)
With frmForm.Recordset
    If Not (.BOF And .EOF) Then
        .MovePrevious
        If .BOF Then
            .MoveFirst
        End If
    End If
End With
End Sub

Public Sub subNextRecord(frmForm As Form)
With frmForm.Recordset
    If Not (.BOF And .EOF) Then
        .MoveNext
        If .EOF Then
            .MoveLast
        End If
    End If
End With
End Sub

Public Sub subNewNextRecord(frmForm As Form)
With frmForm.Recordset
    If Not (.BOF And .EOF) Then
        If Not .EOF Then
            .MoveNext
        End If
        If .EOF Then
            DoCmd.GoToRecord , , acNewRec
        End If
    End If
End With

So on every continuous form that you want to have that option you just need to add this code on the KeyDown event. (Having that your form's keypreview property is set to Yes)

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
     Select Case KeyCode
          Case vbKeyUp
             KeyCode = 0
             Call subPreviousRecord(Me.Form)
         Case vbKeyDown
             KeyCode = 0
             Call subNextRecord(Me.Form)
     End Select
End Sub

Or if its a form where addition is allowed use subNewNextRecord instead of subNextRecord.

Now the reason i found this thread is because i was looking for a workaround a new problem. My new problem is when the users try to expand a combobox using Alt+Down, it will take them to the next record. Does anyone have a lean mean way to accomplish this? And once it is expanded, is there a way to make the down arrow key go thru the combo box options intead of going to the new record?