Solved

KeyDown code for SubForm

Posted on 2002-04-23
21
568 Views
Last Modified: 2011-09-20
I use the following code to let the user move to the next record on a form (frmDonations) by pressing Ctrl-RightArrow:

_______________________________________________________
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
   
    Dim intCtrlDown As Integer
   
    intCtrlDown = (Shift And acCtrlMask) > 0
   
    If (KeyCode = vbKeyRight And intCtrlDown = True) And (cmdGoToNext.Visible = True) Then
        cmdGoToNext_Click
   
    ElseIf (KeyCode = vbKeyLeft And intCtrlDown = True) And (cmdGoToPrevious.Visible = True) Then
        cmdGoToPrevious_Click
   
    End If
   
End Sub
_________________________________________________

cmdGoToNext_Click looks like this:
_________________________________________________

Private Sub cmdGoToNext_Click()

    Check_Allocations
   
    If StopCodeExecution = True Then
        StopCodeExecution = False
        Exit Sub
    Else
        DoCmd.GoToRecord acDataForm, "frmDonations", acNext
    End If

End Sub
_________________________________________________

The main form frmDonations has a subform in it, and the code above works fine if the focus is in the main form frmDonations. But the code doesn't work if the focus is in the subform.

Please can you show me what to do to make the same keystrokes work when the focus is in the subform too. (I want it to move to the next record of the main form frmDonations.)

Thanks.
0
Comment
Question by:naqayya
21 Comments
 

Expert Comment

by:Lex_Luger
ID: 6963051
Hi naqayya, try this way:
set the procedure Form_KeyDown to Public and in subform on OnKeyDown event write: Forms!frmDonations.Form_KeyDown KeyCode, Shift
0
 
LVL 4

Expert Comment

by:zuijdhoek
ID: 6963104


Lex' suggestion will not work because you can't make a form's module public. In order to navigate through the main form records you have to implement following code in the subforms' Form_Keydown mondule. Pay attention to the FindFirst method. Use the primary key value in your main form datasource in order to synchronize both recordsets.

Hope this makes sense to you,

Mark

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
On Error GoTo ErrHandling
   Dim intCtrlDown As Integer
   Dim rst As Recordset
   
   intCtrlDown = (Shift And acCtrlMask) > 0
   
   With Me.Parent.Form
     Set rst = .RecordsetClone
     rst.FindFirst "ItemID=" & ![ItemID]
     
     If (KeyCode = vbKeyRight And intCtrlDown = True) And (.cmdGotoNext.Visible = True) Then
       rst.MoveNext
       .Bookmark = rst.Bookmark
     ElseIf (KeyCode = vbKeyLeft And intCtrlDown = True) And (.cmdGoToPrevious.Visible = True) Then
       rst.MovePrevious
       .Bookmark = rst.Bookmark
     End If
     
   End With
   
ExitHere:
   Set rst = Nothing
   Exit Sub
   
ErrHandling:
   Select Case Err.Number
   Case Else
     MsgBox Err.description
   End Select
   
   Resume ExitHere
   
End Sub
0
 

Author Comment

by:naqayya
ID: 6963177
Mark: Thanks.

I need to carry out a check before moving to the next/previous record. The code I showed you has a call to Check_Allocations in the cmdGoToNext_Click() proc.

What do I have to do to carry out the check too.

Thanks.
0
 

Author Comment

by:naqayya
ID: 6963261
Mark:

I tested your code and I get a 'Type mismatch' error(13) whenever I press any key with focus on the subform. The code line that gives the error is:

Set rst = .RecordsetClone

Any clues?
0
 

Author Comment

by:naqayya
ID: 6965086
Mark:

I figured out how to carry out the check (proc Check_Allocations), but could you help me with the error that I am getting?

Is it something to do with my backend database? My Access project (.adp) is connected to a SQL Server database where the data is stored.

Appreciate your help.
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6965936
Hi nagayya,

>>>but could you help me with the error
that I am getting?

Change:
Set rst = .RecordsetClone

To:
Set rst = Me.RecordsetClone


HTH,

