DLookup returning #Error

[apk19]

Hi people,

I've been trying to simply populate a textbox based on the information in a combobox but keep returning #Error.




The Numb combobox is labelled: [CbxRMA] and is the PK in a table "tblRMAs" with only two columns: a number "MARN" and the corresponding name "RMAName", which is the text I want displayed in the "Agent" textbox. The combobox value is stored in the form's underlying table not using a Lookup.

The Control Source for the textbox is: =DLookUp("RMAName","tblRMAs","[MARN] = '" & [CbxRMA] & "'"). I have tried variations for [CbxRMA] with no luck.

Any help is always appreciated.

[ItsMe]

What is the RowSource of the Combo? In the property sheet, what is the value of the Column Count and Bound Column properties?

[apk19]

Hi ItsMe,

RowSource of the Combo is: SELECT tblRMAs.MARN, tblRMAs.RMAName FROM tblRMAs;
Column Count: 2 (to display corresponding values)
Bound Column: 1

[ItsMe]

It sounds like your expression should consider the second column in your multicolumn combobox control. So something like this ...
= DLookup("RMAName", "tblRMAs", "[MARN] = '" & Me.CbxRMA.Column(1) & "'")

Having said that, why bother adding an unbound control that takes a second trip to the data to display something the combo can display? In other words, the value of RMAName is already in the combo's second column.

You can adjust what is visible in a combo by adjusting the column widths property.

The following will hide the first column and display the second.
Column Widths: 0, 2.5

[apk19]

Hi ItsMe,

Thanks for the reply.

I never really thought of that. Pretty stupid to not. I've been wanting a separate field for the sake of using it for templates, but I guess it can be derived straight from the table anyway.

[ItsMe]

No worries, I know it can help to get a fresh set of eyes on things. You definitely want to be familiar with some of the properties of the combo. I have some screencast tutorials that you are welcome download. They go over the combo's properties and can be found here ...
https://www.accessforums.net/tutoria...ers-52741.html

[apk19]

I will definitely check it out.

Thanks again.

[Missinglinq]

...a number "MARN"...

=DLookUp("RMAName","tblRMAs","[MARN] = '" & [CbxRMA] & "'")

ItsME is correct, of course, about not making two trips to the data, when one trip will do, But just so that you know...your problem was that MARN is a Number, and your Where Clause

"[MARN] = '" & [CbxRMA] & "'"

is only the correct syntax for a Text Field!

For a Number Field it would have to be

"[MARN] = " & [CbxRMA]

Linq ;0)>

[apk19]

Thanks for the input. Managed to sort this one out. Full correct syntax is:

=DLookUp("[RMAName]","tblRMAs","[MARN] = " & Nz([Forms]![Applicants]![CbxRMA],0))