DLookup - going mad!

[vavroom]

I'm having real trouble making this DLookup work properly. I suspect
I'm not getting the right combination of single quotes and double
quotes and ampersands...

I have one unbound textbox and two unbound combo boxes on a form. On
the After Update event of the second combo box, I want to lookup the
value of one field in a table, based on the values of both combo boxes.

In the After Update event, I have:

Me.txtEmail = DLookup(fldEmail, tblSendTo, "[fldCollege] = " & _
Forms![TESTEXPORT]![cmbCollege]& " AND [fldLocation] = " & _
Forms![TESTEXPORT]![cmbLocation])

Both combo boxes are keeping a numeric value.

I've tried many different ways to write this, to no avail

Could some kind soul extricate this with me please?

Thanks

 

[vavroom]

Hmm, I have no idea what's going on, but it now works!

And I haven't changed a thing!!!

Thanks for looking anyway.

 

[Steve Schapel]

Vavroom,

Hmmm, I doubt very much that it would work without ""s around the first
2 arguments of the DLookup function, in other words...
DLookup("fldEmail","tblSendTo","[fldCollege] = " & _ ...

 

[vavroom]

Hmmm, I doubt very much that it would work without ""s around the first
2 arguments of the DLookup function, in other words...
DLookup("fldEmail","tblSendTo","[fldCollege] = " & _ ...

Not sure why, but it does work!

 

[Steve Schapel]

Vavroon,

This is interesting, and I would like to learn what I can here.

Can you clarify for me please...
tblSendTo is the name of a table or query, right?
fldEmail is the name of a field in tblSendTo, right?
These are not variables that you have defined within a VBA procedure,
right?
Can you say what version of Access you are using?

--
Steve Schapel, Microsoft Access MVP

Hmmm, I doubt very much that it would work without ""s around the first
2 arguments of the DLookup function, in other words...
DLookup("fldEmail","tblSendTo","[fldCollege] = " & _ ...

Not sure why, but it does work!

 

[vavroom]

Sorry for the late response Steve, life distracted me from the computer

tblSendTo is the name of a table or query, right?

Correct, it is the name of a table.

fldEmail is the name of a field in tblSendTo, right?

That is also correct.

These are not variables that you have defined within a VBA procedure,
right?

No, not variables at all. The DLookup statement is written in VBA
under an AfterUpdate event of a combo box

Can you say what version of Access you are using?

Access 2002 SP3 (10.6771.6804)

Cheers

 

[Steve Schapel]

Thank you, Vavroom. I am asking around...

 

[Steve Schapel]

Vavroom,

I have thought about this some more, and also discussed with some
colleagues.

Your initial question was about the Where condition argument of your
DLookup, of course. And maybe if it is all working sweet for you now,
you are happy. But to me there is a concern that you will get incorrect
results. There is something unexpected about the situation. If the
DLookup simply refers to a field named fldEmail in a table named
tblSendTo, it should not work without these being enclosed in ""s. It
should produce an error. Unless you do not have a line at the top of
your form module saying Option Explicit, in which case the error
possibly would not be triggered. Otherwise, if you right-click on the
fldEmail or tblSendTo in your code, and then select 'Definition' from
the popup menu, what does it do?

 

[vavroom]

If the
DLookup simply refers to a field named fldEmail in a table named
tblSendTo, it should not work without these being enclosed in ""s.

Ah ha!

But they are... I now see that I somehow didn't put in here the same
code I have in the DB.

I am *very* sorry to have caused you confusion and extra work