DlookUp in Subform

[tis9700]

I've been messing with this on and off for days.

I have a DLookUp expression in the Default Value of a textbox control on a subform. For the life of me I can figure out what is wrong. I believe I have the Forms referenced properly because another variation of this expression works in a different Master/Detail environment.

The error I get in the textbox is....#Error

The DLookup is...
=DLookUp("User","DB_USERS","RacfID = '" & [Forms]![STUDENT_DATA_TEMP]![STUDENT_DATA_ENTRY].[Form]![RacfID] & "'")

Is there any reason, perhaps a setting in one of my forms to explain this behavior?


Thanks,
Tracy

 

[Remou]

You need to check the spelling of every thing. A good way is to break it down:

[tt]=DLookUp("User","DB_USERS","RacfID = 'ABC'")[/tt]

[tt]=DLookUp("User","DB_USERS","RacfID = 123")

=[Forms]![STUDENT_DATA_TEMP]![STUDENT_DATA_ENTRY].[Form]![RacfID][/tt]

 

[tis9700]

Hi Remou,

Thanks for the response.
All spelling appears to be correct.
Tracy

 

[Remou]

Did each of the suggested tests (above) work?

 

[tis9700]

The first test worked when I substituted an actual value.

=DLookUp("User","DB_USERS","RacfID = 'BH01701'")

I understand the criteria is like a WHERE clause.

Tracy

 

[Remou]

That is correct.

From the above, it seems that we need to sort out this:

=[Forms]![STUDENT_DATA_TEMP]![STUDENT_DATA_ENTRY].[Form]![RacfID]

You seem to imply that the DlookUp control is on the subform, if that is the case, try with just the control, that is:
[RacfID]

If not, try the expression builder to get the reference, perhaps there is some very small difference in a name that is difficult to spot.

 

[tis9700]

I tried it with just the control

=Dlookup("User","DB_USERS","RacfID")

It works when I view it in Subform only but when I open the Main form and subform together, I get #Error in the textbox.

I'm trying the Expression builder next.

 

[Remou]

I did not explain that very well, I meant:

=Dlookup("User","DB_USERS","RacfID = '" & [RacfID] & "'")

 

[tis9700]

Yeah,
I thought I had that wrong. When I did it the wrong way, I believe it was presenting the value directly from the table?

I did that test again, properly this time with the same result.

 

[Remou]

If this is being done on the subform:

=Dlookup("User","DB_USERS","RacfID = '" & [RacfID] & "'")

Is correct. Please double check that RacfID is the name of a field in the table bound to your subform.

 

[tis9700]

All of a sudden it's working! I typed it again and it works in subform alone and Both forms. Why is that? Aren't you supposed to reference both the main form and the subform in the criteria?

Thanks for the help!
Tracy

 

[Remou]

You only need to reference the full name if you are working on the main form, it is, as it were, a different country.