Setting properties

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In a data entry subform, I would like to set the Enabled property of 14
controls (DayHours1, DayHours2, ...DayHours14) by the value of their
associated Status property (Status1, Status2, ...Status14) with a loop. The
code below generates error 438: "Object does not support this property or
method". Can anyone help?

Private Sub Form_Current()
Dim i As Integer

' Set Enabled properties based on value of Status fields
For i = 1 To 14
Select Case Me("Status" & i)
Case 1
Me("DayHours" & i).Enabled = True
Case 2, 3
Me("DayHours" & i).Enabled = False
End Select
Next i
End Sub
 
Nothing is jumping out at me. Is this code in the current event of the main
form? If so you've got to reference the controls through the form object of
the subform control. For example, if the subform control is named
"sfrmMySub" then you would need to reference the this way:

Select Case Me.sfrmMySub.form("Status" & i)

You really need to think about normalizing this data - anytime you have
repeating groups of fields in a single table it should scream to you to
split it off into another table that is related to the original table. Then
use a subform to view the data. This prevents you from having to make
unnecessary design changes when someone decides that now we need Dayhours15
and Status15.
 
Just a little syntax adjustment is all that is necessary:

For i = 1 To 14
Select Case Me.Controls("Status" & Cstr(i))
Case 1
Me.Controls("DayHours" & Cstr(i)).Enabled = True
Case 2, 3
Me.Controls("DayHours" & Cstr(i)).Enabled = False
End Select
Next i

The Cstr is not absolutely necessary, but I prefer casting my own types
rather than entrusting it to VBA.
 
Sandra,

Thank you for your response. It was indeed a reference issue. The code was
in the subform’s OnCurrent event, however, there are no controls named
DayHoursx and Statusx, they are rather txtDayHoursx and txtStatusx. After
revising the code, it worked, however, I’ve abandoned it in favor of
Conditional Formatting which can work on each row separately.

I totally agree with you about data normalization, and frequently advise
other Discussion Group members in a similar manner. This is an exception
made to simplify data entry for a timesheet application.

We like our current spreadsheet, which has two weeks of days spread across
14 columns, with a row for each project. However, the collection of
spreadsheets (one per user per period) makes it difficult to cull summary
data for billing and other management purposes.

Building a subform based on a normalized detail table would require the user
to enter a project, date, and hours for each record, rather than just
cursoring to the “cell†and entering the number. Such a solution seemed
needlessly inefficient and inelegant.

So I settled on a data entry form that mimics the current one, based on
dummy tables that hold two weeks of data. One parent record exists for each
project manager. When data entry is complete for a given period, the user
presses a “Post†command button, which inserts the dummy records into
normalized tables, empties the dummy records (except for the Projects, which
presumably will be worked on the next week), and resets the Period Start
Date. All summary reporting uses the normalized tables.

When billing, the firm Principal has the option of “stealing†hours that
haven’t been yet posted when he knows that work on the project is complete,
and wishes to avoid sending another invoice next month for the few hours that
were missed. The stolen records are inserted into the normalized tables
immediately, and the associated dummy table Status fields are set to 2,
disabling them and avoiding subsequent posting by the user.

Thanks again for your help.

Sprinks
 
Sandra,

Please see my reply. I accidentally responded to my original post rather
than your reply.

Sprinks

Sandra Daigle said:
Nothing is jumping out at me. Is this code in the current event of the main
form? If so you've got to reference the controls through the form object of
the subform control. For example, if the subform control is named
"sfrmMySub" then you would need to reference the this way:

Select Case Me.sfrmMySub.form("Status" & i)

You really need to think about normalizing this data - anytime you have
repeating groups of fields in a single table it should scream to you to
split it off into another table that is related to the original table. Then
use a subform to view the data. This prevents you from having to make
unnecessary design changes when someone decides that now we need Dayhours15
and Status15.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

In a data entry subform, I would like to set the Enabled property of
14 controls (DayHours1, DayHours2, ...DayHours14) by the value of
their associated Status property (Status1, Status2, ...Status14) with
a loop. The code below generates error 438: "Object does not support
this property or method". Can anyone help?

Private Sub Form_Current()
Dim i As Integer

' Set Enabled properties based on value of Status fields
For i = 1 To 14
Select Case Me("Status" & i)
Case 1
Me("DayHours" & i).Enabled = True
Case 2, 3
Me("DayHours" & i).Enabled = False
End Select
Next i
End Sub
Click to expand...
Click to expand...
 
Back
Top