add a 2nd field in a cascading combo box

[KevinSlater]

Hello all, I have a cascading combo box that works fine, however I would like the “STK_PART_CODE_DESCRIPTION” field (product description) to be displayed in the combo list to the user also, because the user doesn’t always know what the product is just by the code.

The field ““STK_PART_CODE_DESCRIPTION” is in the query: “SCREEN_ADD_PRODUCT” (The combo box looks at this query).

Below shows what code I currently have that works

Private Sub CBO_SAL_ACCOUNT_AfterUpdate()
On Error Resume Next
CBO_PART_CODE.RowSource = "Select DISTINCT SCREEN_ADD_PRODUCT.STK_PART_CODE " & _
"FROM SCREEN_ADD_PRODUCT " & _
"WHERE SCREEN_ADD_PRODUCT.SAL_ACCOUNT = '" & CBO_SAL_ACCOUNT.Value & "' " & _
"ORDER BY SCREEN_ADD_PRODUCT.STK_PART_CODE;"
End Sub


From this code Ive tried modifying the select clause to the following below amongst others but had no luck so far, Access usually says theirs missing syntax!? (but im not sure what im missing?)

CBO_PART_CODE.RowSource = "Select DISTINCT SCREEN_ADD_PRODUCT.STK_PART_CODE " & _ ““STK_PART_CODE_DESCRIPTION ” &_


CBO_PART_CODE.RowSource = "Select DISTINCT SCREEN_ADD_PRODUCT.STK_PART_CODE " & “-“ “STK_PART_CODE_DESCRIPTION” &_

CBO_PART_CODE.RowSource = "Select DISTINCT SCREEN_ADD_PRODUCT.STK_PART_CODE , STK_PART_CODE_DESCRIPTION ” &_

Any suggestions on how I can fix this would be excellent, thanks kevin.

 

[Ian Mac]

I can't see what wrong with the last one:

Select DISTINCT SCREEN_ADD_PRODUCT.STK_PART_CODE , STK_PART_CODE_DESCRIPTION ” &_

You need to set the Combobox 'Column Count' to 2 for it to be viewed.
It is a better method than concatenating the strings as you will end up with:

1001 - This One
23 - That One
30040234 - The Other

Which I find a bit messy.

The other way will place them neatly in 2 Columns, no awkward spacing.

Hope that helps,

 

[DB7]

I agree with Ian but, in case you do decide to concatenate now,
or in the future;(no need to reference the table, every time)

CBO_PART_CODE.RowSource = _
"Select DISTINCT " & _
"STK_PART_CODE & ', ' & STK_PART_CODE_DESCRIPTION " & _
"FROM SCREEN_ADD_PRODUCT " & _
"WHERE SAL_ACCOUNT = '" & CBO_SAL_ACCOUNT.Value & "' " & _
"ORDER BY STK_PART_CODE;"

 

[KevinSlater]

Thanks a lot Ian and DB7 for your help and advice, its working lovely now
Your right placing the fields in 2 columns is much better than having them the other way. I tried the other way but found it untidy. I’m sure ill be using cascading combo boxes again soon in future databases now.

Cheers

Kevin