Nosterdamus
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6965953
actualy, I suggest that you be more specific by replacing:
With Me.Parent.Form
    Set rst = .RecordsetClone
    rst.FindFirst "ItemID=" & ![ItemID]
   
    If (KeyCode = vbKeyRight And intCtrlDown = True) And (.cmdGotoNext.Visible = True) Then
      rst.MoveNext
      .Bookmark = rst.Bookmark
    ElseIf (KeyCode = vbKeyLeft And intCtrlDown = True) And (.cmdGoToPrevious.Visible = True) Then
      rst.MovePrevious
      .Bookmark = rst.Bookmark
    End If
   
  End With


with:
    Set rst = Forms![YourMasterFormNameHere].RecordsetClone
    rst.FindFirst "ItemID=" & ![ItemID]
   
    If (KeyCode = vbKeyRight And intCtrlDown = True) And (Forms![YourMasterFormNameHere].cmdGotoNext.Visible = True) Then
      rst.MoveNext
      Forms![YourMasterFormNameHere].Bookmark = rst.Bookmark
    ElseIf (KeyCode = vbKeyLeft And intCtrlDown = True) And (Forms![YourMasterFormNameHere].cmdGoToPrevious.Visible = True) Then
      rst.MovePrevious
      Forms![YourMasterFormNameHere].Bookmark = rst.Bookmark
    End If
   
HTH,

Nosterdamus
0
 

Author Comment

by:naqayya
ID: 6966183
Thanks Nosterdamus:

I'm getting the same error with your new code on the same code line:

Set rst = Forms!frmDonations.RecordsetClone

Any clues?
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6967827
Hi nagayya,

Lets try another approach.

Say that the first TextBox control's name on your main form is MyFirstControl.

Attached is a modification to your existing Form_KeyDown code:

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

   'Just place the real name of the first textbox in the main form between the double quotes.
   'Leave (MyFirstControl) inside the code as is!
   Const MyFirstControl = "PlaceTheRealNameOfTheControlHere"

   Dim intCtrlDown As Integer
   
   intCtrlDown = (Shift And acCtrlMask) > 0
   
   If (KeyCode = vbKeyRight And intCtrlDown = True) And cmdGoToNext.Visible = True) Then
          Forms!frmDonations(MyFirstControl).SetFocus
          cmdGoToNext_Click
   
   ElseIf (KeyCode = vbKeyLeft And intCtrlDown = True) And cmdGoToPrevious.Visible = True) Then
       Forms!frmDonations(MyFirstControl).SetFocus
       cmdGoToPrevious_Click
   
   End If
   
End Sub


Does it work for you?

Nosterdamus
0
 

Author Comment

by:naqayya
ID: 6968283
Nosterdamus: Thanks.

Your new code doesn't compile; I get the error:

Sub or function not defined

for the line cmdGoToNext_Click

Thanks again.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6968562
Hi nagayya,

Your original question included the following code:

Private Sub cmdGoToNext_Click()

   Check_Allocations
   
   If StopCodeExecution = True Then
       StopCodeExecution = False
       Exit Sub
   Else
       DoCmd.GoToRecord acDataForm, "frmDonations", acNext
   End If

End Sub


That means that you have a subroutine called cmdGoToNext_Click. The question is, does this code located in the MainForm or is it located in the child (sub) form?

My guess is that it is located in the subform, as the command:
Forms!frmDonations(MyFirstControl).SetFocus
will set the focus to the main form, and since cmdGoToNext_Click is defined as private sub in the subform, the error message is triggered, as the sub is not visible to the application while executing from the main.

Try to move the cmdGoToNext control (button) and cmdGoToPrev control to the main menu and see if it works for you.

HTH,

Nosterdamus
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6968570
Correction:
Try to move the cmdGoToNext control (button) and cmdGoToPrev control to the main menu and see if it works for you.

Should read:
Try to move the cmdGoToNext control (button) and cmdGoToPrev control to the main FORM and see if it works for you.

0
 

Author Comment

by:naqayya
ID: 6968607
Thanks Nosterdamus:

Sorry for confusing you. The code for cmdGoToNext_Click and cmdGoToPreviuous_Click is in the MAIN FORM ie frmDonations.
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6968633
And where does the following sub located?
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

In the Master or in the Sub form?
0
 

Author Comment

by:naqayya
ID: 6968699
Nosterdamus:

Right lets get this straight:

