Tab order by column

[Leesha]

Hi,
I'm attaching a form here. The user wants to be able to enter the data by column vs by row. For example, the would enter all "Net Sales" and then may go the column labeled "Bev Cup Sales" and enter in all of that data. Is it possible to do this and if so how do I do it?

Thanks!
Leesha

[HansV]

Not quite what you want, but near:

1) Set the Key Preview property of the form to Yes (in the Event tab of the Property Sheet).

2) Create a procedure in the form module or in a standard module:

Code: Select all

Sub HandleTab(KeyCode As Integer, Shift As Integer)
    On Error Resume Next
    If KeyCode = vbKeyTab Then
        KeyCode = 0
        If Shift And acShiftMask = acShiftMask Then
            RunCommand acCmdRecordsGoToPrevious
        Else
            If Me.NewRecord Then
                RunCommand acCmdRecordsGoToFirst
            Else
                RunCommand acCmdRecordsGoToNext
            End If
        End If
    End If
End Sub

3) Create an event procedure for the On Key Down event of each of the text boxes in the Detail section and call HandleTab from them:

Code: Select all

Private Sub txtNetSales_KeyDown(KeyCode As Integer, Shift As Integer)
    HandleTab KeyCode, Shift
End Sub

Private Sub txtEstRegSales_KeyDown(KeyCode As Integer, Shift As Integer)
    HandleTab KeyCode, Shift
End Sub

etc. etc., with the actual names of your text boxes of course.

[Leesha]

Hi Hans,
I've never created a form module before so I figured I create a standard Module. Do I need to name it anything specific
Thanks!
Leesha

[HansV]

You don't have to create a form module yourself - Access creates it automatically for you the moment you create an event procedure for the form or any of its components.

If you create a standard module (select Insert | Module in the Visual Basic Editor): the name of the module doesn't matter except that you shouldn't give it the same name as a procedure (Sub) or function - that would confuse VBA.

[Leesha]

Hi Hans,
It doesn't look like my response posted. I'm so tired from looking at the computer all day I probably forgot to hit submit! First, this is awesome and works exactly the way I hoped it would! You are the best. Does this only work with the tab key or is it possible to put it on the down arrow? I figured I'd ask since it is inevitable that the user will ask for something else.
Thanks!
Leesha

[HansV]

The line

Code: Select all

    If KeyCode = vbKeyTab Then

tests whether the user pressed the Tab key. If you want it to work with the down arrow key, use

Code: Select all

    If KeyCode = vbKeyDown Then

and if you want it to work with both:

Code: Select all

    If KeyCode = vbKeyTab Or KeyCode = vbKeyDown Then

[Leesha]

Thanks Hans!! What awesome code!!!

[Leesha]

Since they use an accounting keyboard they hit the "enter" key and wanted to be able to use that to tab down. I tried "Or KeyCode = vbKeyEnter" to give it a shot but of course it didn't work. I was just stabbing in the dark.
Thanks!
Leesha

[HansV]

It's vbKeyReturn, not vbKeyEnter...

[Leesha]

OH, so close :-)! Thanks