Return values from a linked excel table

[graccess]

I've established a linked table from excel consisting of dates and other numerical values. These values could be updated throughout the day by formulas linked to third party API feeds so I wanted to have something dynamic. Where I'm stumped is retrieving a field value in a record that matches a record searched in my form. For example, I look up companies that already exists in my database, values are then returned from the company table but a couple of fields that I wish to populate have data residing in the linked excel table. I know I can't relate tables that have external links so I'm searching for a solution to incorporate this data into a form or report. What is the procedure for returning the appropriate value?

[June7]

Linked Excel sheet can be treated like a table (except cannot edit the data). Include the link in query that joins on the PK/FK fields. Alternative might be DLookup. Need to know more about data structure to better advise.

[graccess]

Linked Excel sheet can be treated like a table (except cannot edit the data). Include the link in query that joins on the PK/FK fields. Alternative might be DLookup. Need to know more about data structure to better advise.

I think the Dlookup might the way for me to go. How do I give the linked table a primary key, not letting alter the table. If I create a new table out of the linked table and add a primary key, wouldn't I then lose my dynamic linked date?

[June7]

The linked sheet must have some field that serves as foreign key. This would link to some table with corresponding value which is the primary key. Example:

A table of Customers, each record is unique with a CustID as primary key, and has info about customers.

A link to Excel which has transactions associated with customers, each record has CustID as foreign key. Join the two datasets in query on the common CustID fields.

I still don't know your data structure.

[graccess]

I have 4 tables. tblcompany, tblnotes, tblevents, and the newly excel linked table testbook1. I have a query that sorts the information for my form by a ticker combo box. that query is the recordsource of my form frmCompany. One of the text boxes I'd like to see populated in this form is a field from the testbook1. If I'm understanding correctly, I should have included in my excel worksheet a field such as CompanyID that I should be using as my foreign key when I first link the info to Access?

[graccess]

I created a table which is correctly returning the data I want with the following query:
SELECT tblCompany.Ticker, testbook1.CalcV INTO tbllinkdata
FROM tblCompany INNER JOIN testbook1 ON tblCompany.Ticker = testbook1.Ticker;

Then in my main form, I wrote a dlookup expression in the control source:
=DLookup("CalcV","testbook1","Ticker =" & [cboTicker])

Receiving #Errors in the text field.
I'm sure I'm missing something fairly simple but I'm spinning my wheels a bit. Thanks again for your help.

[June7]

What is the error?

What is the value of Ticker - number or text type?

Why save data to a table? Just save a query that joins the two datasets.

Shouldn't the DLookup refer to the new table or query instead of testbook1?

[graccess]

The error is '#Error' in the text field, nothing else populates. The value of the Ticker is text. Actually, I don't need to save the data at all, so that could be a mistake I'm making, I'm fine retrieving the data each time I view any ticker, the corresponding value stored in excel should populate. I think I effectively saved a query joining these two datasets as you mentioned, but I'm having some trouble retrieving it when I call a ticker. You are correct, the DLookup is calling the new table, [tbllinkdata] - I incorrectly posted an earlier failed expression, the rest of the expression is where I stand currently.

[graccess]

Jumping a little ahead, but I've also noticed that when I update the values in the linked excel file, the testbook1 will update but I will need to find a way to make the new table update as well without having to manually run the make-table query again. what is the procedure to automate this?

[June7]

That's why I suggest you not make table. Just use a SELECT query that joins the two datasets.

[graccess]

That makes a lot more sense. I've created a new select query and it runs the info I want to see. Should the dlookup get added to the afterupdate event code of my ticker combo box and if so, how would you write that code? Or should the control source of my textbox reference this select query, in which case I need to add some criteria from the combobox I presume.

[June7]

Treat the Excel link just as you would any table (except for data edit).

Treat that query just as you would any other query.

They can be used as RecordSource for form or report or joined with other tables/queries.

Or use DLookup that references the query. Do that in textbox or in query or in code. Depends what you want to do with the retrieved data.

[graccess]

Treat the Excel link just as you would any table (except for data edit).

Treat that query just as you would any other query.

They can be used as RecordSource for form or report or joined with other tables/queries.

Or use DLookup that references the query. Do that in textbox or in query or in code. Depends what you want to do with the retrieved data.

i decided to go the route of having a dlookup in my sourcecode referencing the query. The query is working as I refresh its value after changing the combobox in my form. But the form itself is not reflecting the value. Instead the text box reads '#Name?'.
=DLookUp([CalcV],[testbook1 Query],[Ticker]=[cboTicker])

CalcV is the field in my query that has the value I want to grab
testbook1 Query is the query i'm referencing.
Ticker is the a field I'm checking to see if it's equal to my selection in the combo box.

[June7]

Need quote marks and apostrophe delimiters and concatenation. Access Help has more info on using domain aggregate functions.

=DLookUp("[CalcV]","[testbook1 Query]","[Ticker]='" & [cboTicker] & "'")

[graccess]

Argh, thanks for the help on the syntax as well!

Works perfectly.