Selection lists (combo boxes) and large databases

I'm testing a user's db that contains a very large number of records. I have
an invoice screen, with an invoice select dropdown box that shows all
invoices, and the customer's name, etc. With 80,000+ invoices in this
particular db, clicking the dropdown is painfully slow accross a network
only (I'm testing on a wireless, so it's even slower still). All fields are
properly indexed, such as the invoice number (sort descending), etc. I've
noticed the Top Values property of the stored query, and this seems
basically useless. If you set it for top 5%, it does run the query much much
faster, however, I was hoping when you scrolled down to the end of the 5% of
the records, it would load the next 5%, but it doesn not appear to do this
(which is why I say it's basically useless). Am I missing something?

User's with smaller databases really find value in the invoice selection,
and want it to show the customer as well, so I hate to lose it.

Many Thanks,

Re: Selection lists (combo boxes) and large databases

You might want to think about adding a combo box. The first could be
for the customer. The next could be for the invoices. When a user
picks a customer, build a dynamic SQL statement to select invoices
only for the chosen customer. Using VBA code you can set this SQL
statement to the Rowsource property of the combo box. Your code
should look something like this...

Sub ComboBox1_AfterUpdate()

ComboBox2.RowSourceType = "Table/Query"
ComboBox2.Rowsource = "SELECT ID FROM tblInvoice WHERE
CUSTOMER_ID = " & ComboBox1 & ";"

End Sub

If it is still not fast enough for you, take it a step higher. Create
a query, qryCustomerInvoices, with the following SQL statement:

SELECT ID FROM tblInvoice WHERE CUSTOMER_ID = FORMS!frmMain!
ComboBox1

It is a known fact that prepared queries run faster than dynamic SQL
statements on list boxes, combo boxes, etc. So now, your AfterUpdate
code becomes this:

Sub ComboBox1_AfterUpdate()

ComboBox2.RowSourceType = "Table/Query"
ComboBox2.Rowsource = "qryCustomerInvoices"

End Sub

Let me know how this works for ya!



On Oct 22, 6:09 am, "ARC" wrote:
> I'm testing a user's db that contains a very large number of records. I have
> an invoice screen, with an invoice select dropdown box that shows all
> invoices, and the customer's name, etc. With 80,000+ invoices in this
> particular db, clicking the dropdown is painfully slow accross a network
> only (I'm testing on a wireless, so it's even slower still). All fields are
> properly indexed, such as the invoice number (sort descending), etc. I've
> noticed the Top Values property of the stored query, and this seems
> basically useless. If you set it for top 5%, it does run the query much much
> faster, however, I was hoping when you scrolled down to the end of the 5% of
> the records, it would load the next 5%, but it doesn not appear to do this
> (which is why I say it's basically useless). Am I missing something?
>
> User's with smaller databases really find value in the invoice selection,
> and want it to show the customer as well, so I hate to lose it.
>
> Many Thanks,

Re: Selection lists (combo boxes) and large databases

Many thanks. I do actually have a separate combo box with just the selected
customer's invoices. I'm thinking of having an option for large databases
where they can turn it on, and it will disable selecting by invoice # (or at
least disable the row source in the invoice only dropdown).

I did notice that the selected customer's dropdown operates much much
quicker. (However, I did have code in the customer's invoice history
combo_after update that set the main / all invoices dropdown, then called
it's after update. So that will have to be re-worked a bit.

Thanks again,


"Technolust" wrote in message
news:1193088481.646205.173330@q3g2000prf.googlegroups.com...
> You might want to think about adding a combo box. The first could be
> for the customer. The next could be for the invoices. When a user
> picks a customer, build a dynamic SQL statement to select invoices
> only for the chosen customer. Using VBA code you can set this SQL
> statement to the Rowsource property of the combo box. Your code
> should look something like this...
>
> Sub ComboBox1_AfterUpdate()
>
> ComboBox2.RowSourceType = "Table/Query"
> ComboBox2.Rowsource = "SELECT ID FROM tblInvoice WHERE
> CUSTOMER_ID = " & ComboBox1 & ";"
>
> End Sub
>
> If it is still not fast enough for you, take it a step higher. Create
> a query, qryCustomerInvoices, with the following SQL statement:
>
> SELECT ID FROM tblInvoice WHERE CUSTOMER_ID = FORMS!frmMain!
> ComboBox1
>
> It is a known fact that prepared queries run faster than dynamic SQL
> statements on list boxes, combo boxes, etc. So now, your AfterUpdate
> code becomes this:
>
> Sub ComboBox1_AfterUpdate()
>
> ComboBox2.RowSourceType = "Table/Query"
> ComboBox2.Rowsource = "qryCustomerInvoices"
>
> End Sub
>
> Let me know how this works for ya!
>
>
>
> On Oct 22, 6:09 am, "ARC" wrote:
>> I'm testing a user's db that contains a very large number of records. I
>> have
>> an invoice screen, with an invoice select dropdown box that shows all
>> invoices, and the customer's name, etc. With 80,000+ invoices in this
>> particular db, clicking the dropdown is painfully slow accross a network
>> only (I'm testing on a wireless, so it's even slower still). All fields
>> are
>> properly indexed, such as the invoice number (sort descending), etc. I've
>> noticed the Top Values property of the stored query, and this seems
>> basically useless. If you set it for top 5%, it does run the query much
>> much
>> faster, however, I was hoping when you scrolled down to the end of the 5%
>> of
>> the records, it would load the next 5%, but it doesn not appear to do
>> this
>> (which is why I say it's basically useless). Am I missing something?
>>
>> User's with smaller databases really find value in the invoice selection,
>> and want it to show the customer as well, so I hate to lose it.
>>
>> Many Thanks,
>
>