ms access populate combo box in a form based on other combo box in another form

0
votes

I'm trying to populate my combo box in a form, based on another 3 combo box selection in another form. I have managed to work it out with one problem.

The code I'm using is:

Private Sub Command715_Click()
DoCmd.OpenForm "SCPrices"

Forms!SCPrices![QuoteRef] = Me![Quote Ref]
Forms!SCPrices![Combo285] = Me![Scope 1] & Me![Scope 2] & Me![Scope 3]
End Sub

It does work fine, however it brings the value of the 3 other combo box as 1 e.g: Scope 1 selected as CCTV, Scope 2 selected as CAD, Scope 3 selected as Survey.

and I get the Combo285 value as "CCTVCADSurvey" instead of individual choice.

What should I use instead of & sign in the code to get the wanted result? Thank you for any help

1
formsms-accesscombobox
Edit question to show the RowSource of Combo285. Of course the code you have is populating with the concatenated values of 3 other comboboxes. If you really want to set Combo285 RowSource based on criteria provided by 3 other comboboxes, that is called cascading comboboxes. – June7
Thanks for the help, just updating – Adrienn Krammer

1 Answers

0
votes

In MS Access, '&' will just concatenate values. If you are looking to create a value list to populate a combobox, then the values must be concatenated, but also separated by a ';'. You may need to wrap your values in Cstr() if they are somehow not strings.

However, you cannot just set a combobox to be a string, Access will not convert this to a valid rowsource, you will just get one value consisting of the string you have defined.

To set the rowsource you must specify the rowsource property itself. Something like this should work, although personally I would pass the values via OpenArgs for use in the form being opened (generally because I am doing much more with the values then just setting a property).

But, this approach works too:

Forms!SCPrices![Combo285].RowSource = Me![Scope 1] & ";" & Me![Scope 2] & ";" & Me![Scope 3]