Subform refreshing? (1 Viewer)

[tehvilinaty]

I have a front end created in MS Access and a backend in SQL Server, the server is remote and the frontend connects to it over the internet. I have a combobox in a rather large form with multiple subforms in it.

The combobox contains names of customers, when someone types in it I wrote code to filter the names, that way it can show names even if part of it is typed. It works well but it's a bit slow. Trying to find out what is causing the delay I encountered this "issue":
The subforms have a master/child relationship with the main form, but if I remove the relationship, or delete the subforms completely, then the combobox filtering gets significantly faster. The combobox is bound to the "CustomerID" field of the form, so selecting a name on the combobox will store the CustomerID on the Order table, but the subforms use OrderID for their relationship and not CustomerID. Further, if I unbound the combobox, so it doesn't store anything anywhere, I still get delays.

This is my code for the on-change sub of the combobox (it's called CustomerCombo):

Private Sub CustomerCombo_Change()
Dim sSQL As String
sSQL = "SELECT Customers.ID, Customers.CustomerName, Customers.Phone, Customers.Email FROM Customers WHERE Customers.CustomerName LIKE '*" & CustomerCombo.Text & "*';"
CustomerCombo.RowSource = sSQL
CustomerCombo.Dropdown
End Sub

It's as if all my subforms are refreshing every time I press a key inside the combobox

 

[tehvilinaty]

To update my post and add something even more confusing. I converted the Customers table (Structure and Data) so it runs locally on my access file (not a linked table)

Modified the code:
sSQL = "SELECT Customers2.ID, Customers2.CustomerName, Customers2.Phone, Customers2.Email FROM Customers2;"
(Customers2 is the name of my local table)

and the problem remains... while I'm typing in the combobox I'm getting delays

Edit: To complete the confusion, I made the Job table (it's one of the subforms) a LOCAL table too and the delays are mostly gone.
Job subform and Order form are connected, but there is no relationship between customer and job (they are linked via the Order)

So why is the subform refreshing? I found this post:

Stop subform requery

Hi All, There is quite a lot of info around this out there, but I have been unale to solve my issue. I have a parent form with a child subform. On the parent form: I have a textbox which the after update property includes a vba query to update a list box. There is no .requery...

www.access-programmers.co.uk

someone had a similar problem of a subform refreshing without being ordered to but there was no resolution there

 

[zeroaccess]

I would recommend not beginning filtering until the user has typed 2-3 characters. Also, you might have a join type that is slowing things down. Can you show us this query in Design view?

 

[tehvilinaty]

Which query? It only applies on a single table, there are no joins.

And the form rowsource doesn't have a join either it's a simple Select * from Order.

Is it possible that every time I change the rowsource of a textbox, the entire form refreshes?

 

[zeroaccess]

You have mentioned multiple tables - so I thought a query was involved.

 

[tehvilinaty]

Oh that! The form takes data only from the table Orders, but it has subforms that get data from different tables. One of them is the Job table, which is connected to the Order table. The tables themselves are linked (there is an OrderID column in Job that connects Jobs with Orders, so an order can have multiple Jobs assigned to it.

If that join was the issue wouldn't it cause problems only when the form is refreshed? Which is my problem, I understand if I was refreshing my main form, the subforms would refresh too and since there are multiple joins, there would be a delay. But to my knowledge I'm not refreshing the main form as I only change the rowsource of a combobox and then use its dropdown method.

 

[Micron]

What do you mean "over the internet"? Surely no one is using WIFI for this? You would just be asking for coruption.
Any slowness you are describing is likely due to the way you're users are accessing your db. Some methods would be OK (e.g. Citrix or similar) but not much else.

 

[tehvilinaty]

No not wifi, just over a VDSL connection, the Access frontend is on one computer and connects to the SQL server which is at a different location

 

[Micron]

Then I guess you'd have to compare performance where the front and back ends are on the same pc. I'm not an expert on connections; I just know that you have to worry about error checking when sending packets over a connection, and some types do a poor job of maintaining data integrity and not dropping connections for brief moments.