DLookup as textbox source

[Lhoj]

I have this particular field from a table I want to display in a textbox, so I'm trying to bring it up with a Dlookup as the control source. I know it should be doable and I managed to do it through VBA code but wanted to set it up in the control source and I can't find the proper syntax. The table is Laptops, field name is IMEI, criteria is SerialNum field must equal a value from a combobox from the same form, named Laptop.



so I tried:

=DLookup("[IMEI]","Laptops","[SerialNum] = '" & [Laptop] & "'")

Bad syntax, so I tried to play a bit with the different reference methods and the quotations and brackets, and tried:

=DLookup("[IMEI]","[Laptops]","[SerialNum] = '" & [Laptop] & "'")
=DLookup("[IMEI]","Laptops","[SerialNum] = """ & [Laptop] & """'")
=DLookup("[Laptops]![IMEI]","[Laptops]","[Laptops]![SerialNum] = '" & [Laptop] & "'")

and every possible combination without luck. What's the correct syntax for a Dlookup when used as a control source?

Thanks in advance!

[June7]

Your first effort looks fine to me.

However, there is a better way.

Include the IMEI field in the combobox RowSource SQL. Refer to that column by index in textbox expression. Combobox column index begins with 0. So if IMEI is in column 3 its index is 2.

=[Laptop].Column(2)

[Welshgasman]

Is laptop numeric?
If so, no single quotes required

You can always test the syntax in the immediate window as well.?

June7's method is much better anyway, but for the next time you do need a DLookup() .....

[Lhoj]

Nop, Laptop combobox contains the serial number of the laptop, which is text. Don't know why the first one (nor any other combination I've tried) works. Thanks for the tip about the combo box column reference June, that's actually what I was doing for another field, only in a total different way because I didn't know you could reference a column like that in the control source. Turns out if you copy the combobox, hide all but the column you want and turn it to a textbox it works too, but this is way cleaner. I had to add another table to the combobox's source query but it works perfectly.

It still bothers me not being able to get the DLookup syntax right, but I like this solution better, thank you both very much!

[pdanes]

When you do it in the textbox's rowsource, you must use semicolons instead of commas between parameters.

Queries also - the SQL uses commas, the graphic grid uses semicolons. Access will switch them back and forth as you switch between views, but you must use the correct ones when you are editing either view manually.

[Welshgasman]

When you do it in the textbox's rowsource, you must use semicolons instead of commas between parameters.

Wrong, this works fine for me?

=DLookUp("ID","Table1","ID=" & [ID])

[Minty]

I suspect it might be regionally "changed" depending where you are.

[Welshgasman]

I suspect it might be regionally "changed" depending where you are.

My thoughts as well Minty, as I have seen plenty of examples from members on various sites.

[pdanes]

Wrong, this works fine for me?

=DLookUp("ID","Table1","ID=" & [ID])

Wrong.

This works:

=Switch(Nz([txtPocetRozdil])='';'';Val(Nz([txtSumKusy]))=Val(Nz([txtKusyvKatalogu]));"Vše určené";Val(Nz([txtSumKusy]))<Val(Nz([txtKusyvKatalogu]));"Zbývá";Val(Nz([txtSumKusy]))>Val(Nz([txtKusyvKatalogu]));"Chyba")

This throws an error, and will not even get saved in the source property box:

=Switch(Nz([txtPocetRozdil])='','',Val(Nz([txtSumKusy]))=Val(Nz([txtKusyvKatalogu])),"Vše určené",Val(Nz([txtSumKusy]))<Val(Nz([txtKusyvKatalogu])),"Zbývá",Val(Nz([txtSumKusy]))>Val(Nz([txtKusyvKatalogu])),"Chyba")

[isladogs]

As already stated, the separator needed depends on your regional settings. In many countries such as the UK its a comma.
If I entered the semicolon version that works for you, I would get an error.

[June7]

In the U.S., also use comma.

[Welshgasman]

So where does the O/P reside?

@lhoj?

[Lhoj]

Sorry, guys, really wished I had more time to invest in this project, I'm actually enjoying it, but hadn't touched it since I last posted.

It's semicolons here in Spanish version of Access. I use so much VBA and so little access functions I had totally forgotten about that. So thanks you for the tip, pdanes. Once that was fixed it basically works with almost any syntax (with or without brackets for the fields, for example). With DBúsq (spanish version of DLookup) this worked fine:

DBúsq("IMEI";"Laptops";"SerialNum = '" & [Laptop] & "'")

Anyway, I'm gonna stick to combobox column reference approach, but I'm glad we got this sorted out. Thank you all!

[pdanes]

Glad it helped.


One additional note - you need brackets in such expressions when your field or table name contains 'bad' characters. Unfortunately, 'bad' means various things at various times. I can't give you a comprehensive list, but spaces are always bad, the standard 26 Latin characters are always good. I think underscores are also always good (except maybe as the first character, sometimes - I'm not 100% sure about that). For you, you may run into issues if you have Spanish characters, like ñ in your names. I work in Prague, and I've had problems with the special characters the Czech language uses. Sometimes it works, sometimes not, sometimes it works only with brackets. I avoid the issue by not using such characters anywhere in my own designs, but I do run across it occasionally when dealing with other people's work, so keep an eye out for that.