j,
Here's what I am doing in my current project. I have several Comboboxes for the user to select a value contained in a table.
For instance the first combobox list Products. As a result of the Product selection, I query the table for a list of contracts associated with the selected product, and display that list in the second combobox.
Here are the elements of the process, which, BTW, uses VBA.
1. The combobox clck event assigns the selected value to a worksheet cell...
Code:
sub combobox1_click()
with combobox1
.topleftcell.value = .value
end with
end sub
2. Use a separate sheet for each query using Data/Get External Data. Set the query up with your Access database to return a SINGLE column with the appropriate criteria. Return the data to Excel. THEN turn on the macro recorder and EDIT the query and return to Excel (you don't actually do anything except get in and out). Turn off the recorder. Then edit the recorded code to substitute the previous selection value for the har-coded value in the SQL array string. In this way the sheet-cell selection will be substituted in the query.
3. Either use a button click event to trigger the query or you can use the Worksheet_change event, but this is alot trickier since there are some pitfalls that come with recursive worksheet change events.
Maybe if you can get this far, (or not) you can post back and get some further guidance.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
Skip,