CODEWITHSUNDEEP
ms-access
Adrian Slater
Adrian Slater

Reputation: 36

Slow Dlookup call in MS Access

I have an MS Access front end/back end database. In the back end database I have a table keyed on FundCode. The table has 4 fields and 150 records. In the front end I have a form used to update a different table in the back end. When the user enters the FundCode on this form, I want to display the data in the three columns on the form - so the use can check they have chosen the correct fund code. To display this I am using a textbox (not enabled) to display with a control being a Dlookup into my fund table e.g.

=DLookUp("ISIN","tbl_FundMap","FundCode = " & [FundCode])

The code does what it supposed to do, but when I have around 100 entries in the form the it takes around 10 sec to refresh the screen every time a user rolls up or down. Any suggestions how I can achieve a quicker result?

Upvotes: 0

Views: 323

Answers (1)

Zack
Zack

Reputation: 2341

Rather than using text boxes and the DLOOKUP function, I would reccomend using combo boxes.

The first combo box is where the user selects the actual FundCode value. The 2nd combo box, which is locked and/or disabled, also has its control source set to FundCode, but its row source includes the ISIN as the 2nd column. That way, the 2nd combo box always shows the ISIN based on the current FundCode selected.

Design View

Form View

Upvotes: 1

Related Questions