DLookup Problem

[Guest]

In a order style form i have created the following in the afterupdate of a
combobox (PartName) to populate another field (Combo22):

Private Sub PartName_AfterUpdate()
'On Error GoTo Err_PartName_AfterUpdate

Dim strFilter As String

strFilter = "[Part] = " & Me![PartName]

Me!Combo22 = DLookup("PartNo", "Parts", strFilter)

Exit_PartName_AfterUpdate:
Exit Sub

Err_PartName_AfterUpdate:

MsgBox Err.Description
Resume Exit_PartName_AfterUpdate
End Sub

The problem is i get error 2001 "You canceled the previous operation". Whats
confusing me also, is that if i change [Part] to a different field called
[No] and change its row source to [No], it works fine. This is the structure
of the table: [No] - Autonumber, [Part] - Text, [PartNo] - Text.
I'm sure its not currption as i have rewritten the whole lot in a new
database and it does the same. Any help much appreciated

TIA

Rico

 

[Van T. Dinh]

Try:

strFilter = "[Part] = '" & Me![PartName] & "'"

 

[Sandra Daigle]

When doing comparisons of data with text fields the comparison value must be
wrapped in quotes such that when the SQL gets to the database engine, the
quotes are around the string value. These quotes are in addition to the
string delimiting quotes used by VBA when constructing the SQL. Here's how
you do it - you put in pairs of double quote characters (") around the
string value. When VBA creates the SQL, the pair of characters is converted
to a single character which is kept in the final string. Here is how it
looks:

strFilter = "[Part] = """ & Me![PartName] & """"

The last part of the string is required since you have to append the final
quote character to the string. The first and last quote characters are the
VBA string delimiters, the middle pair get converted to the single quote
character that is kept in the final string.

 

[Guest]

Brilliant thank you soooo much!! Just out of learning purposes what are you
doing by adding in the extra characters?

Thanks again

Rico

Try:

strFilter = "[Part] = '" & Me![PartName] & "'"


--
HTH
Van T. Dinh
MVP (Access)

In a order style form i have created the following in the afterupdate of a
combobox (PartName) to populate another field (Combo22):

Private Sub PartName_AfterUpdate()
'On Error GoTo Err_PartName_AfterUpdate

Dim strFilter As String

strFilter = "[Part] = " & Me![PartName]

Me!Combo22 = DLookup("PartNo", "Parts", strFilter)

Exit_PartName_AfterUpdate:
Exit Sub

Err_PartName_AfterUpdate:

MsgBox Err.Description
Resume Exit_PartName_AfterUpdate
End Sub

The problem is i get error 2001 "You canceled the previous operation". Whats
confusing me also, is that if i change [Part] to a different field called
[No] and change its row source to [No], it works fine. This is the structure
of the table: [No] - Autonumber, [Part] - Text, [PartNo] - Text.
I'm sure its not currption as i have rewritten the whole lot in a new
database and it does the same. Any help much appreciated

TIA

Rico

 

[Van T. Dinh]

You want to compare [Part] with a literal String value and literal String
needs to be enclosed in single (or double) quotes. Thus, the result of the
concatenation you need is:

[Part] = '(some literal text)'

Hence, the expression I suggested.

Sandra's suggestion works also as the result of her expression is:

[Part] = "(some literal text)"