Changing Combo Box Value Programmatically

[Imran Ghani]

Hi! Im working with MS Access2007 with VBA code. I want to change the value
of combo box programmatically through VBA code. I'll appreciate if someone
could please have a helping advice. Thanks and regards.

 

[Jack Leach]

You should be able to refer to it the same you would a textbox.

Me.YourComboBoxControlName.Value = "something"



If you are talking about the list of items, you would then work with the
Rowsource

Me.ComboControl.Rowsource = "SELECT * FROM ...."

or enter a string of values if it's a value list.



hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Imran Ghani]

Thanks a lot for your prompt reply. I'v got a radio option group and a combo
box. When the values in both these controls are according to my
specification, then I want to have a message to give user an option to change
the value or not, and at the same time if the response of user is change,
then I'd like to change the value of combo box according to my specification.
Please guide, I'll appreciate your valuable help in this regards.

 

[Jack Leach]

It should be something along these lines, assuming the option button control
is ctlOption and the combo control is ctlCombo...

Because you want to check this when either of the two controls is changed, I
would make a private function that you call from the AfterUpdate of each
control. The function will evaluate the values of each control, and return
true if it meets your spec, in which case we'll go to another function that
will change the values to what you want them to. (these functions will keep
us from running duplicate code in the AfterUpdate event of the two controls).


Private Sub ctlOption_AfterUpdate()
If pfCheckValues = True Then
Call psChangeValues
End If
End Sub

Private Sub ctlCombo_AfterUpdate()
If pfCheckValues = True Then
Call psChangeValues
End If
End Sub

Private Function pfCheckValues() As Boolean
If (Me.ctlOption = ??) And (Me.ctlCombo = ??) Then
pfCheckValues = True
Else
pfCheckValues = False
End If
End Function

Private Sub psChangeValues()
Dim i as Integer
i = MsgBox "Do you want to change values?", vbYesNo
'6 is the return for clicking Yes
If i = 6 Then
Me.ctlOption = ??
Me.ctlCombo = ??
End If
End Sub


Replace any ?? with the values you want to check/replace. Be sure to use
quotes if it is a string, or leave them out if it's a number. Ex:

Me.ctlOption = 2
Me.ctlCombo = "ThisValue"

Change the control names to those in your form, and this should get you on
the right track.

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Imran Ghani]

Thanks! That worked great!

It should be something along these lines, assuming the option button control
is ctlOption and the combo control is ctlCombo...

Because you want to check this when either of the two controls is changed, I
would make a private function that you call from the AfterUpdate of each
control. The function will evaluate the values of each control, and return
true if it meets your spec, in which case we'll go to another function that
will change the values to what you want them to. (these functions will keep
us from running duplicate code in the AfterUpdate event of the two controls).


Private Sub ctlOption_AfterUpdate()
If pfCheckValues = True Then
Call psChangeValues
End If
End Sub

Private Sub ctlCombo_AfterUpdate()
If pfCheckValues = True Then
Call psChangeValues
End If
End Sub

Private Function pfCheckValues() As Boolean
If (Me.ctlOption = ??) And (Me.ctlCombo = ??) Then
pfCheckValues = True
Else
pfCheckValues = False
End If
End Function

Private Sub psChangeValues()
Dim i as Integer
i = MsgBox "Do you want to change values?", vbYesNo
'6 is the return for clicking Yes
If i = 6 Then
Me.ctlOption = ??
Me.ctlCombo = ??
End If
End Sub


Replace any ?? with the values you want to check/replace. Be sure to use
quotes if it is a string, or leave them out if it's a number. Ex:

Me.ctlOption = 2
Me.ctlCombo = "ThisValue"

Change the control names to those in your form, and this should get you on
the right track.

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)