Dlookup too slow with external table, need to speed up or find alternate method

I'm looking up three pieces of information in an external database based on a job number input on a form.


Customer Name
Part Number
Part Name


I have a macro attached to the after update event of the job number field so when someone enters the job number it looks up the information in the external table. I have the external table joined in access so it shows up in the table list. I use the setvalue and dlookup functions to set the value in each of the three fields individually (2 dlookup commands). It takes about 30 seconds to update.

There must be a way to search for the job number once then return all three bits of data so I should at least be able to get this down into the 10 second range. That is still a little long though. Maybe I'm going about this completely wrong.


I put the found data into unbound fields on the form.


I tried creating a query based on the joined table. I filter out most of the records so that there are only about 100 records to search through but it didn't run any quicker.


I am an access newbie so I'm hoping I'm just going about this the wrong way. Using Acess 2010.



I'm not sure what is causing the delay. I normally use excel. Import into a table and only refresh as needed because the refresh is what takes the time. I was hoping that I could use a query to do something similar in Access. Don't refresh and run the query every time, only as needed.





Ken

Edit:


Here's one of the dlookup statements

Please Login or Register  to view this content.

Update:

To explain further...


I have a form based on the master fixture list. This is where fixtures get input or updated or whatever. Select fixture then click on sign out fixture button. That opens a form based on the Signouts table and creates a new record.


I created a query with both the Signouts table and the table that is linked to the external data. I joined the job number field in each. Include all from signouts table and only those from the thinked table that match. Then I based the form on the qurey. The proper information is pulled from the linked table (customer, part number, part name) when viewing the query but they don't show up in the fields that have the control sources listed as those fields. The text boxes just show #Name.

Also, I can't add records which make the macro that opens the form and selects a new record now work.


========================

Actually, now the fields do work. Not sure what was going on before. The text boxes on the form do pull the proper information. Still two problems though. 1. the first time I opened the form it took a minute and a half, probably to run the query. 2. I can't add or update records in the form.

Next I think I will try a subform based on the linked table.



========================

Subform actually seems to work. Almost. Takes about ten seconds to find data in subform. I could probably live with this if it was only triggered when actually inputting the job number but it takes 10 seconds just to advance to the next record.

I'm come to the conclusion that I may need to deal with 10 seconds to find the data. I think I will go back to the original concept of Dlookup but figure out how to get all data with a single lookup. Maybe a function that will return record number so then I can use that record number to quickly get all three pieces of data?