How to populate the Row Source of a combo box with a Row Source Type of Value List?

[spenz]

Using VBA to Populate the row source of a Value List as the Row Source Type.

Good day to all. I have a list box named lbxReports with several report names on it and i want to specifically call a set of row source on the chosen report name to the combo box named cboFilterby using Value list as the row source type and this will be triggered via after update event.

I tried to use this (but didn't work at the moment):

Private Sub lbxReports_AfterUpdate()

if me.lbxReports = "Payment Summary" then
me.cboFilterBy.Rowsource = _
"Contact Name";"Account Type";"Transaction Name"

End if

End Sub

 

[speakers_86]

Try me.lbxReports.value in the first line of code.

Also, does the combo box store "Payment Summary", or does it store a number that corresponds to "Payment Summary"? If it stores a number (as it probably should), then your if statement should test for that number, not the text value.

 

[spenz]

Re: Using VBA to Populate a Row Source that has a Value list as the Rowsource Type

Try me.lbxReports.value in the first line of code.

Also, does the combo box store "Payment Summary", or does it store a number that corresponds to "Payment Summary"? If it stores a number (as it probably should), then your if statement should test for that number, not the text value.

Thanks for replying speakers_86.
Payment Summary is also a regular string stored on an unbound list box named lbxReports which happened to have a row source type of a value list as well.

So my aim is to use VBA to populate the combo box named cboFilterby which has a row source type of value list also with the choices "Contact Name"; "Transaction Name"; "Account Type" as soon as i pick "Payment Summary" in the list box named lbxReports.

if i write a code like this in the lbxReports after update event it works (but only for a single choice, but i want other choices to appear as well.):

if me.lbxReports = "Payment Summary" then
me.cboFilterby.Rowsource = "Contact Name"
End if

So i tried thisbut the other names didin't appear only the "Contact Name" part made it to the cboFilterby combo box when i click to see it.)

if me.lbxReports = "Payment Summary" then
me.cboFilterBy.RowSource = "Contact Name";"Transaction Name";"Account Type"
End if

also, access will not compile if i try to and it points out to the " ; " part as the culprit. I tried changing it to comma " , " and other marks but to no avail it just won't compile. meaning i have an expression error.

i tried your suggestion also to put .value after cbofilterby but didn't work.

 

[SOS]

Re: Using VBA to Populate a Row Source that has a Value list as the Rowsource Type

if me.lbxReports = "Payment Summary" then
me.cboFilterBy.RowSource = "Contact Name";"Transaction Name";"Account Type"
End if

also, access will not compile if i try to and it points out to the " ; " part as the culprit. I tried changing it to comma " , " and other marks but to no avail it just won't compile. meaning i have an expression error.

You don't separate the rowsource value list items with quotes. You would use:

.RowSource = "Contact Name;TransactionName;Account Type"

and before you set it, if it is already not set to use a value list you would need:

Me.YourComboName.RowSourceType = "Value List"
Me.YourComboName.RowSource = "Contact Name;TransactionName;Account Type"

 

[spenz]

Re: Using VBA to Populate a Row Source that has a Value list as the Rowsource Type

You don't separate the rowsource value list items with quotes. You would use:

.RowSource = "Contact Name;TransactionName;Account Type"

and before you set it, if it is already not set to use a value list you would need:

Me.YourComboName.RowSourceType = "Value List"
Me.YourComboName.RowSource = "Contact Name;TransactionName;Account Type"

Thanks SOS I will try it as soon as i get home. This looks promising.

 

[spenz]

Thanks SOS! Your suggestion worked perfectly.

 

[SOS]

Thanks SOS! Your suggestion worked perfectly.

Happy to hear.