Array Problem

[Dave_cha]

I've used dlookup to seach 1 field against a given criteria. The dlookup should produce multiple string returns.

I want to store these returns in an array and later feed the contents of this array into a listbox.

It's important that I have the array between the table and listbox so simply setting up a listbox with a rowsource is not an option.

Can anyone help me with the code to feed the dlookup returns into the array (dlookup might not be the best way to go here) and also the code to push the contents of the array into a listbox.

Thanks,

Dave

 

[The_Doc_Man]

Listboxes come in numerous forms. If you are NOT looking up the data from a table, basically the non-table-source format uses semi-colons for separators of the displayed value list elements and a comma (if I remember correctly) to set apart the displayed value from the associated value to be stored once an entry is selected. So I propose an experiment for you.

On a dummy form, build a dummy listbox using the wizard. Let it ask you for a list of values. The wizard will ask you to enter what you want the listbox to say and what you want to store when someone selects that particular displayed value. Save the result. (Make more than one entry, just for giggles and to play with the syntax requirements.) Then in design mode, go back in and look at the properties of the list box for the DATA tab. For the recordsource, you will see the syntax used for your value choices. Since you yourself entered those choices and know what they were, you will get the hint as to what Access wants that to look like.

Issues to consider:

1. Building a string from your array - string concatenation is probably your best bet. Be careful to get the separators right. The "&" operator is going to be your friend here. Watch out for strings with embedded special characters, blanks, and punctuation. The pitfall isn't that these characters DON'T work; it is that you have to be more careful when you have tricky values. Access pays attention to some punctuation more critically than other punctuation. Quote-marks, for example. The ampersand for another.

2. Be careful in how you manage the value returned for the given displayed value from your list. Access won't care that you duplicate associated values, so if you don't store the value right, you won't get the right value back when you select the displayed value.

3. Be aware that you might run into a string size limit, but I'm not sure which of several limits apply.

simply setting up a listbox with a rowsource is not an option.

In any case, if you have more than ten values, you could be wrong to say that a table is not acceptable. If it is more than twenty, you ARE DEFINITELY, CATEGORICALLY wrong to reject a table as your listbox source. Rethink this carefully. If you come up with the same answer, think again. Because if you are going to build a big, whompin' non-tabular list, you are INVITING errors and limitations to creep in.

4. There is always more than one way to skin a cat. {MEEOOORRWWW!} Look into this alternative. When you are going to build your list for your listbox, do this.

a. Design the list to come from a table used for no other purpose than feeding this listbox. Set up required indexes so the listbox can be efficient. (And bear with me on this, despite your aversion to a table solution.)

b. When it is time to alter the table contents, ERASE the table.

c. Now open the table via a table-type recordset.

d. For each value you WOULD have added in string concatenation as I described above, instead do an AddNew on the recordset and put the individual string in the table along with its associated value.

Doing it the latter way means you have much larger limits on how many options you can enter for the listbox. It is now arbitrary and limited by the size of your disk's free space.

If your issue is that this is a shared DB, I understand why a table is not your preferred answer. But unless the DB is split into front-end/back-end, the non-table solution is no good, either.

Not only that, but if the form in question is in a shared DB, changing the contents of the non-tabular listbox requires you to allow your users to modify the design of the form. So then if they try to leave the form, they will be asked to save the modifications! Do you really want that? You will have one helluva time reconciling the divergent front-end DB segments if you have to update them in any way. Doing it via table is a lot simpler and does not require the right to modify a design. (You WERE considering the security aspect of this DB, were you not?)

And if it is NOT a split FE/BE case, users won't be ABLE to save in a shared environment unless they have Open Exclusive permissions AND they actually open the DB exclusively. So you will confuse your users and leave your DB in a shambling wreck very quickly in this case.

 

[Dave_cha]

Thanks Doc.

I should explain my situation a bit more.

