Question about linked Excel table

[bgephart]

I have a database that uses a linked Excel spreadsheet as one of its tables, "Books". Since I have it linked and "read-only", that seems to limit the way I can use it in forms.



I have created a form based on another table, "Checkouts", that is related to "Books" (Books:review = Checkouts:Book ID). So far, I just have fields from the Checkouts table in my form. (They're actually from a query based solely on the Checkout table.) I found that if I linked in "Books" in any way, I was unable to update anything on the form.

Still, I would like to include information from a couple of the fields in Books on my form, and have tried using DLookup in the ControlSource for a text box as follows:

 =DLookUp("[title]","[Books]","[review]= " & [Forms]![Check In Form]![Book ID])

I have gone over this several times looking for errors in punctuation and capitalization, etc., and have tried several variations of syntax, but continue to get "#Error" in the field. Is there something about the nature of the Excel-linked table that's causing a problem? Or is there something wrong with the syntax that I'm not catching?

In case it matters, I'm building this with Access 2007, but in a 2003 compatible format.

Thanks for your help.

[pbaldy]

This would be more efficient if applicable to your situation:

BaldyWeb - Autofill

If not, perhaps your syntax is wrong for the data type?

DLookup Usage Samples