Help with DLookUp getting #Type!

Question

I have a table of regions and their codes, some of the regions are sub regions of parent regions. So the table is:

ID (primary key auto number long integer)

Abr (text, field size 16)

Description (text, field size 255)

Parent (long integer number, many to 1 link to ID)

I have a form where I'm showing the Regions and the Parent field. Currently the parent field just shows the ID that it linked to and I would like it to me more helpful.

So I figured out I need DLookUp.

=DLookUp("[Abr]","Regions","[ID] = " & [Forms]![Parent])

From my understanding that says get the value in Abr from the Regions table when the ID of that record is equal to the value of Parent on the current record.

But when I do this I get "#Type!" showing up in my textbox.

Any suggestions?

Answer 1

You are including a reference to the Forms collection in the expression but not giving a name for the form.  But as in this case the form is that in which the control is situated, you don't need to reference the form at all, simply the control. 

=DLookUp("[Abr]","Regions","[ID] = " & [Parent])

However, on the basis of your description I don't think this is what you want to do as you refer to the form's recordset  having a Parent column, which would mean the form is bound to the Regions table.  If my understanding is correct what you want is a bound combo box rather than an unbound text box, set up like this:

ControlSource:    Parent

RowSource:     SELECT ID, Abr FROM Regions ORDER BY Abr;

BoundColumn:   1

ColumnCount:   2

ColumnWidths:  0cm

If your units of measurement are imperial rather than metric Access will automatically convert the last one.  The important thing is that the dimension is zero to hide the first column.  The value of the combo box, and hence of the parent column to which it is bound, will be the numeric Parent value, but by hiding the ID column returned as the first column by the control's RowSource query, the value shown in the control will be the text Abr value corresponding to the ID value which matches the Parent value in the column in the current record.

Where you could use an unbound control would be in a form bound to a referencing table which has a foreign key column. RegionID say, referencing the ID column in the regions table, e.g. a table of locations within a region.  This table would not have a Parent column of course as parent is determined by Region.  However, the expression to call the Dlookup function would be like this:

=DLookUp("[Abr]","Regions","[ID] = " & DLookup(["Parent]","Regions", "[ID] = " & [RegionID]))

i.e. it looks up the Abr value from the row in Regions where the ID value equals the value of Parent in that row in regions with the ID value matching the foreign key RegionID selected in the current record in the form bound to the referencing table.

BTW I would advise against using parent as a column name as it is the name of a built in property in Acces, so should be avoided as an object name.  ParentRegion would be better.

Answer 2

Thank you Ken. I'm not quite clear on the RowSource you've provided here. Are the R1 and R2 shorthand for something or do the reference the AS R1 and AS R2 bits of the expression?

I think that is actually going to help me with my other problem of linking up data that is more than one table away....

In any event the current control I have works perfectly well. I just want to have it not look like a combobox. On this form the user isn't going to be able to change the parent region, so I just want to show the info.

Control source: ParentRegion

Row Source: SELECT ID, Abr FROM Regions ORDER BY Abr;

Bound column: 1

Column count: 2

ColumWidths: 0, 1

That works great and fast, but it only works in a combo box not a textbox. The version I have working in a text box also works fine, but is a little slow.

=IIf(IsNull([ParentRegion]),"",DLookUp("[Abr]","Regions","[ID] = " & DLookUp("[ParentRegion]","Regions","[ID] = " & [ID])))

So at the moment it comes down to which bugs me more, slow or combo that I don't need? If there was a way to combine the speed of the combo and the appearance of the textbox I would be set.

I'm going to take a look at the select you've given me here to see if could help with my other problem.

Answer 3

Domain functions are notoriously slow, and this is exacerbated by calling one from within another of course.  An alternative is to use a combo box for the RegionID column in the form and join two instances of the table in its RowSource query, so the combo box would be set up as follows:

Name:    cboRegion

ControlSource:    RegionID

RowSource:    SELECT R1.ID, R2.Region, R1.Region FROM Regions AS R1 LEFT JOIN Regions AS R2 ON R1.ParentRegion = R2.ID ORDER BY R1.Region;

BoundColumn:    1

ColumnCount:     3

ColumnWidths:    0cm;0cm;8cm

As the ControlSource property of the text box to show the parent region use an expression which references the hidden second column of the combo box:

=cboRegion.Column(1)

The Column property is zero-based, so Column(1) is the second column.

Answer 4

One interesting thing I'm noticing is that the SELECT in a combo box is instantaneous, the moment I get to a record its value for the ParentRegion is shown in the combo box immediately. The DLookup is fast, but it takes a fraction of a second to fill in.

Any idea how to get the SELECT speed, but on just a text box?

Answer 5

Thank you. That makes some sense to me...probably more as I learn more.

The one thing is this isn't actually the entry form for the Regions, it is just a heading above a subform and I don't want it to appear as a combo box. It is just to be informational. I have locked it so the user can't be playing with it here.

I have changed the name to ParentRegion. Thank you for that advice.

Your second expression does what I want -- almost! So I used the DLookup you gave as a second example and it mostly works:

=DLookUp("[Abr]","Regions","[ID] = " & DLookup("[ParentRegion]","Regions", "[ID] = " & [ID]))

But it showed an error if the ParentRegion was empty. So I added a conditional:

=IIf(IsNull([ParentRegion]),"",DLookUp("[Abr]","Regions","[ID] = " & DLookUp("[ParentRegion]","Regions","[ID] = " & [ID])))

Now it works!