access2007, sql2005
form bound to a linked table
combobox bound to a field (baanItem) with an afterUpdate event
the event has a 'stop' statement so I can step through and view
variable contents in the intermediate window

baanItem = ucase(baanitem)
if (nz(itemGroup,"")<>nz(baanItem.column(2),"") then
itemgroup = baanItem.column(2)
end if

stepping through the first line is fine
stepping through the 'if' line, shows an hourglass from a few minutes
once I get control back, with the 'itemgroup =' line highlighted, I
clicked in the 'intermediate window' and again got an hourglass that
lasted minutes

once I get to the window
?itemgroup shows the correct value

but
?baanitem.column(2) brings back the hourglass with a long delay,
but eventually shows the correct value

?baanitem.rowSource is
SELECT trim(t_item), t_dsca, trim(t_citg), trim(t_suno), t_prip,
t_csig FROM qryImcItem WHERE (t_item like 'P10*') ORDER BY t_item

where qryImcItem is a sql server view, and the statement returns
14,000 rows, which I assume is not 'too many'

I tried decompiling, compacting, copying to new mdb - same problem in
all cases

what could be causing this problem ?

Hi Roger,

Functions in a query can slow down the retrieval process.
What happens if you leave out the Trim functions in your query?


Imb.

On Sep 28, 1:22*am, imb <im... (AT) onsmail (DOT) nl> wrote:
I understand that functions slow down a query
but the third party ERP data is created using char() instead of
varchar(), thus the need for the functions

what I don't understand is why the hourglass while I'm stepping
through each line of vba ?
the rowsource is long ago set, and the data has been retrieved from
the sql server ?

On Sep 28, 1:22*am, imb <im... (AT) onsmail (DOT) nl> wrote:
it seems like if I step through the vba, there is no delay
but if I execute the whole function, there is
it's like ms-access needs to release cpu processing to allow data to
be received from sql server (ie doevents)

I'm using linked tables, and odbc datasources - so I'm not sure why
this is an issue

On Oct 1, 8:23*am, Roger <lesperan... (AT) natpro (DOT) com> wrote:
I've been slowly removing objects from the form until it would work
in it's simplest form, i've got a combobox called baanItem
column count = 6
column widths = 2",2.5",0,0,0,0


the following vba hangs on the second debug.print
baanItem.RowSource = "SELECT * FROM qryImcItem WHERE 1 = 2"
debug.print baanItem.RowSource
Stop
Debug.Print baanItem.Column(3)

qryImcItem is a sql server view,
and the number of rows returned is irrelevant
in this case, I return none

the column width settings allow me to display data other textboxes
using baanItem.column(x)

the first debug.print confirms that the rowsource is correctly set

shouldn't this technique of assigning .columns() as rowsource to
textboxes worked ?

On Oct 1, 2:21*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:
I would offer my sympathy to any user that had to use a form that
contained a selection of 14K rows in a combobox.

On Oct 5, 11:34*am, Patrick Finucane <patrickfinucan... (AT) gmail (DOT) com>
wrote:
I agree, but they want to enter a part# if they know it
and if not, they want to scroll down the list find it

I've added a filtering combo box, to select by product line, but that
still gives me 1000+ rows and performance still sucks

so then I created a local table, and when the product line is
selected, the afterUpdate loads this table with the 1000+ rows, and my
rowsource is based on this local table, and performance is quick

so it takes a couple of seconds to copy 1000 rows from sql server to
the local table with an INSERT
yet when the rowSource is based on the linked table, it takes many
minutes to scroll to the end of the list -
why the difference, in both cases, is it now transferring 1000 rows
once, from the sql server to the access mdb (one, into a table and the
other into a comboBox list) ?

inserting the local table causes bloat, but the architecture is such
that the MDB is reloaded everytime they open the app - so it's not an
issue


but if
I don't want to use a local table, and
I want to allow the user to enter the part # if they know it
I want to allow a lookup if they don't know, and I have no other
filtering options
I want quick performance retrieving sql server data

do I have other options ?

"Roger" <lesperancer (AT) natpro (DOT) com> wrote

For part number lookup, I use a unbound text box, where the user can type-in
the part number for the form to lookup. Other filtering options are vendor,
where used, manufacturer, manufacturer part number, description if indexed.
It they don't know the part number, scrolling a list box will not help. Look
at it from the uses point of view, how do they find part numbers? If your
part's descriptions are structured, try building an index table based on
keywords. Our system has 72000 part numbers and growing, by selecting 2
keywords, BEARING then SC it is filtered down to 7; which can easily be
scrolled.