Dlookup Works in Controlsource - but not in VBA

[crimedog]

in the control Source I have:

=DLookUp("[RevBonus]","[Tbl2_MgrBonus]","[BonusMo]=" & [TxtMo1] & "And [BonusYr]=" & [TxtYear] & "And [MgrID]=" & [TxtMgrID])

And this works.

When I have tried to translate it to VBA in On Load
Me.RevAttain1.ControlSource=



I get errors - I have tried what I know - closest I can come gives me a Name? error - or a VBA error

Can someone translate it for me?

[pbaldy]

If you're trying to put the result of the DLookup() there, use the Value property, not ControlSource.

[crimedog]

When I do that I get You entered an expression that has no value

[crimedog]

Me.RevAttain1.Value = DLookup("RevBonus", "Tbl2_MgrBonus", "BonusMo" = [TxtMo1] And "BonusYr" = [TxtYear] And "MgrID" = [TxtMgrID])

one verson - getting the no value error

[pbaldy]

Note the differences in how you did it:

DLookUp("[RevBonus]","[Tbl2_MgrBonus]","[BonusMo]=" & [TxtMo1] & "And [BonusYr]=" & [TxtYear] & "And [MgrID]=" & [TxtMgrID])
DLookup("RevBonus", "Tbl2_MgrBonus", "BonusMo" = [TxtMo1] And "BonusYr" = [TxtYear] And "MgrID" = [TxtMgrID])

[crimedog]

yeah, I tried it both ways - same error

[pbaldy]

The first one is your working one from the control source. The second is completely messed up. Try the syntax of the first. You may need to use the names of textboxes if different.

[ssanfu]

Also, I would try adding a space in front of each of the and's"

DLookUp("[RevBonus]","[Tbl2_MgrBonus]","[BonusMo]=" & [TxtMo1] & " And [BonusYr]=" & [TxtYear] & " And [MgrID]=" & [TxtMgrID])

And if [TxtMo1] is a control on a form, I would use the Me keyword:
DLookUp("[RevBonus]","[Tbl2_MgrBonus]","[BonusMo]=" & Me.[TxtMo1] & " And [BonusYr]=" & Me.[TxtYear] & " And [MgrID]=" & Me.[TxtMgrID])

[crimedog]

it is not giving a syntax error - it is saying "you entered an expression that has no value"
(I reverted to the original syntax)

[crimedog]

Me.RevAttain1.Value = DLookup("[RevBonus]", "Tbl2_MgrBonus", "[BonusMo] =" & Me.[TxtMo1] & " And [BonusYr] =" & Me.[TxtYear] & " And [MgrID] =" & Me.[TxtMgrID])

Returning the No value error (It does return a value when it is in the control source)

[pbaldy]

Can you attach the db here?

[ssanfu]

Try with one criteria.

Me.RevAttain1.Value = DLookup("[RevBonus]", "Tbl2_MgrBonus", "[BonusMo] =" & Me.[TxtMo1])

If a value is returned, add another criteria:

Me.RevAttain1.Value = DLookup("[RevBonus]", "Tbl2_MgrBonus", "[BonusMo]  =" & Me.[TxtMo1] & " And [BonusYr] =" & Me.[TxtYear])

Then add the third criteria. Trying to see if one of the criteria is causing the problem........

[crimedog]

Got it - I was getting the NO Value Error because I was pointing to Text Boxes in the report that was loading - so in effect My On Load Action is happening before the Text Boxes are filled.
I pointed it instead to Text boxes on a form that is open in the background - works like a charm


Thanks Guy!!

[pbaldy]

No problem.