Pass Combo Values to an ODBC Parameter........

J

Jonathan

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
 
K

K Dales

Possible and not terribly difficult.
Sample code below - with any needed substitutions could be put in the
ComboBox1_Change event procedure:
With Worksheets("Sheet1").QueryTables(1)
.Parameters(1).SetParam xlConstant, ComboBox1.Value
.Refresh
End With
 
J

Jonathan

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

K Dales

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top