Full Version: Lookup Data from Linked Excel Spreadsheet
UtterAccess Forums > Microsoft® Access > Access Forms
roseweb2
I have a linked excel 2007 spreadsheet with customer data that I need to lookup within a form. I can't import the spreadsheet as it changes daily.The spreadsheet doesn't have any unique identifier, so I'm using a combination of two fields (Area Code and Pitch Number) for the lookup.
need a method of comparing the lookup fields on the form with those in the spreadsheet, to enable me to return the correct row of customer data. I guess DLookup would be too slow and I'm unclear on how to generate the code.
I've tried various methods, but most lock the tables from being updated.
Any assistance on this would be appreciated.
ScottGem
Have you tried Dlookup? Have you tried a Form Filter? Have you tried a query with criteria based on selection from a form?
roseweb2
Hi Scott, yes I tried a dlookup with the following ...=DLookUp("[name]","[tbl_customers_file_link]","[area code]"=[Forms]![frm_incidents_management]![area code] And "[pitch number]=[Forms]![frm_incidents_management]![pitch number]"). I've tried variants of that but they either return 'error' or pick the first record. I also tried a query, referencing the form fields. Haven't tried the form filter, as I'm not sure how to do it in this circumstance. Thanks for your reply.
ScottGem
Try it this way:
DLookUp("[name]","[tbl_customers_file_link]","[area code] = '" & [Forms]![frm_incidents_management]![area code] & "' And [pitch number]= " & [Forms]![frm_incidents_management]![pitch number])
When using control references or variables to supply criteria in an expression like that, you have to concatenate the values in. The above assumes that Area Code is a text value and Pitchnumber a numeric value. That's what the single quotes for Area code.
roseweb2
Cheers Scott, that workes nicely, except using 9 seperate dlookups for the customer details makes it very slow.
ScottGem
You could use a subform, you can use multiple links from main to subform.
roseweb2
I've tried changing the 'pitch number' part of the dlookup to be a text value, but my syntax is continually wrong. How should it look?
ScottGem
How are you doing it ti should be somethign like:
[PitchNumber] = '" & txtPitchNumber & "'"
roseweb2
Currently looks like this:
DLookUp("[Name]","[tbl_Owners]","[Area Code] = '" & [Forms]![frm_New_Incidents]![Area Code] & "' And [Pitch Number]= " & [Forms]![frm_New_Incidents]![Pitch Number])
ScottGem
"[Area Code] = '" & [Forms]![frm_New_Incidents]![Area Code] & "' And [Pitch Number]= " & [Forms]![frm_New_Incidents]![Pitch Number])
That looks correct if AreaCode is text and Pitch number is a nmber.
roseweb2
Thanks Scott. My problem is changing Pitch Number to a text value. I tried your example above and again it wasn't happy with the syntax.
ScottGem
What do you mean by changing to a text value?
roseweb2
I mean the 'pitch number' needs to be changed to evaluate for text as opposed a numeric. I discovered a few values that contained a letter (i.e. 3024a).
ScottGem
What datatype is the field?
roseweb2
text
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.