Enable Datasheet SubForm vertical Tab order

[Pete]

I have a datasheet Subform where each column represents a day of the week,
requiring variable entries in the same column each day. Is it possible to so
organise the Tab order (or however else one would do this!) that the cursor
will remain in the same text box column and move vertically down the
datasheet? Thanks.

 

[Pete]

The Tab Stop of all the text boxes in the Datasheet have been switched to
‘No’. Each column represents a day of the week. If the day, e.g., is
Tuesday, then I want the Tab to move the cursor / focus to remain in the
Tuesday column of the datasheet and move down the column to the next record
entry point for ‘Tuesday’ when the Tab button or ‘Enter’ button is clicked.
Is it possible to give me an example of the code that would achieve this if
it cannot be done through the field Property box? (There is a column for each
day of the week). Thanks. .

 

[tina]

well, you might write some code in the subform's Load event to check the day
of the week and set the controls' Tabstop setting. first, open the subform
in Design view, click on the Other tab in the Properties box, and set each
control's Tag property to the appropriate day of the week, as Mon, Tue, Wed,
Thu, Fri, Sat, or Sun. you do *not* need to enclose the text in double
quotes. then add the following code to the subform's Load event procedure,
as

Private Sub Form_Load()

Dim ctl As Control

For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then
ctl.TabStop = (ctl.Tag = Format(Date, "ddd"))
If ctl.TabStop Then ctl.SetFocus
End If
Next

End Sub

the advantage of the above code is that the cursor is not "forced" to a
particular record/control by user action, it's just the Tabstop property
working as designed, to direct the "flow" in the form. the user is free to
click into another control (column) if desired, with no adverse effects.

having given the above solution, i have to question the design of the table
underlying the subform. storing data in fieldnames, such as the days of the
week, is a violation of normalization principles. suggest you read up/more
on normalization, and then re-examine your table structure for possible
improvements. for more information, see
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101.

hth

 

[Pete]

Thanks, Tina - and I will read up about normalisation and try to work out a
better design.

 

[tina]

you're welcome

Thanks, Tina - and I will read up about normalisation and try to work out a
better design.