Text Box based on Combo Box is slow when opening existing records

[appelq]

I have a form [Containers] with a combo Box for [cboCustID]
The Row Source is:
SELECT dbo_tblCustomers.CustID, dbo_tblCustomers.CustName FROM dbo_tblCustomers ORDER BY dbo_tblCustomers.CustName;
There are < 100 Customers in the dbo_tblCustomers and when adding a new record in [Containers] the Combo Box performs fine.

A second Text Box field [txtCustName] returns the name of the customer selected in the combo box and has:
=[cboCustID].[column](1)

Side note the Database is split between a Front End and Back End

The noticeable problem is when a user does a search for a Container record.
The record is found fairly quickly, but it take another 10-12 seconds for the Text Boxes (there are 4 combo boxes on this form all setup similar to the above) to fill in.

Is there a better method for displaying the CustName in this case?

Side note the other Combo Boxes are also very small tables : Yards (3 records) ; Products (148 records) ; Vendor )3 records)

Thanks in advance.

Chuck

 

[lameid]

I have never had a problem with using combo boxes this way. However I would not use a linked SQL table in a query this way either ("dbo_" is a tell tale sign of linked SQL Server tables). You should use an SQL Pass Through query as your row source. Either pass the SQL code or use it to call a stored procedure. You are better off using the SQL Native Client driver which can be a bit of a deployment nightmare if this is multi-user.

When you let the Access database Engine do table scans against an RDBMS, it does some of the dumbest things imaginable which takes a while.