Access SubReport Lookup Value from Table

0
votes

I've been breaking my brain all day with this, and I'm starting to really despise Access. At the root, I want to be able to use a textbox value in a Report to populate another Report textbox by looking up the value from a table. Here's a vanilla version:

  • Open Access 2016. Create Blank Database. Create Table called Table1 with 3 Fields, ID, Product and Spec. Populate with some data.
  • Create a Form or Report called Form1 with 2 textboxes. One textbox is Spec, the other is Product.
  • Try to use DLookup to lookup the Spec of the value in the Product text using some variant of the following:

=DLookup("[Spec]", "Table1", "[Product] =" & Forms![Form1]!Product)

It doesn't work, ever. I've tried so many variations of this in VBA and just using the control source as well, and none of them work. What am I missing here? Do I need to setup some kind of relationship between the table and the form?

I've followed examples from these Microsoft non-help sites exactly, and none of them actually work.

https://msdn.microsoft.com/en-us/library/office/aa172176(v=office.11).aspx

https://msdn.microsoft.com/VBA/Access-VBA/articles/application-dlookup-method-access

1
ms-accessvba

1 Answers

1
votes

Looks like Product field has a text data type, so your code should be like this:

=DLookUp("[Spec]","Table1","[Product] ='" & [Forms]![Form1]![Product] & "'")