DlookUp in a Subform

J

Jologs

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.
 
S

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])
 
J

Jologs

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


Steve Schapel said:
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.
 
V

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 ...
 
S

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top