Help!!! DLookup returning #Error (1 Viewer)

[NoviceBoy]

Hi All,

I'm new here, and found the site when looking for some help on a DLookup problem. I'm just about to pull the rest of my hair out.

I've tried just about everything - even copying and pasting the code directly from the Microsoft site.

My form has a Combo box, which links to a table not bound to my form. I have 3 other fields which I need the value for, based on the Combo Box selection. When I enter the following into AfterUpdate, I get the error #Error returned.

=DLookUp("AcqCosts","CSL_Agreed_Costs","AgreedCostType=" & [AgreedCostType])

I have also tried using this in the Control Source, but it has the same effect.

Is the above code correct? It's driving me nuts, and the bean counter has given me until Monday to get it fixed.

Can anybody help?

Many thanks

 

[NoviceBoy]

Hang on a minute, I've just noticed a space in AgreedCost Type, which doesn't show when I paste this into the Control Source!!

Weird - is this a bug?

 

[boblarson]

Hang on a minute, I've just noticed a space in AgreedCost Type, which doesn't show when I paste this into the Control Source!!

Weird - is this a bug?

The forum frequently does that when a certain number of characters is reached. To make sure it doesn't do that, use CODE TAGS.

 

[boblarson]

Oh, and is AgreedCostType a number or text?

 

[NoviceBoy]

Thanks Bob,

AgreedCostType is text and set as text in the table. AcqCosts is a Currency.

 

[boblarson]

Then if it is text you need quotes:

=DLookUp("AcqCosts","CSL_Agreed_Costs","AgreedCostType=" [COLOR=red][B]& Chr(34)[/B][/COLOR] &  [AgreedCostType] [B][COLOR=red]& Chr(34)[/COLOR][/B])

 

[NoviceBoy]

Hi Bob,

I've tried this with spaces and without, and it gives the following message: -

The expression you entered has an invaild string
A string can be up to 2048 characters long, including opening and closing quotation marks.

Apologies if I'm being thick here.

 

[Access9001]

try "AgreedCostType like " instead of "AgreedCostType="

 

[NoviceBoy]

Hi Access9001, this makes the field go blank.

 

[boblarson]

try "AgreedCostType like " instead of "AgreedCostType="

Nope, it needs the = sign in there.

 

[boblarson]

Oh, I just noticed. What is the name of your combo box? That needs to be in there like this:

=DLookUp("AcqCosts","CSL_Agreed_Costs","AgreedCostType=" [COLOR=black]& Chr(34)[/COLOR] &  [B][COLOR=blue]Me.ComboBoxNameHere[/COLOR][/B] [COLOR=black]& Chr(34)[/COLOR])

 

[NoviceBoy]

Wow,

When I put the Me. in without the square brackets, the field goes crazy, like it is refreshing itself over and over. When I include the square brackets, it returns #Error

 

[NoviceBoy]

Number of hairs on head left = 35.

 

[boblarson]

Wow,

When I put the Me. in without the square brackets, the field goes crazy, like it is refreshing itself over and over. When I include the square brackets, it returns #Error

Not sure why you would be having it updating over and over again (is this on a single view form, a continous, or datasheet?

You don't have square brackets around the control name in VBA.

 

[NoviceBoy]

This is in the Control Source in the Properties. I have seen a number of examples of this on various sites, and couldn't understand why the square brackets were put in even though the field name had no spaces in the name.

 

[boblarson]

Ah, yes you would need it in the Control source and you can't use ME there. So, if it doesn't work with just the control name in brackets, make sure that the field and the control don't have the same names. So, rename the control something like cboAgreedCostType if the name of the field is AgreedCostType and then use the

[cboAgreedCostType]

like that in place of the

& Me.ComboBoxNameHere &

 

[NoviceBoy]

Hi Guys,

Can I post a cut down version of the database, it must be difficult trying to visualise this.

 

[NoviceBoy]

Another thought is could I use columns for this?

 

[boblarson]

Yes, post the database (with bogus data of course). See here for how.

 

[NoviceBoy]

Thanks Bob,

Will take a little while to strip it out, but will have it over to you asap.