SQL & Combo Box

[Jerry8989]

I have a combo box I have to populate based on a query. I'm also doing an import within my app and if I hard code my query within my drop down I get an error during my import about it being used by someone else. My import effects the table that is used in my drop down. So i'm trying to use the following code to set the query to the combo box

I've tried this with no success

cmb.RowSource = "SELECT VAL, TXT FROM S_V WHERE SID = 1;"
cmb.BoundColumn = 3
cmb.Requery
cmb.SetFocus
cmb.Value = "All"

 

[CameronM]

What happens when you use the code you have posted? Do you still get the error about the data being used by another user?

One quick thing, your query only has 2 fields, yet your BoundColumn is set to use column 3?

It is unusual that a select query would lock the table, so I would check that it really is the combobox, as there may be fields bound to data that needs to be updated as part of your import - if you remove the RowSource altogether, does the import still run?

You could also try clearing the RowSource before running the import.

'clear the RowSource value to allow updating
cmb.RowSource = ""
 
'run your code to import data here
 
'reset the RowSource
cmb.RowSource = "SELECT VAL, TXT FROM S_V WHERE SID = 1;"

 

[Jerry8989]

Cameron,
Thank you for your reply. I did the following and it corrected it. Thanks for pointing out the boundcolumn = 3 that fixed a different problem for that drop down. It didn't allow to be select anything when bound column was 3 now that it's two it works. Here is the code I used to get it to work.

cmb.RowSourceType = "Table/Query"
cmb.RowSource = "SELECT VAL, TXT FROM S_V WHERE SID = 1;"
cmb.BoundColumn = 2
cmb.SetFocus
cmb.Value = "All"

Thanks again
Jerry

 

[Jerry8989]

Cameron,
I ran into a problem with that code I posted.
In that query VAL is supposed to be my drop downs Value and TXT is supposed to be the visible text. Currently the VAL column is showing as the values in the drop down. How can I change that to make VAL the behind the scenes value and TXT as the visible text?

Thanks
Jerry

 

[ACMAIN_CHM]

hi Jerry,

you can either change the SQL like below
cmb.RowSource = "SELECT TXT,VAL FROM S_V WHERE SID = 1;"

or change the binding column and the columnwidth.
cmb.BoundColumn = 1
cmb.ColumnWidths = "0;" 'to hide the first column of VAL, and show the TXT


best regards
ACMAIN

 

[CameronM]

Yes, as ACMAIN suggested, use cmb.BoundColumn = 1 with your current rowsource to link VAL as the value of the combobox instead of TXT.