Change Value of combobox depending on the selected radio button value

[angybab]

Hi all,
is it possible to fill a combobox with different values depending on what value of radio button selected?

For example I have combo box list sample like this:

Code Meaning
A40 A40Meaning
A40.1 A40.1Meaning
B21 B21Meaning
B21.0 B21.0Meaning
1 Meaning of code 1


2 Meaning of code 2
3 Meaning of code 3

I want a vba code to display only rows with code 1,2 and 3 when I select the radio button 1 and 2
and to display all other rows apart from 1, 2, and 3 when radio button 3 is selected.

On form load the last saved list (record) should be displayed

[June7]

You are describing 'cascading combobox/listbox' - this is a very common topic.

[angybab]

not really, I have in my case only one combobox and want certain rows
to be selected depending on the value of the radio button selected

[orange]

If the values you want in the combo box are resident in a table, then with a button (or several) or an option group on a form, you may be able to (via code) set the rowsource of the combo with a selection based on the button or option group. Untested, but seems possible -- but perhaps there is more to your question.
Show some details to get more focused responses.

[Missinglinq]

not really, I have in my case only one combobox...

Still the same concept.

Linq ;0)>

[angybab]

Hi,
the list is from a table.

so this is a sample code:


Private Sub myRadiobutton_click()
Select Case Me.myRadiobutton

Case 1 Me.txtVariable1.Visible = True
Me.txtVariable2.Visible = True
Me.txtVarVersion.Visible = True

Case 2 Me.txtVariable1.Visible = True
Me.txtVariable2.Visible = True
Me.txtVarVersion.Visible = True

Case 3 Me.txtVariable1.Visible = False
Me.txtVariable2.Visible = False
Me.txtVarVersion.Visible = False

Case 99 Me.txtVariable1.Visible = False
Me.txtVariable2.Visible = False
Me.txtVarVersion.Visible = False


End Select

I need a vba code to display only rows with code length < 2 when button 1 and 2 is selected
otherswise if the code length>2 then to display the rest.





End Sub

[Gicu]

As the others said it is the same concept of cascading "controls", you're just replacing the first combo with an option group. Build a query that returns the appropriate results based on the value of the option group and use that for the row source of the combo. In the Enter event of the combo you will need to add code to requery itself:

Me.cboYourCombo.Requery

Now for the last part (to show the last list on loading of the form) you will need to save the value of the option group somewhere, probably in a local (front-end) "settings" table. Depending on your form setup you could bind it directly to the table/field (probably not if the form is bound to a different table, yes if the main form with the combo and option group is unbound) or most likely you will need code in the AfterUpdate event of the Option Group to run an update SQL statement to save the value.

Cheers,

[June7]

Whenever combobox/listbox list is dependent on another field/control value, behavior is called 'cascading combobox'.

Be aware this will not work nicely with form in continuous or datasheet view.

[angybab]

Hi,
I created 2 queries
the first query has this condition: where length of code =1
the second where length of code > 1
these both queries work fine


Now am strugging with how to make access execute this
If I enter in the code below it doesnt work. Can someone tell
me the best way to call in the queries in each case?

Private Sub myRadiobutton_click()
Select Case Me.myRadiobutton

Case 1
Me.txtVariable1.Visible = True
Me.txtVariable2.Visible = True
Me.txtVarVersion.Visible = True

Me.cboName= DoCmd.OpenQuery "qrysomething1"

Case 2

Me.txtVariable1.Visible = True
Me.txtVariable2.Visible = True
Me.txtVarVersion.Visible = True

Me.cboName= DoCmd.OpenQuery "qrysomething1"

End Select
End Sub

[orange]

Show us the sql for each of your queries. I think your queries are querysomething1 and querysomething2, right?

[angybab]

Hi,
thanks I solved it by using

Me.cboName.Rowsource = "qrysomething1"

am left with the last part Gicu stated


Now for the last part (to show the last list on loading of the form) you will need to save the value of the option group somewhere, probably in a local (front-end) "settings" table. Depending on your form setup you could bind it directly to the table/field (probably not if the form is bound to a different table, yes if the main form with the combo and option group is unbound) or most likely you will need code in the AfterUpdate event of the Option Group to run an update SQL statement to save the value.

[Gicu]

So create a local table in your front-end (lets say tblSettings) with a field called CodeLength. In the AfterUpdate event of the myRadioButton control add

CurrentDb.Execute "UPDATE tblSettings SET CodeLength=" & me.myRadioButton ,dbFailOnError

Then in your form load event add:

Me.myRadioButton =dlookup("[CodeLength]","[tblSettings]")

Also, note that I would move the code that sets the combo's rowsource property from the click event of the radio button to the Enter event of the combo itself.

Cheers,
Vlad

[angybab]

So create a local table in your front-end (lets say tblSettings) with a field called CodeLength. In the AfterUpdate event of the myRadioButton control add

CurrentDb.Execute "UPDATE tblSettings SET CodeLength=" & me.myRadioButton ,dbFailOnError

Then in your form load event add:

Me.myRadioButton =dlookup("[CodeLength]","[tblSettings]")

Also, note that I would move the code that sets the combo's rowsource property from the click event of the radio button to the Enter event of the combo itself.

Cheers,
Vlad

Thanks a lot, I created the table with the Codelength as a new field
but on adding the code to the Afterupdate event of the RadioButton I get an error messege (Debugging error)
CurrentDb.Execute "UPDATE tblSettings SET CodeLength=" & me.myRadioButton ,dbFailOnError
the after load codes works perfectly

[Gicu]

What is the error? Is the new field's data type numeric?

Cheers,

[accesstos]

Also, you can use the SaveSetting statement to store a single value locally and the GetSetting function to retrieve it.

Use the SaveSetting in the Close event of a form and the GetSetting in the Load event of a form.

For example:

Private Sub Form_Close()
    SaveSetting CurrentProject.Name, Me.Name, Me.myRadioButton.Name, Nz(Me.myRadioButton, 0)
End Sub

Private Sub Form_Load()
    Me.myRadioButton = GetSetting(CurrentProject.Name, Me.Name, Me.myRadioButton.Name, 0)
End Sub

Cheers,
John