Turn on controls when you open a form in Access
I have a form whose controls I want to enable / disable based on the values in the ComboBox control. This ComboBox control is associated, like all other controls on a form, with a table. Inside the ComboBox change event, I put code that enables / disables other controls.
The problem is that when the form is opened, the controls are not enabled / disabled. I need to reselect the ComboBox value to enable or disable all other controls.
One thing I noticed is that the RecordSet control inside the ComboBox often does not change to the value specified in the ComboBox value property.
I tried using
combobox.recordset.filter = "Key = " & combobox.value
but I get the errorOperation is not supported for this type of object.
Update
I think my problem is to do more in the way I access the values in the combobox.recordset. I was under the impression that the combobox.recordset retained the value it got from the table. But it looks like it is holding the first record from the record source.
I am guessing that I will need to search for the values I want using a different Recordset object.
source to share
Most access control events are not triggered by programmatic changes to the control. You can call code to include controls from the form load event.
You don't specify the version of Access you're using, but I don't believe any version has a Recordset property for comboboxes.
Do you want to set the combobox value to a specific value?
source to share
One way to do what you are trying here is to put combobox.change () inside the form.current () method.
It will then act as if the combobox was changed as soon as the form is up and running.
I've done something similar to this before, but I don't have any code at the moment. Once I look at it, I'll post it here in more detail, but from my point of view, I believe this was the way I did it.
source to share
In a commentary, lamcro notes with regard to the question of whether the combed box has a Box Recordset:
When I broke into VB Code Forms and the "Add Watch" CB control, the recordset property is there. I can even go in and see properties.
I see this when I set the browse list but the recordset in the combo box is not available or changed by code. To filter a combo box, you need to work with its Rowsource.
This can be done in one of two ways:
- use event to assign a new Rowsource for your combo boxes on the fly, OR
- use the reference to the control whose value you want to filter in the WHERE clause in the Rowsource of your other combo fields.
Let's say you have a cmbComboBox1 and when you select a value in it you want the values listed in cmbCombBox2 to be filtered according to the value selected in cmbComboBox1. For method 1, you must use the first combo AfterUpdate block to specify the row source of the second:
Private Sub cmbComboBox1_AfterUpdate()
Dim strRowsource As String
strRowsource = "SELECT * FROM MyTable"
If Not IsNull(Me!cmbComboBox1) Then
strRowsource = strRowsource & " WHERE MyField = " & Me!cmbComboBox1
End If
Me!cmbComboBox2.Rowsource = strRowsource
End Sub
To use the second method, you must instead define the Rowsource of the second combo box based on checking the value of the first:
SELECT * FROM MyTable
WHERE (MyField=[Forms]![MyForm]![cmbComboBox1]
AND IsNull([Forms]![MyForm]![cmbComboBox1])=False)
OR IsNull([Forms]![MyForm]![cmbComboBox1])=True
What it means is it filters the source of rows if the first combo box has a value and doesn't filter it if there is a value. That is, you get an unfiltered list until you select a value for the first combo box.
Then, in the Afterupdate event for cmbComboBox1, you must specify a second combo box:
Private Sub cmbComboBox1_AfterUpdate()
Me!cmbComboBox2.Requery
End Sub
Also it would probably be nice to define a parameter to ensure that the link to the form control is resolved appropriately, so your Rowsource will be like this:
PARAMETERS [Forms]![MyForm]![cmbComboBox1] Long;
SELECT * FROM MyTable
WHERE (MyField=[Forms]![MyForm]![cmbComboBox1]
AND IsNull([Forms]![MyForm]![cmbComboBox1])=False)
OR IsNull([Forms]![MyForm]![cmbComboBox1])=True
(assuming you are filtering on Autonumber PK - if the datatype is different, you will of course use a different datatype)
I would like to use dynamic Rowsource assignment, simply because I found it to be less problematic for different versions of Access (i.e., references to controls in forms are not resolved the same in all versions of Access).
source to share
Can you explain what type of combobox you have and what you want to do please? For example, do you have an unrelated pivot that is used to find records for your form, or do you have an associated combo box that is used to update fields in a table? This is controlled by the RowSource and ControlSource properties, Comboboxes do not have a recordset property. An unbound combobox will have no value when the form is opened, and the value will not change when navigating from post to post, but simply assign the value using the current event.
EDIT I still don't understand what you want to do. Are you having trouble changing the controls when you select an option as per your comment, or having trouble setting a combo value when you open a form? Do you want to change the RowSource of the combo, perhaps as implied in your original post?
source to share