requery not calling User-defined RowSourceType function

[Michael Suttkus, II]

I have a form that features a combo box that is filled by a user-defined
RowSourceType function.

Another combobox on the form selects one of four default filters (no filter,
and three filters to restrict the records viewed to one of our three
campuses.)

When the form first opens, the default "no filter" is selected and the UD
RST combobox works correctly.

When you subsequently select any of the campus-specific filters, the UD RST
combobox again operates as expected, filling up with just the inventory items
found on the specific campuses. It continues to operate correctly if you
switch from one campus filter to another.

However, when you switch back to "no filter", the UD RST combobox's Requery
method is called as expected, but Access makes the remaining requests of the
UD RST function in the wrong order. Specifically, it calls the acLBGetValue
& acLBGetFormat for the previous ID session, THEN closes the previous session
(acLBEnd, acLBClose, acLBEnd), and then starts the next session
(acLBInitialize, the mysterious, undocumented 2, acLBOpen), but never
requests acLBGetValue or acLBGetFormat for the new session. The result is an
empty combobox!

I have a patch for this problem. Calling Requery a second time produces a
new session with the calls to the UD RST function taking place in the correct
order (though it never closes the previous ID session, so if I was opening
resources, this patch would result in a memory leak). But calling requery
twice is not a satisfactory solution.

Does anyone have an idea what's going on here?

 

['69 Camaro]

Hi, Michael.

I have a form that features a combo box that is filled by a user-defined
RowSourceType function.

Don't use this method to fill a combo box or list box. Instead, store the
data in tables and use a query to retrieve the data. Assign this query to
the combo box's RowSource Property. Whenever combo box's data source needs
to be changed, assign a new SQL statement to the combo box's RowSource
Property and requery the combo box. For example, if a selection of one combo
or list box (lstSites in the following example) dictates what records will be
displayed in another combo or list box (lstNutrients in this example), then
this can easily be accomplished with the following code:

Private Sub lstSites_AfterUpdate()

On Error GoTo ErrHandler

Me!lstNutrients.RowSource = "SELECT NID, Nutrient " & _
"FROM tblNutrients " & _
"WHERE (SID = " & Me!lstSites.Column(0) & ");"
Me!lstNutrients.Requery

Exit Sub

ErrHandler:

MsgBox "Error in lstSites_AfterUpdate( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub

.. . . where NID is the first column in the lstNutrients list box, Nutrient
is the second column, and SID is the first column (and the bound column) in
the lstSites list box, as well as the foreign key in the tblNutrients table.

Does anyone have an idea what's going on here?

It's buggy. You'd never notice how buggy it was if you used it for its
intended purpose, which is to display a single column and only a few rows in
a list box or combo box -- where this data set is static (never changes)
while the form is open. If you step through the debugger while your function
is filling your combo box, you'll find that the function is being called
about six times to fill only one value per column, per row. If you have four
columns and three or four hundred rows, you'll see that it's slow as molasses
while filling the combo box -- and while scrolling, too, because this
function gets called again about six times per value in each column and row
that's being displayed in the combo box's drop down area, and you'll have to
wait for the data to be calculated, assigned, and then the screen updated.

And if you have multiple combo boxes depending upon the selected value in
another combo box, then you'll find that you have to come up with all kinds
of work-arounds to get this to work correctly for all situations that the
combo box is likely to be used for. You're much better off not using the
RowSourceType function to fill the combo box or list box dynamically at run
time, but using the RowSource Property instead. Using a query for the
RowSource Property is reliable and far speedier.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Michael Suttkus, II]

A fine solution if the combo box is to be filled purely with data deriving
from a table. However, I'm planning to add functional choices to the combo
box as well (for example, to add a new record). I'd either have to include
dummy data in the source table (unacceptable for many reasons) or include
code to modify the results of a temporary make-table query, which seems an
over-wrought solution.

I could also add a sub to AddItem the combo box until it's complete and call
it instead of Requery, but that seems artificial and prone to error. (If I
come back in six months and add code that needs to repole the combo box, I'll
use Requery by habit.)

The UD RST function works (or should!) with the existing Requery method and
results in more centralized programming.

If it works.

Thank you for your answer, but I'm still in the market for a solution using
the current technique or a good explanation of why UD RST functions should
emphatically not be used.

 

['69 Camaro]

The UD RST function works (or should!) with the existing Requery method

and
results in more centralized programming.

If it works.

It kinda does. Just don't expect the functionality to be very flexible or
expandible -- or fast, for that matter.

I'm still in the market for a solution using
the current technique

If you aren't willing to consider other alternatives to solving the problem,
then I hereby bequeath unto you half the dents in my skull that I received
from banging my head against the wall while yelling, "WHY WON'T THIS
WORK??!! It's from Microsoft's own documentation!" when I was an Access
developer.

or a good explanation of why UD RST functions should
emphatically not be used.

1.) Because dents in the skull hurt (even if they look cool on Halloween).
2.) Because your boss may not understand why it still doesn't work
correctly, even after you've spent hours investigating and trying to solve
the problem.
3.) Because there are better ways to do this -- if you are sufficiently
stubborn or clever enough to find them.

May your stubbornness reward you, as mine surely has for me.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.