DlookUp in a Subform

[Guest]

I have tried numerous times and ways to do a dlookup within a subform. It
just doesn't work. I'm getting an error "you cancelled the previous
operation". All I want to do is show a staff's 'Section' then I select the
name from a combo box in the subform. Here is my syntax

Dim varX As Variant
Dim strCriteria As String

strCriteria = Me.cboAssignee

varX = DLookup("[strSection]", "tblEmployees", "[strAlias]=" & strCriteria)

Me.txtSection.Value = varX

I placed the code in the 'cboAssignee' after update event. I give up.

It works fine when I open the subform as a stand-alone using dlookup
formula. Please help.

 

[Steve Schapel]

Allan,

I am not sure why the code example is so involuted. It would be easier
like this (one line)...

Me.txtSection = DLookup("[strSection]", "tblEmployees", "[strAlias]="
& Me.cboAssignee)

I don't know what is causing the error. I can see no reason why this
sort of code should produce different results, depending on whether the
form is a subfrom or not. But here's a couple questions for
clarification...
Do you really have a field in the tblEmployees table called strAlias?
Is strAlias really a Number data type?
Is txtSection an unbound control? If not, it should be. If so, why not
just forget the code approach altogether, and set its Control Source to...
=DLookup("[strSection]","tblEmployees","[strAlias]=" & [cboAssignee])

 

[Guest]

Thanks, Steve. I tried your code and it produced the same error. I came
accross with MVP Marsh posted answer with same issue and gotcha I found the
solutions. She mentioned to add closing quotes on the conditions.

Me.txtSection = DLookup("[strSection]", "tblEmployees", "[strAlias]='" &
Me.txtAssignee & "'")

Thanks again.
--
Allan

Allan,

I am not sure why the code example is so involuted. It would be easier
like this (one line)...

Me.txtSection = DLookup("[strSection]", "tblEmployees", "[strAlias]="
& Me.cboAssignee)

I don't know what is causing the error. I can see no reason why this
sort of code should produce different results, depending on whether the
form is a subfrom or not. But here's a couple questions for
clarification...
Do you really have a field in the tblEmployees table called strAlias?
Is strAlias really a Number data type?
Is txtSection an unbound control? If not, it should be. If so, why not
just forget the code approach altogether, and set its Control Source to...
=DLookup("[strSection]","tblEmployees","[strAlias]=" & [cboAssignee])

--
Steve Schapel, Microsoft Access MVP

I have tried numerous times and ways to do a dlookup within a subform. It
just doesn't work. I'm getting an error "you cancelled the previous
operation". All I want to do is show a staff's 'Section' then I select the
name from a combo box in the subform. Here is my syntax

Dim varX As Variant
Dim strCriteria As String

strCriteria = Me.cboAssignee

varX = DLookup("[strSection]", "tblEmployees", "[strAlias]=" & strCriteria)

Me.txtSection.Value = varX

I placed the code in the 'cboAssignee' after update event. I give up.

It works fine when I open the subform as a stand-alone using dlookup
formula. Please help.

 

[Van T. Dinh]

The question is how you got it to work without the quotes if the Form is
opened on its own???

That's what you stated in your original post ...

 

[Steve Schapel]

Agreed. Not only that, but we also have a switch now from cboAssignee
to txtAssignee. Very confusing.

Anyway, Allan, for future reference, the additional "s are required
because strAlias, and the value of the combobox, are text, not numerical.