Keypress event in subform

C

cmray58

Registered User.
Local time
Today, 14:21
Joined
Mar 24, 2014
Messages
70
Hello,

I have a form that displays records from a query. I've utilized the following code to allow users to navigate forward and back in records by using the left and right arrow keys on their keyboard:

Code: 
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
On Error GoTo ErrHandler
Select Case KeyCode
    Case vbKeyLeft
        KeyCode = 0
        DoCmd.RunCommand acCmdRecordsGoToPrevious
    Case vbKeyRight
        KeyCode = 0
        DoCmd.RunCommand acCmdRecordsGoToNext
End Select
Exit Sub
ErrHandler:
Select Case Err.Number
    Case 2046
    Case Else
        MsgBox Err.Number & " " & Err.Description
End Select
Resume Next
End Sub

The code works just fine when used upon opening the form. However, one of the fields that users edit for records is actually on a subform. When the user edits this field then tries to navigate with the arrow keys, the code doesn't work (because the focus is no longer on the main form). How do I force the focus back to the main form and/or how do I get the same code to work in the subform (I.e., when key is pressed in the subform, navigate forward or back in the recordset from the main form)?
 
We did something like this. We coded Alt C so that it would close the main form even if focus was in the subform. Looking at our form/subform in question I notice that Key Preview is set to No in the main form and Yes in the subform. That's in the form's properties Event tab, at the bottom. But the code needs to be run in the main form. We move the form close code to a module and called it from the subform or main form. You could probably call the code in the main form without moving it albeit I think it would have to be public. This thread post #7 has
Code: 
Forms("frmTyreMaintenance").cboShowTrailers_AfterUpdate

as an example of how to do that.
 
sneuberg said:
We did something like this. We coded Alt C so that it would close the main form even if focus was in the subform. Looking at our form/subform in question I notice that Key Preview is set to No in the main form and Yes in the subform. That's in the form's properties Event tab, at the bottom. But the code needs to be run in the main form. We move the form close code to a module and called it from the subform or main form. You could probably call the code in the main form without moving it albeit I think it would have to be public. This thread post #7 has
Code: 
Forms("frmTyreMaintenance").cboShowTrailers_AfterUpdate

as an example of how to do that.
Click to expand...


Tried using that code but I'm getting a Run-time 449: argument not optional. I don't know if this effects anything, but I have KeyPreview on for both forms. The code doesn't work at all in the main form when KeyPreview is set to No. Also, I don't really understand the whole module piece of this.

I don't know if maybe this is the problem: when I use the below code, Access is telling Subform1 to run the code from ParentForm in Subform1 . This isn't what I want though; on KeyPress in Subform1, run code from ParentForm in ParentForm since that's where the recrodset is. Am I making any sense?

Code: 
Forms("Provider_Review_Main_Form").Form_KeyDown
 
Last edited:
In the main form you would need to change this
Code: 
[COLOR="Blue"]Private [/COLOR]Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

to
Code: 
[COLOR="blue"]Public [/COLOR]Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

and in the subform's KeyDown event put
Code: 
Forms("Provider_Review_Main_Form").Form_KeyDown [COLOR="Blue"]Keycode, Shift[/COLOR]

so that arguments are satisfied. I'm not sure if the main form Key Preview can be Yes or not. If this doesn't work I suggest trying it set to No
 
cmray58 said:
Also, I don't really understand the whole module piece of this.
Click to expand...

It's probably best to keep it in the form module anyway since code like

Code: 
DoCmd.RunCommand acCmdRecordsGoToPrevious

is meant to be run there.
 
sneuberg said:
In the main form you would need to change this
Code: 
[COLOR="Blue"]Private [/COLOR]Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

to
Code: 
[COLOR="blue"]Public [/COLOR]Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

and in the subform's KeyDown event put
Code: 
Forms("Provider_Review_Main_Form").Form_KeyDown [COLOR="Blue"]Keycode, Shift[/COLOR]

so that arguments are satisfied. I'm not sure if the main form Key Preview can be Yes or not. If this doesn't work I suggest trying it set to No
Click to expand...

Couldn't get this to work with the KeyPress event so I changed it to run the On Click event of the forward and back command buttons I have, but it's still not working. Now I'm getting a "cant go to specified record" error.

The error with the KeyPress attempt was "application-defined or object-defined error"
 
cmray58 said:
Couldn't get this to work with the KeyPress event so I changed it to run the On Click event of the forward and back command buttons I have, but it's still not working. Now I'm getting a "cant go to specified record" error.

The error with the KeyPress attempt was "application-defined or object-defined error"
Click to expand...

Sorry, I've been guessing at this. I'll try to get you a working solution soon.
 