The table with the source data contains a list of employees, each with an employee i.d. Each employee also has a 'Manager' field containing the id of the employee to whom they report.

When a given form is opened I want a listbox within to display all those employees reporting either directly or indirectly to a given employee.

With help I received from a previous thread, I managed to get the recursive lookups required working and built up list using a tmp table. i.e. A simple lookup for a given employee within the Managers field will give me a list of employees reporting directly into the given employee. These employees are then stored into the tmp table and in turn checked for employees reporting into them thus providing employees reporting indirectly into the original given employee.

While this code works, it is too slow to use given the number of table writes. I'm sure there must be a way to build up the list of direct & indirect reportees using an array and then populate a listbox based on the contents of the array.

I've added the existing code below to explain better. If you can figure out how to rebuild arrays I would be VERY grateful.

Thanks, Dave

Private Sub List0_Click()

DoCmd.Hourglass True
DoCmd.SetWarnings False

'Clears temp table for new run

DoCmd.RunSQL "DELETE * FROM [TempTable]"

'Checks the 'Reporting Lines' table to find those reporting directly into the selected employee.
'Inserts the id's of these employees into the temp table

DoCmd.RunSQL "INSERT INTO [TempTable] ( EMPFF ) SELECT [Reporting line].EMPLOYEE FROM [Reporting line] WHERE [Manager]= [Forms]![Reporting Lines]![List0];"

'Requery Listbox 2 which displays FF's contained in the temp table.

Me.List2.Requery

'Declares the first and last row in Listbox 2 and a counter to track progress through the rows.

Dim TMPFIRST, TMPLAST, TMPCOUNTER As Integer
TMPFIRST = 0
TMPLAST = Me.List2.ListCount - 1

'Loops through the temp table checking each id for direct reportees.
'Each run adds any new reportees found to the temp table.
'New additions to the temp table are picked up on the next loop and checked for direct reportees.

Do

Me.List2.Requery

For TMPCOUNTER = TMPFIRST To TMPLAST
DoCmd.RunSQL "INSERT INTO [TEMPTABLE] ( EMPFF ) SELECT [Reporting line].EMPLOYEE FROM [Reporting line] WHERE [Manager] = '" & Me.List2.Column(0, TMPCOUNTER) & "';"
Next TMPCOUNTER

TMPFIRST = TMPLAST + 1
TMPLAST = Me.List2.ListCount - 1
'MsgBox TMPFIRST & " - " & TMPLAST
Loop Until TMPFIRST >= TMPLAST

DoCmd.SetWarnings True
DoCmd.Hourglass False

End Sub

 

[The_Doc_Man]

Your problem is a data design error of a very specific and obscure type, not at all uncommon.

When you designed your database, you set up a situation in which you don't store information you needed to use for an important function. So since you don't store it, you have to compute it on the fly.

Here is the problem restated: Pay me now or pay me later, but if you pay me later, you'll pay me EVERY TIME!

Build a linking table that provides reporting structure such as you are trying to represent. I.e. Joe Blow directly reports to Hank Snow and indirectly reports to Dr. No. Make a ReportsTo table, have it contain JoeBlow and Hank Snow as one entry, Joe Blow and Dr. No as another entry. You could mark the latter entry as "indirect" with a Yes/No flag.

When reporting structure changes, adjust the entries but then leave them as static entries. When you need to know who reports to whom, it is in the static table and you can run a straight query, complete with direct or indirect reporting as a separate flag.

If the number of changes is small with respect to the number of times you have to ask the question, the overhead of defining this table and adjusting it only when org-chart changes occur, then you pay a small price up front. As you yourself pointed out, the table-build overhead being paid at every query is too expensive. But a straight-out static query ought to be faster than greased lightning, comparatively speaking.

Now, it sounds to me like you also have a problem that would be solved if you found threads in this forum on the topic "Cascading Combo Boxes" because it seems to me that you really want something like that topic describes. I.e. make a selection in one box that affects selections to be shown in a second box.