Tabbing through filled fields in forms

[Melinda Chase]

Hi,
I have an incredibly stupid question. Is it possible to open a form and
have tab stops set themselves up only for fields that are already filled in?
We inspect bridges and the inspection and inventory data is stored in
Access. Each year we look up each bridge and overwrite the inspection data
for the previous year. The form has 66 fields that we have to tab through.
Often 50+ fields are not filled in for a given bridge and will never be.
We'd like to avoid tabbing through those fields. The problem is, the
unfilled fields change from bridge to bridge.
Any suggestions, or shall I tell my inspectors to bite the bullet?
Thanks,
Melinda

 

[George Nicholson]

There's no automatic way.

One way I can think of to do something along those lines is to create a
routine that can be called from the Form_Current event, sorta like this:

Private Sub EnableControls(Optional bolOption as Boolean = False)
' If bolOption = True, user has requested that all controls be
enabled
On Error resume next
Dim ctl as control

For each ctl in Me.Controls
Select Case True
Case ctl.Name = "Some control that we will always want enabled"
' Handle (ignore) any exceptions before you do all
remaining TextBoxes in the next Case
Case ctl.controltype = acTextBox
' We're only disabling/enabling text boxes that weren't
handled by any prior Case statements
if Me.NewRecord or bolOption then
' Enable all controls if this is a NewRecord or at
User's request
ctl.enabled = true
else
' Enable controls with values. Disable empty controls
ctl.enabled = Len(ctl.value)>0
End if
Case else
'Ignore Labels, combo boxes(?), Option buttons, etc.
' They either have no enabled property, or are likely to
always have a value.
End Select
Next ctl
End Sub

- This is air code & i have no idea if this would work.
- Looping through the Controls collection each time you move to a record
will carry a performance hit.
Whether that hit is within acceptable parameters is up to you & your
users.
- You may want to adjust the "appropriate" field types to disable/enable
based on your situation, but I assume we're only worried about TextBoxes.
- You add an "Enable All" button to the form (that calls this with the
optional argument set to True) so that users can get get access to
those fields that are disabled when the need arises (which it will).
- Issue: you can't disable the active control. "On Error Resume Next" is not
a particularly elegant solution, but maybe its sufficient.


HTH,

 

[tina]

sounds like your data table is set up spreadsheet-style, with a field for
every piece of data for every type (?) of bridge that you inspect - with any
single bridge type using only 15-20% of those fields. if that's the case,
you may want to consider harnessing the full power of Access by normalizing
your tables/relationships design; you can learn more about normalization
principles at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

hth

 

[Melinda Chase]

George,
Thanks for the air code. Unfortunately it didn't work, but it's given me a
starting point. I never thought of enabling and disabling the fields. If
you have any more suggestions, please let me know and if I get anything that
works, I'll post it.
Melinda

There's no automatic way.

One way I can think of to do something along those lines is to create a
routine that can be called from the Form_Current event, sorta like this:

Private Sub EnableControls(Optional bolOption as Boolean = False)
' If bolOption = True, user has requested that all controls be
enabled
On Error resume next
Dim ctl as control

For each ctl in Me.Controls
Select Case True
Case ctl.Name = "Some control that we will always want enabled"
' Handle (ignore) any exceptions before you do all
remaining TextBoxes in the next Case
Case ctl.controltype = acTextBox
' We're only disabling/enabling text boxes that weren't
handled by any prior Case statements
if Me.NewRecord or bolOption then
' Enable all controls if this is a NewRecord or at
User's request
ctl.enabled = true
else
' Enable controls with values. Disable empty controls
ctl.enabled = Len(ctl.value)>0
End if
Case else
'Ignore Labels, combo boxes(?), Option buttons, etc.
' They either have no enabled property, or are likely to
always have a value.
End Select
Next ctl
End Sub

<snip>

 

[Melinda]

I got it working. Here's the code I used.

Called from the Form_Current event.

Private Sub EnableControls(Optional bolOption as Boolean = False)
' If bolOption = True, user has requested that all controls be
enabled
On Error resume next
Dim ctl as control

For each ctl in Me.Controls
Select Case True
Case ctl.Name = "Some control that we will always want
enabled"
ctl.Enabled = True
' Handle (ignore) any exceptions before you do all
remaining TextBoxes in the next Case
Case ctl.controltype = acTextBox
' We're only disabling/enabling text boxes that
weren't handled by any prior Case statements
if Me.NewRecord or bolOption then
' Enable all controls if this is a NewRecord or at
User's request
ctl.enabled = true
else
' Enable controls with values. Disable empty
controls
ctl.enabled = IsNull(ctl.Value) = True
End if
Case else
'Ignore Labels, combo boxes, Option buttons, etc.
' They either have no enabled property, or are likely
to always have a value.
End Select
Next ctl
End Sub