strSubject and DLookup in email

[BarryMK]

An email is sent on close of my form. We have three shops (CCCs) and I’m trying to eliminate a line of hard code so the email selects the correct shop name depending on the value of field txtShopID.

The hard code line below works fine and the email is sent using:
strSubject = “New feedback from a customer at CCC No 1"

If I try this:
strSubject = “New feedback from a customer at" & DLookup("[Shop]", ["tblShops"], "[ShopID]= Me.txtShopID")

nothing happens, no error message just no email. I have a feeling it’s the link to the form field I’ve got wrong.

 

[missinglinq]

If [ShopID] is defined in your table as Text then

"[ShopID]= Me.txtShopID"

should be

"[ShopID]= '" & Me.txtShopID & "'"

 

[BarryMK]

Hi missinglinq
No - it's a number type in the table pulled through into a hidden text box on the form.

 

[Rabbie]

Have you tried

strSubject = “New feedback from a customer at" & DLookup("[Shop]", ["tblShops"], "[ShopID]= " & Me.txtShopID)

 

[BarryMK]

Have you tried

strSubject = “New feedback from a customer at" & DLookup("[Shop]", ["tblShops"], "[ShopID]= " & Me.txtShopID)

Hi rabbie

Afraid it reds out and in the dbug window I get expected end of statement

 

[Brianwarnock]

Should those [ ] be round the domain name?

Brian

 

[BarryMK]

Should those [ ] be round the domain name?

Brian

Hi Brian
Both these get the end of statement error
strSubject = “New feedback from a customer at" & DLookup("[Shop]", "tblShops", "[ShopID]= Me.txtShopID")

strSubject = “New feedback from a customer at" & DLookup("[Shop]", "tblShops", "[ShopID]= " & Me.txtShopID)

 

[missinglinq]

I assume that strSubject is just feeding a string to the subject line of the email, which makes the expected end of statement error rather strange. Maybe we need to see the code immediately around this statement. Access has a bad habit of not actually hi-liting the offending code, but rather a line later.

The only other thing I see is that

strSubject = “New feedback from a customer at" & DLookup("[Shop]", "tblShops", "[ShopID]= " & Me.txtShopID)

looks like it would return

“New feedback from a customer atCCC No 1"

since there's no space between the at and the quotation mark following it. But if it is, in fact, merely assigning a string to the email, I can't see this creating a problem.

 

[BarryMK]

No the space made no difference.

If I remove the part
“New feedback from a customer at ", the mail fires and places the text field "Shop" in the subject line as needed. I could probably live with this as the shop is identified but it would be nicer to completely idiot proof it with the feedback description.

 

[BarryMK]

Tut! Tut! Barry this is a duplicate post.

Brian

Brian I've deleted the rogue post!
The bloody post stuck first time around when I was uploading and did not appear when I went back to look for it!

 

[BarryMK]

Re: strSubject and DLookup in email - Solved!

I stopped chewing the ceiling and came up with a solution (workaround).

I created an extra field in tblShops called "ShopMail" and wrote in the full message for each shop record i.e. "New Customer Feedback from CCC No 1" and called that with the DLookup instead of the "Shop" field. Blindingly obvious in hindsight although not as elegant as doing it with code.

Thanks for all your efforts everyone.

 

[Brianwarnock]

I think its the "

This works
strSubject = “New feedback from a customer at CCC No 1"

This doesn't

strSubject = “New feedback from a customer at" & DLookup("[Shop]", "tblShops", "[ShopID]= " & Me.txtShopID)

which would not give
strSubject = “New feedback from a customer atCCC No 1"
But
strSubject = New feedback from a customer atCCC No 1

Atleast I think so, maybe some varation.

Brian

 

[BarryMK]

Solved


Here it is people

strSubject = "New feedback from a customer at " & DLookup("[ShopMail]", "tblShops", "[ShopID]= " & Me.txtShopID) compare


strSubject = "New feedback from a customer at " & DLookup("[Shop]", "tblShops", "[ShopID]= " & Me.txtShopID) Works
strSubject = "New feedback from a customer at" & DLookup("[Shop]", "tblShops", "[ShopID]= " & Me.txtShopID) Doesn't


Thanks for keeping me at it Brian

 

[Brianwarnock]

Re: Solved

Here it is people


Thanks for keeping me at it Brian

Knew it was the "


Well done Barry.

Brian

 

[BarryMK]

Cheers Brian, they do say the devil is in the detail......

 

[Rabbie]

Re: Solved

Here it is people

strSubject = "New feedback from a customer at " & DLookup("[ShopMail]", "tblShops", "[ShopID]= " & Me.txtShopID) compare


strSubject = "New feedback from a customer at " & DLookup("[Shop]", "tblShops", "[ShopID]= " & Me.txtShopID) Works
strSubject = "New feedback from a customer at" & DLookup("[Shop]", "tblShops", "[ShopID]= " & Me.txtShopID) Doesn't


Thanks for keeping me at it Brian

Glad you have got it working. It seems my change to the DLOOKUP worked after all

Apart from a a trailing space after the at is there any difference between the two values? My old eyes can't see any difference between them otherwise.

 

[BarryMK]

Bang on that appears to be it. Brian has a theory that the spacing prevented concatenation. Many hands eh Rabbie?