Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Tab up and down bound datasheet 3

Status
Not open for further replies.
diwin

diwin

Technical User
Nov 29, 2002
218
0
0
CA
I have a data entry form with a subform bound to a (temporary) table. Default Tab and Enter behavior moves focus one field to the right. Default Shift-Tab moves focus one to the left. The code below is my attempt to force those defaults to move up and down one record in same column instead. However, since the user is holding down the Shift key, SendKeys {"UpArrow"} is really Shift-[SendKeys {"UpArrow"} ], and by default, this causes the selection to expand upwards. How can I instead get focus to go up one record when Shift-Tab is pressed?

Code: 
Private Sub Text50_KeyDown(KeyCode As Integer, Shift As Integer)
    
    Select Case KeyCode
        Case vbKeyTab
            Select Case Shift
                Case 0
                    SendKeys "{down}", True
                Case acShiftMask
                    SendKeys "{up}" ', True
                Case Else
                    'ignore
            End Select
        Case vbKeyReturn
            Select Case Shift
                Case 0
                    SendKeys "{down}", True
                Case Else
                    'ignore
            End Select
        Case Else
            'Ignore
    End Select

End Sub

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)
 
Sort by date Sort by votes
Much like the combo box wizard that finds a record by making code that uses recordsetclone and bookmark, you should beble to do the same here.
 
Upvote 0 Downvote
Recordsetclone and bookmark are somewhat foreign to me, although I have seen them and sometimes read what they do, their use to slip through the seive that is my brain.

Could you give me a tip what to search for, maybe a method or something?

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)
 
Upvote 0 Downvote
Assuming you have advanced wizards installed (if not fix it)...

Make a new generic bound form using a wizard.

Next add a combo box and use the wizard to find a record in your form (I am not sure what it says exactly).

Look at the code on the afterupdate event.

Instead of using the find method you will movenext or moveprev (embarrased to say I have to guess on that one).
 
Upvote 0 Downvote
Okay. I'm getting there. I just need to be able to land this thing when it gets to the top/bottom of the column.
See the red bits...That is what I would like it to do, somehow.

Code: 
Private Sub Text50_KeyDown(KeyCode As Integer, Shift As Integer)
    
    Dim rs As Object

    Select Case KeyCode
        Case vbKeyTab
            Select Case Shift
                Case 0
                    Set rs = Me.Recordset.Clone
                    rs.FindFirst "[ID] = " & Str(Nz(Me![ID], 0))
                    rs.MoveNext
                    If Not rs.EOF Then
                        Me.Bookmark = rs.Bookmark
                    Else
                        [COLOR=red yellow][rs.MoveFirst][/color]
                    End If
                Case acShiftMask
                    Set rs = Me.Recordset.Clone
                    rs.FindFirst "[ID] = " & Str(Nz(Me![ID], 0))
                    rs.MovePrevious
                    If Not rs.EOF Then
                        Me.Bookmark = rs.Bookmark
                    Else
                        [COLOR=red yellow][rs.Movelast][/color]
                    End If
                Case Else
                    'ignore
            End Select
        Case vbKeyReturn
            Select Case Shift
                Case 0
                    Set rs = Me.Recordset.Clone
                    rs.FindFirst "[ID] = " & Str(Nz(Me![ID], 0))
                    rs.MoveNext
                    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
                Case acShiftMask
                    Set rs = Me.Recordset.Clone
                    rs.FindFirst "[ID] = " & Str(Nz(Me![ID], 0))
                    rs.MovePrevious
                    If Not rs.EOF Then
                        Me.Bookmark = rs.Bookmark
                    Else
                        [COLOR=red yellow][rs.MoveFirst][/color]
                    End If
                Case Else
                    'ignore
            End Select
        Case Else
            'Ignore
    End Select

End Sub

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)
 
Upvote 0 Downvote
Replace this:
[rs.MoveFirst]
with this:
Me.Recordset.MoveFirst

and so on ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
OR fix the repeated code, the particular block certain statements occur and eliminate unnecessary Not's in IF statements...


Code: 
Private Sub Text50_KeyDown(KeyCode As Integer, Shift As Integer)
    
    Dim rs As Object
    Set rs = Me.Recordset.Clone

    Select Case KeyCode
        Case vbKeyTab
            Select Case Shift
                Case 0
                    rs.FindFirst "[ID] = " & Str(Nz(Me![ID], 0))
                    rs.MoveNext
                    If Not rs.EOF Then
                    Else
                        rs.MoveFirst
                    End If
                Case acShiftMask
                    Set rs = Me.Recordset.Clone
                    rs.FindFirst "[ID] = " & Str(Nz(Me![ID], 0))
                    rs.MovePrevious
                    If rs.EOF Then
                         rs.Movelast
                    End If
                Case Else
                    'ignore
            End Select
        Case vbKeyReturn
            Select Case Shift
                Case 0
                    Set rs = Me.Recordset.Clone
                    rs.FindFirst "[ID] = " & Str(Nz(Me![ID], 0))
                    rs.MoveNext
                    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
                Case acShiftMask
                    Set rs = Me.Recordset.Clone
                    rs.FindFirst "[ID] = " & Str(Nz(Me![ID], 0))
                    rs.MovePrevious
                    If rs.EOF Then
                        rs.MoveFirst
                    End If
                Case Else
                    'ignore
            End Select
        Case Else
            'Ignore
    End Select
    Me.Bookmark = rs.Bookmark
    Set RS = Nothing 'Better to destroy it than hope the procedure does