sneuberg said:
Sorry, I've been guessing at this. I'll try to get you a working solution soon.
Click to expand...


Well..I think I've managed a workaround, but now I'm having trouble setting the focus back to the field in the subform.

Code: 
Me![SubformName].Form![FieldName].SetFocus

What do I have wrong here?
 
The following code goes in the subforms KeyDown event as shown. No modification of the parent form is required. This works with the Key Preview of the main and subform set to yes.

Code: 
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

On Error GoTo ErrHandler
Dim rs As DAO.Recordset
Set rs = Me.Parent.RecordsetClone
Select Case KeyCode
    Case vbKeyLeft
        KeyCode = 0
        rs.MovePrevious
    Case vbKeyRight
        KeyCode = 0
        rs.MoveNext
End Select
Me.Parent.Bookmark = rs.Bookmark
Set rs = Nothing

Exit Sub
ErrHandler:
Select Case Err.Number
    Case 3021 'no current record at the first or last record
    Case Else
        MsgBox Err.Number & " " & Err.Description
End Select
Resume Next

End Sub

I don't know why

Code: 
Me![SubformName].Form![FieldName].SetFocus

isn't working for you. It looks right if in the main form.

The previous idea I had was doing what you thought it would do. The code in the parent code was being run as though in were in the subform.
 
sneuberg said:
I don't know why

Code: 
Me![SubformName].Form![FieldName].SetFocus
isn't working for you. It looks right if in the main form.
Click to expand...
First set the focus to the control which hold the subform, then you can set the focus to the control in the subform.
 
JHB said:
First set the focus to the control which hold the subform, then you can set the focus to the control in the subform.
Click to expand...

Can you provide an example? I don't fully understand.
 
sneuberg said:
The following code goes in the subforms KeyDown event as shown. No modification of the parent form is required. This works with the Key Preview of the main and subform set to yes.

Code: 
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

On Error GoTo ErrHandler
Dim rs As DAO.Recordset
Set rs = Me.Parent.RecordsetClone
Select Case KeyCode
    Case vbKeyLeft
        KeyCode = 0
        rs.MovePrevious
    Case vbKeyRight
        KeyCode = 0
        rs.MoveNext
End Select
Me.Parent.Bookmark = rs.Bookmark
Set rs = Nothing

Exit Sub
ErrHandler:
Select Case Err.Number
    Case 3021 'no current record at the first or last record
    Case Else
        MsgBox Err.Number & " " & Err.Description
End Select
Resume Next

End Sub

I don't know why

Code: 
Me![SubformName].Form![FieldName].SetFocus

isn't working for you. It looks right if in the main form.

The previous idea I had was doing what you thought it would do. The code in the parent code was being run as though in were in the subform.
Click to expand...

Genius!! This all works perfectly. Thank you so much! I'll try to pick apart the code myself to get a better understanding of the moving parts. Thanks again!
 
I think if you read this Web page it will help you understand the code. Bookmarks are used frequently to move within a recordset. It's good stuff to know about. You get a lot more control than when you use statements like DoCmd.RunCommand acCmdRecordsGoToPrevious
 
The code posted in post #12 has a flaw in it in that it doesn't copy the bookmark of the current record to the recordset clone. This results in the action of the arrows not being in sync with the record selector of the parent form. The line of code that fixes this is shown in blue.

Code: 
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

On Error GoTo ErrHandler
Dim rs As DAO.Recordset
Set rs = Me.Parent.RecordsetClone
[COLOR="Blue"]rs.Bookmark = Me.Parent.Bookmark[/COLOR]
Select Case KeyCode
    Case vbKeyLeft
        KeyCode = 0
        rs.MovePrevious
    Case vbKeyRight
        KeyCode = 0
        rs.MoveNext
End Select
Me.Parent.Bookmark = rs.Bookmark
Set rs = Nothing

Exit Sub
ErrHandler:
Select Case Err.Number
    Case 3021 'no current record at the first or last record
    Case Else
        MsgBox Err.Number & " " & Err.Description
End Select
Resume Next

End Sub
 
You must log in or register to reply here.

Similar threads

D
Solved Highlighting Next Record After Current Record Filtered Out of Datasheet
Replies
17
Views
2,068
cheekybuddha
cheekybuddha
J
Match the primary Key in the Main form to the foreign key in the Subform
Replies
9
Views
305
MajP
MajP
N
Solved VBA blocking the saving of foreign key and line numbering in the subform and child table
Replies
7
Views
368
Minty
Minty
N
Solved How to scan a barcode from a parent form and update the child/subform in MS access
Replies
4
Views
648
Uncle Gizmo
Uncle Gizmo
A
Form with subform in transaction
2
Replies
39
Views
1,532
Albert D. Kallal
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom