Solved Slow cursor movement from parent form to child form (1 Viewer)

[nector]

Hi

In my project I reduced the number of subforms to 1 to improve the speed this works very well in Ms Access Backend , but not so good with the SQL server, the cursor take almost a minute to move from the parent form to the sub form. The combos though having over 3500 record are working reasonable well after linking them to views plus the help of the code below:

Private Sub Product_Combo_Change()
Dim strText As String

strText = Nz(Me.Products_Combo.Text, “”)

If Len(strText) > 2 Then
Me.Product_Combo.RowSource = “Select keywords from ” _
& “ProductName ” _
& “where keywords like ‘” & strText & “*’ ” _
& “order by keywords”
Me.Product_Combo.Dropdown
End If
End Sub

How did you do it to speed up the cursor movement between parent form and child form?

 

[Minty]

That sounds very unusual, how are the two forms linked and what is the rowsource for the sub form?

 

[nector]

Here is the row source , the parent table is called tblCustomer and Child table is called tblLinedetails

SELECT tblCustomerInvoice.InvoiceID
FROM tblCustomerInvoice;

 

[Minty]

And what links the two?
If you are only showing the invoice ID I would have thought a List box that you updated on change of customer would be more efficient than a subform?

 

[nector]

The combo box is connected to view direct from Ms Sql server , here I do not have any speed issue, the problem is the cursor to move from the parent form to the child below is believe looking at what is happening here:

Since we are using WIFI not Ethernet connection could this the cause of the problems, sorry I'm now thinking too much here because of the issues below:

WIFI connection Vs Ethernet connection

A Wi-Fi connection is more vulnerable to interference from electrical devices or physical objects that can block the signal. An Ethernet connection is consequently more reliable, as it is insulated from interference and crosstalk and unaffected by the presence of physical objects.

Example:

Here’s the skinny. If you’re outside mowing the lawn and you want to stream music, Wi-Fi is your only option of the two. If you’re participating in an online gaming tournament where every button presses counts, Ethernet is your optimal connection.

That’s the short answer.

Overall, wireless gives you mobility. You can freely roam while you stream music or watch Netflix on your tablet while snuggled up in bed. The drawback is range and interference play havoc on your connection. You will see dramatic slowdowns or disconnects altogether as you move away from the router.

Meanwhile, Ethernet gives you reliability. The wires are ugly and keep you tethered to a location. But Ethernet speeds don’t fluctuate like Wi-Fi—you can get the same speeds at 300 feet as you do at ground zero. The drawback is you lose the tether-free mobility of Wi-Fi.

 

[Minty]

A cursor in SQL is very different to moving the cursor in an access form, which is what your original post seemed to be talking about.
It has nothing to do with the speed of operation of your forms (or shouldn't do). I'm intrigued by what makes you think it does.

I'll ask the question again - In your sub form what links it to the main form:

What are these set to?

 

[nector]

Okay here it is

 

[Minty]

Okay, that looks a bit strange.
Why would you have a combo box on the subform for the InvoiceID, surely it's a fixed value based on the main form?
If you are populating that with every invoice id that makes no sense, and almost certainly will be contributing to your forms slowness

 

[nector]

No this invoiceID is populated automatically when the parent form is completed and when a form open its hidden

 

[Minty]

No this invoiceID is populated automatically when the parent form is completed and when a form open its hidden

So it doesn't need to be a combo box then. Just make it a bound control.
That will stop it from having to load any underlying data.

 

[nector]

Okay Thanks

 

[Pat Hartman]

No this invoiceID is populated automatically when the parent form is completed and when a form open its hidden

Minty was being nice. I'll be more direct. Change the combo to a textbox and hide it. Every row of the subform would have the same value anyway so you are wasting resources by using a combo when you don't need it. This may not be the problem, but it does contribute to it.

Access works differently when the BE is Jet/ACE from when it is SQL Server. You are having the typical results of a straight conversion of an app that uses Jet/ACE techniques to SQL Server. Access is very tightly integrated with Jet/ACE and so lets you get away with lots of poor practices.

One thing you will need to do is to change the way your main forms work. Do NOT bind them to tables or queries without criteria. Bind them to queries that have criteria that severely limits the rows and columns to be returned. You want to minimize the traffic over your LAN, ESPECIALLY when it is Wifi. Why have the server send you thousands or millions of records when the user is only going to use one at a time?

For some of my forms, I have complicated search forms where the user picks what he wants to search on but for most, It is just a customerID or name. To manage this, the query that the form is bound to has a WHERE clause
Where SomeID = Forms!mymainform!SomeID

The form opens with the search control empty so no record is retrieved. The user enters the search criteria and either the AfterUpdate or a button click run a requery.

Me.Requery

to bring up the requested record. Bringing back each record, one at a time, when the user is ready to work with it is far more efficient than bringing an entire table or the results of a join into memory on your local PC and then filtering it locally.

 

[nector]

For some of my forms, I have complicated search forms where the user picks what he wants to search on but for most, It is just a customerID or name. To manage this, the query that the form is bound to has a WHERE clause
Where SomeID = Forms!mymainform!SomeID

Below is my combo box which populate all my required fields when select a product name, then how do I filter the combo box so that the subform does not load the entire data until I scan the barcode or type in the possible option,

Example

I type in a product name "FANTA ORANGE 300 RGB" the combo bring up only data related to this product or if I scan the barcode "1005632780754" for this product in the said combobox only details relating to this product come up. This is where I'm lost , probably I'm missing something!

SELECT DISTINCTROW tblProducts.ProductID, tblProducts.ProductName, tblProducts.BarCode, tblSalesTax.TaxClass, tblPricing.Prices, tblPricing.RRP, tblSalesTax.VatRate, tblSalesTax.Tourism, tblSalesTax.Insurance, tblSalesTax.TourismLevy, tblPricing.TaxInclusive, tblSalesTax.InsuranceRate, tblSalesTax.InsuranceRate AS Premium, IIf(([TaxClass]="D"),Round(IIf(([TaxClass]="D"),[Prices],([Prices]/1.16)),2),Round(IIf(([TaxClass]="C3"),[Prices],([Prices]/1.16)),2)) AS ExportPrice, tblPricing.NoTaxes, tblProducts.Sales
FROM tblSalesTax INNER JOIN (tblProducts INNER JOIN (tblcartergory INNER JOIN tblPricing ON tblcartergory.CartID = tblPricing.CartID) ON tblProducts.ProductID = tblPricing.ProductID) ON tblSalesTax.IDClass = tblPricing.IDClass
WHERE (((tblProducts.Sales)=Yes))
ORDER BY tblProducts.ProductID DESC;

So in this case how do the where clause come into picture, that is where my confusion is

Highly appreciated you have a valid point its just I do not understand whether the search box is supposed bound or unbound

 

[Minty]

Rather than loading a combo box with 100000's of product lines immediately, you could use a search box that only loads the combo after a certain number of characters are entered. Or possibly have a filtered listbox that displayed the results of a partial search.

This would limit the returned no of records being dragged in then simply pick the one you want.
If the barcode is unique to the product have a separate barcode search box that simply returns the data for the single matched record.

 

[nector]

Okay let me make the point very clear , the subform is filtered see this code below

SELECT tblProducts.ProductID, tblProducts.ProductName, tblProducts.BarCode
FROM tblProducts
WHERE (((tblProducts.BarCode)=[Forms]![tblCustomerInvoice]![txtscan]));

Now requirements

Since the subform is forced to load with one record only see the picture and a sample database , all I need is the moment this txtscan control is updated , the subform control called Product name must be updated automatically since there is just one record already filtered.

Yes there is need for VBA code to do that ,this is where the mix up is

 

[Minty]

I can't see that working, as the combo box on the subform won't show any data the next time you scan something on that invoice, assuming you have more than one detail line.

 

[nector]

No no the scan is the one filters the combo just enter for example 114 or 113 you see that the data in subform is changing

 

[nector]

For example if I was in the subform it will be me.productid = me.parent!txtscan.value
But now I want to do the update from the parent form

 

[Minty]

See the attached for a possible method - I would probably get rid of product combo on the sub form if you really have 100,000's of items.

 

[nector]

Thank so much this will help alot minty