Here is my try (at pasting in what I wrote) I hope it comes out ok:
--------------------------------------------------------------------------------
This is a long-winded approach, but I'm going to post it anyway because I've wanted to address this for some time now. Please note that this is one of several ways that this can be done, and is by no means the easiest. If you can, use the methods described in earlier posts such as one of these:
http://mrexcel.com/board/viewtopic.php?topic=40&forum=2&start=10
http://mrexcel.com/board/viewtopic.php?topic=28124&forum=2 (which has a link to another post)
- or many others if you search...
However, this does work well for the case of exporting data of unknown size to Excel from Access.
--------------------------------------------------------------------------------
Ok, first I'll describe what you'd need to do in Access (as far as data to be put into Excel).
Step1 - Identify unique Sales Managers:
For this, you either need a table that holds unique Sales Managers (each sales mgr is in the table only once), or write a query that gets unique sales managers (Select fldSalesMgr from tblX Group By fldSalesMgr Order By fldSalesMgr).
Step2 - Assign a number from 1 to X for each Sales Manager:
This query is a little bit complex, but fun. Here's an example of the SQL (say I wrote the query above to get unique sales managers and saved it as qrySalesMgrs):
SELECT qrySalesMgrs.fldSalesMgr, (SELECT Count(fldSalesMgr) From qrySalesMgrs A WHERE A.fldSalesMgr<=qrySalesMgrs.fldSalesMgr) AS Sequence
FROM qrySalesMgrs
ORDER BY qrySalesMgrs.fldSalesMgr;
If you want to build this query, add the qrySalesMgrs, then in the first query field, put fldSalesMgr, and sort it ascending. In the second field put:
Sequence: (SELECT Count(fldSalesMgr) From qrySalesMgrs A WHERE A.fldSalesMgr<=qrySalesMgrs.fldSalesMgr)
(my Sales Manager is stored in fldSalesMgr, yours may differ of course).
What this does is it gives you a list of Sales Managers, in Alphabetical order, and a list of numbers in the Sequence field, from 1 to X (where X is the number of Sales Managers). You can also do this in Excel, of course, but why not let Access do the work for you before you spit the data into Excel?
Save this query also.
Step3 - Get a list of Sales Managers and their corresponding Account Managers (with the manager number from above).
This would involve joining the last query we created with a table that has both Sales Managers and Account Managers. I assume you already have this since you want to put them in combo boxes. The resulting output should look something like the first 3 columns below:
OneComboBasedOnOther.xls |
---|
|
---|
| A | B | C | D |
---|
1 | JohnSmith | Suzy | 1 | JohnSmith |
---|
2 | JohnSmith | Todd | 1 | JaneSmith |
---|
3 | JohnSmith | Tommy | 1 | BobRoberts |
---|
4 | JohnSmith | Frazier | 1 | JamesJones |
---|
5 | JaneSmith | Bobby | 2 | RoseCarter |
---|
6 | JaneSmith | Sally | 2 | |
---|
7 | JaneSmith | Johnny | 2 | |
---|
8 | JaneSmith | Jerry | 2 | |
---|
9 | JaneSmith | Sherry | 2 | |
---|
10 | JaneSmith | Sammy | 2 | |
---|
11 | BobRoberts | Alan | 3 | |
---|
12 | BobRoberts | Elaine | 3 | |
---|
13 | BobRoberts | George | 3 | |
---|
14 | BobRoberts | Newman | 3 | |
---|
15 | JamesJones | Jenny | 4 | |
---|
16 | JamesJones | Kosmo | 4 | |
---|
17 | JamesJones | Esmerelda | 4 | |
---|
18 | RoseCarter | Jimmy | 5 | |
---|
19 | RoseCarter | Billy | 5 | |
---|
20 | RoseCarter | Timmy | 5 | |
---|
21 | RoseCarter | Gale | 5 | |
---|
22 | RoseCarter | Gary | 5 | |
---|
23 | RoseCarter | Kramer | 5 | |
---|
24 | RoseCarter | Estelle | 5 | |
---|
25 | RoseCarter | Frank | 5 | |
---|
26 | RoseCarter | Mulva | 5 | |
---|
27 | RoseCarter | Morty | 5 | |
---|
28 | RoseCarter | Dolores | 5 | |
---|
29 | RoseCarter | Norm | 5 | |
---|
|
---|
So we have Sales Manager, Account Manager, and Sales Manager Number in the 3 columns, respectively. And I gather that you already know how to put this information in Excel (let me know if not).
Ok, now let's go to Excel.
Step 4 - Assign a dynamic range for your list of managers (Sales Mgr, Acct Mgr, Sequence):
Go to Insert - Name - Define. Type in a name (I used rngManagers), then type the following formula in the "Refers To" box:
=OFFSET(Sheet2!$A$1,,,COUNTA(Sheet2!$A:$A),COUNTA(Sheet2!$1:$1)-1)
(where Sheet2 is the name of the sheet where you will put the list of managers from your query). This range is outlined in purple above.
Step 5 - Put a list of Sales Managers in Excel.
You can either export this data from Access, or you can use Advanced Filter (Unique Values, copy to another location) on your complete list of managers to get this. Again, I would do this in Access and export to Excel. For my example, the managers are:
John Smith
Jane Smith
Bob Roberts
James Jones
Rose Carter
I have these in Sheet2!D1:D5, and I have inserted a dynamic range for these also (I called my range rngSalesMgrs):
=OFFSET(Sheet2!$D$1,,,COUNTA(Sheet2!$D:$D))
(This is the reason for the minus 1 in the dynamic name above (rngManagers) for the "all managers" list - there's data in Column D, but we don't want to count it for the 3-column list above). This range is outlined in red above.
Step 6 - Hide the sheet with these ranges.
This is obviously optional.
Step 7 - Create 2 Combo Boxes on the sheet you want the user to see.
Create these using the Control Toolbox. For my example, the Sales Manager combo box is ComboBox1, and the one with the Account Managers for that Sales Manager is ComboBox2. In the properties of ComboBox1, set the ListFillRange to rngSalesMgrs. Do not set the ListFillRange of ComboBox2.
Step 8 - Insert code like the following behind the Combo Boxes (see below).
This code will go in the Worksheet module of the worksheet that these combo boxes are on. The best way to insert the code is to (in Design View) right-click on the button, then select "View Code." For more information on Workbook and Worksheet modules click
HERE.<pre><font color='#000000'><font color='#000080'>Option</font><font color='#000080'>Explicit</font></pre><font color='#000080'><pre style="line-height: 100%; margin-top: 0; margin-bottom: 0">Private</font> fRunChange<font color='#000080'>As</font> Boolean</pre><hr align=left width=500><font color='#000080'><pre style="line-height: 100%; margin-top: 0; margin-bottom: 0">Private</font><font color='#000080'>Sub</font> ComboBox1_GotFocus()<font color='#008000'>' When ComboBox1 is selected, the user should be changing it,</font><font color='#008000'>' so set fRunChange to true, and we'll allow the Change Event</font><font color='#008000'>' to fully run.</font>
fRunChange = True<font color='#000080'>End</font><font color='#000080'>Sub</font></pre><hr align=left width=500><font color='#000080'><pre style="line-height: 100%; margin-top: 0; margin-bottom: 0"> </pre><pre style="line-height: 100%; margin-top: 0; margin-bottom: 0">Private</font><font color='#000080'>Sub</font> ComboBox1_LostFocus()<font color='#008000'>' When ComboBox1 doesn't have the focus, don't allow the</font><font color='#008000'>' Change event to fully run (this is in conjunction with the</font><font color='#008000'>' "If Not fRunChange Then Exit Sub" line in ComboBox1_Change).</font>
fRunChange = False<font color='#000080'>End</font><font color='#000080'>Sub</font></pre><hr align=left width=500><font color='#000080'><pre style="line-height: 100%; margin-top: 0; margin-bottom: 0"> </pre><pre style="line-height: 100%; margin-top: 0; margin-bottom: 0">Private</font><font color='#000080'>Sub</font> ComboBox1_Change()<font color='#008000'>' -- We only want to run the change event if ComboBox1</font><font color='#008000'>' -- has been changed. Because ComboBox1's ListFillRange</font><font color='#008000'>' -- is based on a dynamic range, whenever the worksheet is</font><font color='#008000'>' -- changed, the dynamic range is re-calculated, and this</font><font color='#008000'>' -- seems to trigger the change event for this combo box.</font><font color='#008000'>' -- "Not fRunChange" is equivalent to "fRunChange = False"</font><font color='#000080'>If</font><font color='#000080'>Not</font> fRunChange<font color='#000080'>Then</font><font color='#000080'>Exit</font><font color='#000080'>Sub</font><font color='#008000'>' -- Call SetRange, pass it the sequence number of the currently</font><font color='#008000'>' -- selected Sales Manager (a combo box list is zero-based, so</font><font color='#008000'>' -- the first item is item 0. So add one to the ListIndex).</font>
FillAccountMgrs (ComboBox1.ListIndex + 1)<font color='#008000'>' -- I like to select a cell on the sheet after the combo box is</font><font color='#008000'>' -- changed. The reason for this is that Excel can act weird if</font><font color='#008000'>' -- you try to do some things while a combo box is still selected.</font><font color='#008000'>' -- Cell J6 in my workbook is the cell behind my ComboBox1.</font>
ActiveSheet.Range("J6").Select<font color='#000080'>End</font><font color='#000080'>Sub</font></pre><hr align=left width=500><font color='#000080'><pre style="margin-top: 0; margin-bottom: 0"> </pre><pre style="margin-top: 0; margin-bottom: 0">Private</font><font color='#000080'>Function</font> FillAccountMgrs(lngSalesMgr<font color='#000080'>As</font> Long)<font color='#008000'>'*********************************************************************</font><font color='#008000'>' Purpose: Populates ComboBox2 with the Account Managers that</font><font color='#008000'>' correspond to the Sales Manager that is passed in.</font><font color='#008000'>' Inputs:</font><font color='#008000'>' lngSalesMgr: the sequence number of the Sales Manager that has</font><font color='#008000'>' been selected in ComboBox1</font><font color='#008000'>'*********************************************************************</font><font color='#000080'>Dim</font> rngManagers<font color='#000080'>As</font> Range<font color='#000080'>Dim</font> lngRow<font color='#000080'>As</font><font color='#000080'>Long</font><font color='#000080'>Dim</font> lngFirstRow<font color='#000080'>As</font><font color='#000080'>Long</font><font color='#000080'>Dim</font> lngLastRow<font color='#000080'>As</font><font color='#000080'>Long</font><font color='#000080'>Dim</font> lngNumRows<font color='#000080'>As</font><font color='#000080'>Long</font><font color='#008000'>' -- This is the range that we will search to find all Account</font><font color='#008000'>' -- Managers for the passed in Sales Manager.</font><font color='#000080'>Set</font> rngManagers = ThisWorkbook.Names("rngManagers").RefersToRange
ComboBox2.Clear<font color='#008000'>' -- Navigate through the managers range to see which ones</font><font color='#008000'>' -- belong to the currently selected Sales Manager. If</font><font color='#008000'>' -- we get a match, add it to ComboBox2</font><font color='#000080'>For</font> lngRow = 1 To rngManagers.Rows.Count<font color='#000080'>If</font> rngManagers(lngRow, 3) = lngSalesMgr<font color='#000080'>Then</font>
lngFirstRow = lngRow<font color='#000080'>Do</font><font color='#000080'>While</font> rngManagers(lngRow, 3) = lngSalesMgr
ComboBox2.AddItem rngManagers(lngRow, 2)
lngRow = lngRow + 1<font color='#000080'>Loop</font>
lngLastRow = lngRow - 1<font color='#000080'>Exit</font><font color='#000080'>For</font><font color='#000080'>End</font><font color='#000080'>If</font><font color='#000080'>Next</font> lngRow<font color='#008000'>' -- How many managers do we have?</font>
lngNumRows = lngLastRow - lngFirstRow + 1<font color='#008000'>' -- We don't want to show too many rows - if there are more than</font><font color='#008000'>' -- 10 Account Managers, then there will be a scroll bar that the</font><font color='#008000'>' -- user can use to find any that are not in the first 10.</font><font color='#000080'>If</font> lngNumRows > 10<font color='#000080'>Then</font>
lngNumRows = 10<font color='#000080'>End</font><font color='#000080'>If</font><font color='#008000'>' -- Set Combo2's text to its first item.</font>
ComboBox2.Text = ComboBox2.List(0)<font color='#008000'>' -- This is just for cosmetic reasons - show the exact number</font><font color='#008000'>' -- of Acct Mgrs if less than or equal to 10.</font>
ComboBox2.ListRows = lngNumRows<font color='#000080'>End</font><font color='#000080'>Function</font></pre><hr align=left width=500><font color='#000080'><pre>Private</font><font color='#000080'>Sub</font> ComboBox2_Change()<font color='#008000'>' Again, I just like to select a cell on the sheet after</font><font color='#008000'>' changing the combo box. L6 is behind ComboBox2</font>
ActiveSheet.Range("L6").Select<font color='#000080'>End</font><font color='#000080'>Sub</font></pre>
EDIT: One thing I thought of after re-reading this is that you need to export the data to Excel in the locations that I have above (for my example to work, anyway). So the fist query goes on Sheet2 in columns A-C, and the unique sales managers go to column D. You could also put these on separate sheets if you wanted to...
This message was edited by Russell Hauf on 2002-11-07 14:16