The two subs in my original question are located in the master form frmDonations.

So they work fine if the focus is on the master form.

Now I want to make a new KeyDown sub in the subform, so that if the focus is in the subform it will make the MASTER FORM'S record move next or move previous.

Hope it is clear now, Nosterdamus, and sorry for the confusion.
0
 
LVL 7

Accepted Solution

by:
Nosterdamus earned 150 total points
ID: 6968758
Ok!

Lets go back to our little routine:

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

  'Just place the real name of the first textbox in the main form between the double quotes.
  'Leave (MyFirstControl) inside the code as is!
  Const MyFirstControl = "PlaceTheRealNameOfTheControlHere"

  Dim intCtrlDown As Integer
 
  intCtrlDown = (Shift And acCtrlMask) > 0
 
  If (KeyCode = vbKeyRight And intCtrlDown = True) And cmdGoToNext.Visible = True) Then
         Forms!frmDonations(MyFirstControl).SetFocus
         cmdGoToNext_Click
 
  ElseIf (KeyCode = vbKeyLeft And intCtrlDown = True) And cmdGoToPrevious.Visible = True) Then
      Forms!frmDonations(MyFirstControl).SetFocus
      cmdGoToPrevious_Click
 
  End If
 
End Sub


If you want to place it in the sub form as well, then you MUST make sure that you have sub cmdGoToNext_Click and cmdGoToPrevious_Click routines in the sub form as well!

Unless, you make 1 global routine for cmdGoToNext_Click and 1 global routine for cmdGoToPrevious_Click, place it in a global Module and the you can call it from ever you want.

Please note that if you choose to create global routines for the Next & Prev, then you'll have to make the following routines and parameters global as well:
Check_Allocations
StopCodeExecution

Otherwize you'll get the compile error as well.

For additional explanation about the scope of parameters (Private, Public etc.), please search the MS Help for "understanding", and pick "understanding scope and visibility" from the list.



Hope that it clears thing a bit for you now...

Nosterdamus
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6968764
Ok!

Lets go back to our little routine:

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

  'Just place the real name of the first textbox in the main form between the double quotes.
  'Leave (MyFirstControl) inside the code as is!
  Const MyFirstControl = "PlaceTheRealNameOfTheControlHere"

  Dim intCtrlDown As Integer
 
  intCtrlDown = (Shift And acCtrlMask) > 0
 
  If (KeyCode = vbKeyRight And intCtrlDown = True) And cmdGoToNext.Visible = True) Then
         Forms!frmDonations(MyFirstControl).SetFocus
         cmdGoToNext_Click
 
  ElseIf (KeyCode = vbKeyLeft And intCtrlDown = True) And cmdGoToPrevious.Visible = True) Then
      Forms!frmDonations(MyFirstControl).SetFocus
      cmdGoToPrevious_Click
 
  End If
 
End Sub


If you want to place it in the sub form as well, then you MUST make sure that you have sub cmdGoToNext_Click and cmdGoToPrevious_Click routines in the sub form as well!

Unless, you make 1 global routine for cmdGoToNext_Click and 1 global routine for cmdGoToPrevious_Click, place it in a global Module and the you can call it from ever you want.

Please note that if you choose to create global routines for the Next & Prev, then you'll have to make the following routines and parameters global as well:
Check_Allocations
StopCodeExecution

Otherwize you'll get the compile error as well.

For additional explanation about the scope of parameters (Private, Public etc.), please search the MS Help for "understanding", and pick "understanding scope and visibility" from the list.



Hope that it clears thing a bit for you now...

Nosterdamus
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6968783
Sorry for the DUP posts...
0
 

Author Comment

by:naqayya
ID: 6968907
Nosterdamus:

Right lets get this straight:

The two subs in my original question are located in the master form frmDonations.

So they work fine if the focus is on the master form.

Now I want to make a new KeyDown sub in the subform, so that if the focus is in the subform it will make the MASTER FORM'S record move next or move previous.

Hope it is clear now, Nosterdamus, and sorry for the confusion.
0
 

Author Comment

by:naqayya
ID: 6969098
Thanks Nosterdamus, it works now.

Much appreciated!
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6974610
Hi nagayya,

Glad I could help & good luck with your app.

;-)

Nosterdamus
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now