Help!!! Form Controls & DB Query's

[Guest]

Hi Folks,

I have two sheets. The first contains a db query with two parameters which
the user is prompted to input for if they refresh the query. My second sheet
is an auto-refreshing query with three columns; first column contains an
order number. the second column contains an order code, and the third column
contains an order description.

On my first sheet I have placed a list box that gets it's input range from
the third column on sheet two (order description), and it's cell link from
the first column (order number) on sheet two.

My thought is to place a button on sheet one next to the list box that will
take the order number associated with the selected order description from the
list box and use that as the parameter for my query.

If possible I'd like to keep the form elements directly on the first sheet
whithout having to resort to a user form if it is more effecient. I'm not
sure how to code the button to take the value I need from the list box.

I have another file which take it's query parameter directly from specific
cells on a sheet but my problem is making the leap from cell selected values
to list box selected values to be used as my parameters for my query.

Any help is always greatly appreciated.

Thanks!

 

[Guest]

cell link from
the first column (order number) on sheet two.

what does that mean. the LinkedCell property is where the Value property of
the listbox is written. See Excel VBA help on BoundColumn for an
illustration of how you can achieve what I think you want. Where it says
controlsource, on the worksheet, that would be the linkedcell.

 

[Guest]

I read up on the cell link and I think i have a better idea of how that
works. so let me pose this...

i have created a second combo box on my first sheet which gives the value of
my second sheets A column that relates to the first combo boxes C column
value. See below...

A B C
1 a a1
2 b b1
3 c c1
4 d d1

So if my first combo box reads "a1" then my second combo box reads "1". If
I change the velue of my first or second combo box, the other returns the
correct value for it's corresponding range...

If box 1 reads then box 2 reads
d1 4
c1 3
a1 1

conversely

If box 2 reads then box 1 reads
1 a1
2 b1
4 d1

Now my question is how can I get the givenvelu of my second combo box to
populate in a given cell that I can have my query reference for it's
parameter?

 

[Tom Ogilvy]

By linking it to the cell where you want the output using the LinkedCell
property.

 

[Guest]

got it!!!

I figured out how to properly use the linked cell and =index formula to
return the value that I am looking for from my 'A' column into a cell that is
hidden behind my combo box.

In the final result, I only needed the single combo box that populates it's
values from my 'C' column. With the linked cell and =index I am getting my
proper value into a cell that i am referencing in my query when my users hit
the refresh button. Everything tested out great.

Thank you for all your help and pointing my in the right direction.