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, 2 1755
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" <PCES...@PCESoft.invalidwrote:
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,
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" <qu**********@technologist.comwrote in message
news:11**********************@q3g2000prf.googlegro ups.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" <PCES...@PCESoft.invalidwrote:
>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,
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Frances |
last post by:
Hi All,
* PREMISE *
I'm creating an Access form with 150 items subdivided into 20
categories. Multiple categories (and items) can be selected so my
user wants checkboxes. All of the options...
|
by: Rob |
last post by:
Hello,
I am developing an A2K application.In the subform ,I have around 4
combo boxes for Group,Division,Name and Application.The selection
criteria of each combo is narrowed based on the value of...
|
by: Galina |
last post by:
Hello
I have 6 dependent list boxes on my ASP page:
Faculty;
Lecturer;
Course;
Course occurrence;
Group;
Week commencing date.
When faculty is selected, lists of lecturers and...
|
by: ebasshead |
last post by:
Hi everyone,
I'm having trouble getting my head around how to make a text box appear from a combo box selection. The combo box lists methods of payment eg cash, visa, mastercard. If visa or...
|
by: Dave |
last post by:
I want to put the information that the user selects in my combo boxes
into a subform that lies on the same form as the combo boxes.
Thanks for your help already,
Dave
| |
by: Dave |
last post by:
I have 3 tables of information feeding into 4 combo boxes on my main
form (DR Form). I have as many list boxes (acting as text boxes) as
there are fields in each one of the 3 tables. Once...
|
by: Alo2aaa |
last post by:
Dear all
i have two linked combo boxes (master/child) ,the user wants to have multiple selection from both combo boxes , can u tell me how can i store them in a way that i can have a unique id...
|
by: evenlater |
last post by:
Using Access 2007, I've found that combo box back colors change to
transparent from normal inconsistently for no reason I can discern.
Never had that problem in previous versions of Access. I do...
|
by: Orbie |
last post by:
Hey Guys,
I'm new to VB.NET and looking for some help with combo boxes. I was wondering if it is possible to link 2 combo boxes to each other based on user selection?
Example:
Table 1 - Dept...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |