When i put in you code i get run time error 424 object required.
Any ideas
:
Sorry, StuJol. I assumed too much.
Access is "event-driven", meaning a number of different events are
triggered
by user action. For example, when a form opens, the form's OnOpen
event
occurs. When a user updates the value of a control, the control's
BeforeUpdate & AfterUpdate events occur.
When an event occurs, if an event procedure is defined for the
control, then
the procedure is executed. My previous post was Visual Basic code
designed
to be placed in your combo box' AfterUpdate event procedure. To
clarify the
code,
- Me! is shorthand for the name of the current form
- Me![YourStatusComboBox] refers to the combo box control on your
current
form. You will need to substitute the name of your combo box inside
the
brackets.
- The first If..Then looks at the value of your combo box. If it is
equal
to "Closed", it executes the rest of the code; otherwise, it does
nothing.
- Me.Controls refers to the collection of all the controls on your
form.
ctl is a variable of the type Control.
- The For Each...Next loop executes once for each control on the
form. If
the name of the control is the combo box itself, which you presumably
do not
want to disable, it does nothing, otherwise, it checks to see if the
control
is a textbox or combobox (you can't disable a Label, for example); if
it is,
it disables the control, that is, sets its Enabled property to False.
- The Tag property is a non-functional control property that Access
provides that you can use however you want. When I want to disable a
series
of controls on my forms, I set their Tag property to some value, and
test for
the value to decide whether to turn it off or not.
To create your event procedure, open your form in design view. Show
the
properties with View, Properties, then select your combo box. Click
on the
Data tab, and check the Bound Column field.
Now examine the RowSource property, which is an SQL query that fills
the
rows of the combo box. The value of the combo box corresponds to the
BoundColumn (if the BoundColumn = 1, then the value will be the first
field
mentioned in the RowSource query). If it is Bound to a numerical
code,
rather than a text field, you will need to get this value, that
corresponds
to the text "Closed".
On returning to the form, click on the Event tab, and into the
AfterUpdate
field. An ellipsis will appear to the right; click on it and select
Code
Builder (if necessary--depending on your configuration, it may launch
automatically).
Access will create the shell of your procedure. Cut and paste the
code
between the Sub and End Sub lines and make the aforementioned
changes,
inserting your combo box name in two places, keeping the brackets and
quotes.
Save & exit the VBA Editor, and save your form.
Hope that helps.
Sprinks
:
Could you please try and explain in more simple terms as im a
beginner and
are finding it difficult to understand your response
many thanks
:
Hi, StuJol.
I'll assume that you want to keep the original combo box Enabled
so that you
could theoretically change it back. Loop through the controls,
and disable
all textboxes and combo boxes:
Dim ctl as Control
If Me![YourStatusComboBox] = "Closed" Then
For Each ctl In Me.Controls
If ctl.Name<> "YourStatusComboBoxName" Then
If (ctl.ControlType = acTextbox OR ctl.ControlType
=
acComboBox) Then
ctl.Enabled = False
End If
End If
Next ctl
End If
Alternatively, you can use the Tag property to flag the ones
you'd like to
keep open. Enter some text such as "KeepOpen", and modify the
test
accordingly:
If ctl.Tag <> "KeepOpen" Then ...
Also, if the bound column of your combo box is a numeric type,
even though
it displays "Closed", its value might be 1,2, etc., so the test
would be
something like:
If Me![YourStatusComboBox] = 1
Hope that helps.
Sprinks
:
i have a form designed as an invoice, i have a combo box which
is linked to a
table where i get values like "waiting Payment", "closed" etc.
When i select
"closed" i want all my text boxes and combo boxes on my form to
be diabled.
Any ideas???
