Solved: DLookup with Null criteria

[Imdabaum]

Sorry for the double post.
I thought I had hit new topic, but it appears it was new reply.

I'm not exactly sure what I'm doing wrong here, but I'm running into this issue. I have a textbox with a control source as a DLookup. The recordset contains IDs from different tables, and I want the customer name to display in a textbox related to the customer id. Easy enough I thought, but this is what I have/get

'Attempt 1
textbox.ControlSource = DLookUp("CustomerName","TBL_CUSTOMERS","CustomerNumber = '" & Me.CustID & "'")

textbox = #Name? when CustID is null, I thought it was easy enough to handle, using Nz and setting the textbox to an empty string if there was no Customer id

'Attempt 2
textbox.controlSource = Nz(DLookUp("CustomerName","TBL_CUSTOMERS","CustomerNumber = '" _
& Me.CustID & "'"),"")
'I thought this should have given me the customer name or an empty string.

At this point I have tried, =IIF(IsNull(CustID)....) and =IIF(Not IsNull(CustID)... And still the textbox always turns up as #Name?

I tried to evaluate the code in vba and even when CustID is null, it still attempts to evaluate the DLookup instead of just giving me the appropriate value.

I realize that I can set the value of this textbox on Current event, or through VBA using simple If statement, but is there a solution where I can just set the controlsource in design to evaluate it?

[OBP]

Have you tried the Dlookup directly in the Field's Control source? i.e. without using VBA?

[Imdabaum]

Have you tried the Dlookup directly in the Field's Control source? i.e. without using VBA?

Yes.

I went into design mode and set the field's control source to

= DLookup("CustomerName", "TBL_CUSTOMERS", "CustomerNumber = '" & Me![CustID] & "'")

it was the first thing I tried. It barfed when Me!CustID was null and displayed #Name? when it wasn't. Currently I am using the exact same string on the SFRM.Current Sub and it's handling it okay. I just thought I could dispense with such tight control.

What is the difference between
= DLookup("CustomerName", "TBL_CUSTOMERS", "CustomerNumber = '" & Me![CustID] & "'")
AND
= DLookup("CustomerName", "TBL_CUSTOMERS", "CustomerNumber = '" & [txtCustID] & "'")?


Me![CustID] evaluates to the same thing txtCustID displays. But making that one change seems to have done the trick.

Thanks again for bearing with me.
"If at first you don't succeed... perhaps skydiving is not for you."

[Imdabaum]

Spoke too soon. But not solved too soon.

I have a drop down box that allows me to filter records by a certain customer.

Private Sub cboCustFilter_AfterUpdate()
'------------------------------------------------------------
' Filter the CRF records based on the drop down box.
'
'------------------------------------------------------------
Me.filter = "[CustID] = '" & Me.cboCustFilter.Column(0) & "'"
Me.FilterOn = True
'Without Me.refresh the DLookup fails again.
Me.Refresh
End Sub

Is this going to hamper performance drastically?

[OBP]

I don't see why, have you tried refreshing just the Field with the Dlookup in it, I am not sure if it works with fields, it certainly works with Combos.

[Imdabaum]

I don't see why, have you tried refreshing just the Field with the Dlookup in it, I am not sure if it works with fields, it certainly works with Combos.

No, textboxes don't have a refresh sub attached to them. It works for now.