Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass Combo Values to an ODBC Parameter........
Is this possible???????
I have an ODBC report which I currently refresh with values passed from a comebobox on s form to a cell on a worksheet from which the ODBC parameters refresh the ODBC data on a second worksheet. What I want to do is remove the need to have worksheets to store combobox values to refresh an ODBC I want to pass the values direct thus removing the need to have unecessary worksheets and speed up the report data refreshing - does that make sense & is it relativly easy? Tia Jonathan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass Combo Values to an ODBC Parameter........
Possible and not terribly difficult.
Sample code below - with any needed substitutions could be put in the ComboBox1_Change event procedu With Worksheets("Sheet1").QueryTables(1) .Parameters(1).SetParam xlConstant, ComboBox1.Value .Refresh End With -- - K Dales "Jonathan" wrote: Is this possible??????? I have an ODBC report which I currently refresh with values passed from a comebobox on s form to a cell on a worksheet from which the ODBC parameters refresh the ODBC data on a second worksheet. What I want to do is remove the need to have worksheets to store combobox values to refresh an ODBC I want to pass the values direct thus removing the need to have unecessary worksheets and speed up the report data refreshing - does that make sense & is it relativly easy? Tia Jonathan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass Combo Values to an ODBC Parameter........
Thanks I'll give this a try, can I be cheeky and ask one more question?
In my ComboBox I have user friendly Options i.e. "Birmingham", "Manchester", "Leeds" etc however I want to return the values from the user selection to be "Manx", "LDS", etc how do I do this? "K Dales" wrote: Possible and not terribly difficult. Sample code below - with any needed substitutions could be put in the ComboBox1_Change event procedu With Worksheets("Sheet1").QueryTables(1) .Parameters(1).SetParam xlConstant, ComboBox1.Value .Refresh End With -- - K Dales "Jonathan" wrote: Is this possible??????? I have an ODBC report which I currently refresh with values passed from a comebobox on s form to a cell on a worksheet from which the ODBC parameters refresh the ODBC data on a second worksheet. What I want to do is remove the need to have worksheets to store combobox values to refresh an ODBC I want to pass the values direct thus removing the need to have unecessary worksheets and speed up the report data refreshing - does that make sense & is it relativly easy? Tia Jonathan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass Combo Values to an ODBC Parameter........
Probably the easiest way: make the combobox into a 2 column combobox
(ColumnCount = 2), putting the full name in one column and the abbreviation in the other. You can hide the abbreviation column by setting its width to 0 if you don't want the user to have to see it. Let's say you have the abbreviation in the first column (Column(0) in VBA code) and the name in the other column. Then the code would look like this: With Worksheets("Sheet1").QueryTables(1) .Parameters(1).SetParam xlConstant, ComboBox1.Column(0) .Refresh End With -- - K Dales "Jonathan" wrote: Thanks I'll give this a try, can I be cheeky and ask one more question? In my ComboBox I have user friendly Options i.e. "Birmingham", "Manchester", "Leeds" etc however I want to return the values from the user selection to be "Manx", "LDS", etc how do I do this? "K Dales" wrote: Possible and not terribly difficult. Sample code below - with any needed substitutions could be put in the ComboBox1_Change event procedu With Worksheets("Sheet1").QueryTables(1) .Parameters(1).SetParam xlConstant, ComboBox1.Value .Refresh End With -- - K Dales "Jonathan" wrote: Is this possible??????? I have an ODBC report which I currently refresh with values passed from a comebobox on s form to a cell on a worksheet from which the ODBC parameters refresh the ODBC data on a second worksheet. What I want to do is remove the need to have worksheets to store combobox values to refresh an ODBC I want to pass the values direct thus removing the need to have unecessary worksheets and speed up the report data refreshing - does that make sense & is it relativly easy? Tia Jonathan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pass parameter to access query | Links and Linking in Excel | |||
Pass date parameter into MS Query | Excel Discussion (Misc queries) | |||
Is it possible to pass a parameter into IN operator in Query? | Excel Discussion (Misc queries) | |||
Pass worksheet to macro as a parameter | Excel Programming | |||
Pass parameter to a form and then open it? | Excel Programming |