Trying to get AfterUpdate event to work

[Slez via AccessMonster.com]

I'm trying to add records to a subform and store a value in a particular
field, just like in the Northwind database Order Details form. The purpose
is to store the value in my field UnitCost from the Product table into the
UnitPrice field of the ItemDetail table when the ProductDescription is
selected in my subform. To accomplish this, I added an AfterUpdate event to
the ProductDescription control. I pasted the following code from Northwind
and adapted to my database' field and table names:

Private Sub ProductDescription_AfterUpdate()
On Error GoTo Err_ProductDescription_AfterUpdate
Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductDescription = " & Me!ProductDescription

' Look up product's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("UnitCost", "Product", strFilter)

Exit_ProductDescription_AfterUpdate:
Exit Sub
Err_ProductDescription_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductDescription_AfterUpdate
End Sub

However, I am getting the following message when a new record is added to
the subform:

Syntax error (missing operator) in query expression 'ProductDescription =
Base.3 drawer'

Where Northwind has a ProductID, I have ProductDescription, which is a text
field. Perhaps that has something to do with the error. In the error
message, "Base.3 drawer" happens to be the product I picked to try this out.
UnitPrice is the field in the table ItemDetail to store the value.
UnitCost is the value I'm pulling in from the table Product.

As mentioned, is the fact that ProductID in Northwind is a Long Integer and
my ProductDescription is a text field causing the problem? If so, how do I
correct my expression?
I would greatly appreciate any assistance!
Slez

 

[Douglas J. Steele]

As you guessed, the problem is because you're using a Text field, not a
Numeric one.

If ProductDescription will never contain an apostrophe, use:

strFilter = "ProductDescription = '" & Me!ProductDescription & "'"

Exagerated for clarity, that's

strFilter = "ProductDescription = ' " & Me!ProductDescription & " ' "

If the description might contain an apostrophe, but will never contain a
double quote, use:

strFilter = "ProductDescription = " & Chr$(34) & Me!ProductDescription &
Chr$(34)

or

strFilter = "ProductDescription = """ & Me!ProductDescription & """"

(that's 3 double quotes before Me!ProductDescription, and 4 afterwards).

If you might have both apostrophes and double quotes in the description,
check my May, 2004 "Access Answers" column in Pinnacle Publication's "Smart
Access". You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Slez via AccessMonster.com]

Thanks for your reply! I checked out your Access Answers per the link you
provided and I appear to be in "way over my head" so I'll just ask you this
and hopefully you can clarify. Here is one of the example of text in my
ProductDescription field:

Base.Open cubby (5) 7'x18" dp

Am I asking for trouble? Should I eliminate quotes or double quotes? Are
the parentheses going to throw a wrench into this?
Thanks again!
Slez

As you guessed, the problem is because you're using a Text field, not a
Numeric one.

If ProductDescription will never contain an apostrophe, use:

strFilter = "ProductDescription = '" & Me!ProductDescription & "'"

Exagerated for clarity, that's

strFilter = "ProductDescription = ' " & Me!ProductDescription & " ' "

If the description might contain an apostrophe, but will never contain a
double quote, use:

strFilter = "ProductDescription = " & Chr$(34) & Me!ProductDescription &
Chr$(34)

or

strFilter = "ProductDescription = """ & Me!ProductDescription & """"

(that's 3 double quotes before Me!ProductDescription, and 4 afterwards).

If you might have both apostrophes and double quotes in the description,
check my May, 2004 "Access Answers" column in Pinnacle Publication's "Smart
Access". You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

I'm trying to add records to a subform and store a value in a particular
field, just like in the Northwind database Order Details form. The

[quoted text clipped - 45 lines]

I would greatly appreciate any assistance!
Slez

 

[Douglas J. Steele]

There's nothing intrinsically wrong with using single or double quotes in
the text. It just makes the processing a little more complicated (but not
unreliable, or anything like that).

Download the sample I indicated, and copy mdlText from it into your
application.

You'd then use:

strFilter = "ProductDescription = " & CorrectText(Me!ProductDescription)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for your reply! I checked out your Access Answers per the link you
provided and I appear to be in "way over my head" so I'll just ask you
this
and hopefully you can clarify. Here is one of the example of text in my
ProductDescription field:

Base.Open cubby (5) 7'x18" dp

Am I asking for trouble? Should I eliminate quotes or double quotes? Are
the parentheses going to throw a wrench into this?
Thanks again!
Slez

As you guessed, the problem is because you're using a Text field, not a
Numeric one.

If ProductDescription will never contain an apostrophe, use:

strFilter = "ProductDescription = '" & Me!ProductDescription & "'"

Exagerated for clarity, that's

strFilter = "ProductDescription = ' " & Me!ProductDescription & " ' "

If the description might contain an apostrophe, but will never contain a
double quote, use:

strFilter = "ProductDescription = " & Chr$(34) & Me!ProductDescription &
Chr$(34)

or

strFilter = "ProductDescription = """ & Me!ProductDescription & """"

(that's 3 double quotes before Me!ProductDescription, and 4 afterwards).

If you might have both apostrophes and double quotes in the description,
check my May, 2004 "Access Answers" column in Pinnacle Publication's
"Smart
Access". You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

I'm trying to add records to a subform and store a value in a particular
field, just like in the Northwind database Order Details form. The

[quoted text clipped - 45 lines]

I would greatly appreciate any assistance!
Slez

 

[Slez via AccessMonster.com]

This sounds easy, but I guess this just isn't my day. I have copied modules
to my database previously, but each time I try to paste in mdlText, I get the
old "Access has encountered an error and needs to shut down..." error message.


I tried to click "New" and paste in the code, but it doesn't appear that
modules let you do a "Save As" command to save it as a database object. Very
frustrating indeed, considering it seems like I'm so close to making this
work.

Is there a reason that it would not let me paste your module into my database?

Slez

There's nothing intrinsically wrong with using single or double quotes in
the text. It just makes the processing a little more complicated (but not
unreliable, or anything like that).

Download the sample I indicated, and copy mdlText from it into your
application.

You'd then use:

strFilter = "ProductDescription = " & CorrectText(Me!ProductDescription)

Thanks for your reply! I checked out your Access Answers per the link you
provided and I appear to be in "way over my head" so I'll just ask you

[quoted text clipped - 43 lines]

 

[Slez via AccessMonster.com]

After I posted my last time, I found that I had downloaded in Access 97 file
format. Converting to 2000 allowed me to paste it in. I now get an error
message:

Compile error: Sub or function not defined.

When I click OK, VBA highlights the following code in yellow:

Function CorrectText( _
InputText As String _
, Optional Delimiter As String = "'" _
) As String

This is turning out to be more work than it's worth, so I think I'm going to
eliminate the quotes in the approximately 30 products that they exist in. I
posted this so you had knowledge of what I encountered with your code. I'm
bummed that I can't get it to work, but thank you for all your help with this!

Slez

This sounds easy, but I guess this just isn't my day. I have copied modules
to my database previously, but each time I try to paste in mdlText, I get the
old "Access has encountered an error and needs to shut down..." error message.

I tried to click "New" and paste in the code, but it doesn't appear that
modules let you do a "Save As" command to save it as a database object. Very
frustrating indeed, considering it seems like I'm so close to making this
work.

Is there a reason that it would not let me paste your module into my database?

Slez

There's nothing intrinsically wrong with using single or double quotes in
the text. It just makes the processing a little more complicated (but not

[quoted text clipped - 12 lines]