End Sub
 
Upvote 0 Downvote
Thanks lameid.
Code: 
Me.Bookmark = rs.Bookmark
screamed at me. I repeated it as before and it was fine.

Thanks PHV.
"I get run-time error 3021: No current record." at the top of the column.

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)
 
Upvote 0 Downvote
Got it!

Replace 2 instances of .EOF with .BOF.

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)
 
Upvote 0 Downvote
What about this simpler way ?
Code: 
Private Sub Text50_KeyDown(KeyCode As Integer, Shift As Integer)
On Error Resume Next
With Me.Recordset
  Select Case KeyCode
  Case vbKeyTab, vbKeyReturn
    Select Case Shift
    Case 0
      .MoveNext
      If .EOF Then
        .MoveFirst
      End If
    Case acShiftMask
      .MovePrevious
      If .BOF Then
        .MoveLast
      End If
    Case Else
      'ignore
    End Select
  Case Else
    'Ignore
  End Select
End With
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
SWEET. I will have to start using "With..." in my code.

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)
 
Upvote 0 Downvote
Can this be applied to all fields, dynamicallly, and not jsut Text50? I have a subform with several fields in it but allow user to create a few custom fields. Can I make this proc available to the as yet non-existent fields upon their creation by the user?

-------------------------------------
Where would we be if we didn't try?
 
Upvote 0 Downvote
Under "Case acShiftMask" I remmed out ".MoveLast"

Code: 
If .BOF Then
'    .MoveLast
End If
This stopped the focus at the top of the column for Shift-Enter. I like how stopping it from switching columns or from cycling through the records clarifies the sense of place. Is there a way to get Shift-Tab to stop at the top, and for Tab and Enter to stop at the bottom?

-------------------------------------
Where would we be if we didn't try?
 
Upvote 0 Downvote
Clarification: The custom fields are created using VBA. The user just has to enter some values in a form and click on a button to create the form. Users have no access to design view or code window.

-------------------------------------
Where would we be if we didn't try?
 
Upvote 0 Downvote
Code: 
IF RS.BOF or RS.EOF Then
    'Recordset moved to begining or end, do not set the bookmark
Else
    Me.Bookmark = rs.Bookmark
End if

As for doing this for all controls versus one, place the code on the same event on the form instead of on an individual control.
 
Upvote 0 Downvote
Thanks lameid.

This is just what I wanted.

-----------------------------------------
Where would we be if we didn't try?
 
Upvote 0 Downvote
How are ya diwin . . .

I decided to wait until the thread settled down to present this (it was moving at quite a clip!).

Be aware: the [blue]tab keys[/blue] are typically used by users during form navigation (even myself). [purple]I don't think its a good Idea to reprogram such a common keyset.[/purple] [surprise]

To offset using the [blue]tab keys[/blue] I've included in this post a schema that uses the Up/Down [blue]arrow keys[/blue]. If used the tab keys can retain their normal function. Also note: theirs no recordsetclone in the code, just the forms recordset itself. When moving up/down the code is set to loop around and also handles the new record line. So . . .

[ol][li]Be sure te forms [blue]Key Preview[/blue] event is set to [blue]Yes[/blue].[/li]
[li]Copy/paste the following to the forms [blue]On Key Down[/blue] event:
Code: 
[blue]   Dim CurRec As Long, RecCnt As Long
   
   CurRec = Me.CurrentRecord
   RecCnt = Me.Recordset.RecordCount
   
   If RecCnt <> 0 Then
      If Shift = 0 Then
         If KeyCode = vbKeyDown Then
            If Me.NewRecord = True Or CurRec = RecCnt Then
               Me.Recordset.MoveFirst
               KeyCode = 0
            ElseIf CurRec < RecCnt Then
               Me.Recordset.MoveNext
               KeyCode = 0
            End If
         ElseIf KeyCode = vbKeyUp Then
            If Me.NewRecord = True Or CurRec = 1 Then
               Me.Recordset.MoveLast
               KeyCode = 0
            ElseIf CurRec > 1 Then
               Me.Recordset.MovePrevious
               KeyCode = 0
            End If
         End If
      End If
   End If[/blue]
[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TheAceMan1
  • Locked
  • Question
Delete from listi n XL open tab with VBA
Replies
2
Views
115
TheAceMan1
TheAceMan1
Moss100
  • Locked
  • Question
Open New Access Database and Maximise the Application Window
Replies
0
Views
135
Moss100
Moss100
DJD1029
  • Locked
  • Question
Computer Freezing Up
Replies
4
Views
52
xwb
xwb
Dan Stoica
  • Locked
  • Question
Problems with Foxpro dsnless linked tables
Replies
0
Views
76
Dan Stoica
Dan Stoica
Aurillius
  • Locked
  • Question
Convert Date/Time cell to a date and time in VBA
Replies
2
Views
181
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top