Full Version: DLookup not working
UtterAccess Forums > Microsoft® Access > Access Forms
I have the following as the control source for an unbound text box:
IIf(IsNull([PermitID])," ",DLookUp("[Lot]","tblLots","[LotID] = " & DLookUp("[LotID]","tblPermits","[PermitID] = " & [Forms].[frmParkingInfo].[sfrmPermits].[Form]![PermitID])))
For some reason, on a new record, i get the #Error. I thought using the IsNull to tell the control to display nothing would work(I think it has in the past on other projects). So maybe it is and the #Error is coming from somewhere else but I have no clue where. I'm sure the DLookups are right...I'll keep trying to fix it but if anyone has any ideas as well..awesome!!
instead of isnull() try
f(len([PermitID] & "") > 0," ",DLookUp("[Lot]","tblLots","[LotID] = " & DLookUp("[LotID]","tblPermits","[PermitID] = " & [Forms].[frmParkingInfo].[sfrmPermits].[Form]![PermitID])))
does it work on non new records?
Yes, the DLookup works for records that have a PermitID. Will give the 0 length a try...
OK, I used:
IIf(Len([PermitID] & "")>0,DLookUp("[Lot]","tblLots","[LotID] = " & DLookUp("[LotID]","tblPermits","[PermitID] = " & [Forms]![frmParkingInfo].[sfrmPermits].[Form]![PermitID])),"")
and I still get the #Error for the new record...I'm about to use the len in conditional formatting, just make the font white...
Oh..one of my favorite culprits...the default value for the ID's in the tables were at the default 0's. I am revamping an old db and in my rush to get this done, forgot to delete the 0's when I threw the tables together quick...everything works great now. Thanks Danny!!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.