Use DLookup to get data from multiple tables

[medady]

Hello,



I have a big table "Table1" and a form linked to that table "Form1". "Table1" has 250 fields!!! I need to add about 20 fields more, as you know... access tables cannot include more than 255 fields. I've created a small table "Table2" and added those fields into it. I used the same primary key used in "Table1" so that linking tables is easier. I created a query "Query1" that has the primary key from "Table1" and the new fields from "Table2". Please don't get me wrong, the primary key in both tables is identical, same number of rows and same values in that field.

I added the following code to Sub Form_Load for testing it.

Dim varX As Variant
varX = DLookup("[NewField1]", "Query1", "[PrimaryKey] = " & Form_form1.PrimaryKey)
If varX Is Not Null Then MsgBox varX

Every time I open "Form1" I get the following error message

Run-time error '2471':
The expression you entered as a query parameter produced this error: 'Record1'

"Record1" is the value of the PrimayKey of the record I'm trying to test out.

When I debug I get the DLookup line highlighted, i.e. this line
varX = DLookup("[NewField1]", "Query1", "[PrimaryKey] = " & Form_form1.PrimaryKey)

Any ideas how to get dlookup working? Again, I'm just trying to get new fields added to "Form1", "Form1" is already linked to the big table "Table1" which doesn't have enough space to include the new required fields, that's why I'm using "Table2". Thank you!

[JoeM]

I have a big table "Table1" and a form linked to that table "Form1". "Table1" has 250 fields!!! I need to add about 20 fields more

I would question why you have so many fields. Seldom is there ever a need for that many fields in a normalized database.
Perhaps restructuring your table into a more normalized format will allow you to do what you need without having to create this "spillover" table.

Can you explain why there are so many fields in the table (and what they are)? We may be able to offer you suggestions on how to restructure it.

[jzwp11]

Having a large number of fields in a table usually indicates that your table is not normalized. For more on normalization, check out this site. Could you provide a sampling of some of the field names in your table?

Sorry for the duplication JoeM

[JoeM]

Sorry for the duplication JoeM

No problem! It just further reinforces the concept!

[medady]

SOLVED! PrimaryKey is a text field, I should've noticed that . It just needed a quotation. Thank you for passing by!

The code should be like this

Dim varX As Variant
varX = DLookup("[NewField1]", "Query1", "[PrimaryKey] = '" & Form_form1.PrimaryKey & "'")
If varX Is Not Null Then MsgBox varX