Dlookup

[MelB]

I know that dlookup is not the most efficent function, but in what I am going, I think it will work best. I currently have a form that has about 60 fields that each contain numbers (picture an excel spreadsheet). There are hundreds of records for several different plant sites and so I set a filter before the form opens based on two global variables (gintplant and gintunit) I have two controls above about 1/2 of the columns in the report header, one for the minimum value and one for the maximum. The values come from a union query, qryMinMax. The code I have in the control source is as follows.

=DLookUp("[Min Value]","qryMinMax","[PlantID]=" & [Forms]![frmstandardU]![PlantNo] & " and [UnitID]=" & [Forms]![frmstandardU]![Unit] & " and [Field Name]='Load'")

Here is the problem... I can't get the code to work if I refer to the global variable, but it does work if I referance to another control on the form using [Forms]![frmstandardU]![PlantNo] . Now I have hardcoded the specific form name in the control so if I change the form name, the lookup doesn't work any more.

I am sure this is simple to fix but I'm really getting frustraded and could use some help... thanks in advance.

Any other suggestions would be appreciated, too...

 

[Oldsoftboss]

Are you referencing your variable correctly.

I just subsituted a bit of my code and it seemed to work

Subsituted:

Me!Part = DLookup("Description", "AssetNo", "[AssetNo] = " & [Number])

with:

Dim FormRef As Variant

FormRef = "[AssetNo] = " & [Number]

Me!Part = DLookup("Description", "AssetNo", FormRef)

HTH
Dave