dlookup function 4

[00trav]

Hi all,
I have having a problem with the dLookup() function in MS Access 2003 (saved database as Access 2000).

I am fairly new to the VB aspect of access. I am familiar with Lingo (years ago), HTML, PHP, and MySQL. For some reason i find it very hard to go from HTML/PHP/MySQL to Access. I guess i just got used to a very linear display, submit, redisplay method. Anyway, this is my attempt at making a subform for an RMA that displays the price of an item once that item is selected from a combo list. The price is stored in a table called "Items" and it is the third column (if that matters)called "Price". The unique key for the table is "ItemNum". so i put this code in the after update event procedure for the combo box with the Item Num.

Here is my code

Option Compare Database

Private Sub ItemNum_AfterUpdate()

    Dim strFilter As String
    Dim strResult As String
    
    ' Evaluate filter before it's passed to DLookup function.
    strFilter = "ItemNum = " & Me!ItemNum
    
    ' Look up product's unit price and assign it to Price control.
    strResult = DLookup("Price", "Items", strFilter)
    Me!Price = strResult
Exit_ProductID_AfterUpdate:
    Exit Sub

End Sub

[\code]

I recieve this error
"runtime error '2001'
you canceled the previous operation

 

[MajP]

Everything looks OK to me. "Items" is a reserved word in some of the libraries (how about "tblItems", that is the standard), but I replicated this and it still worked. Is item number a string? Then

strFilter = "ItemNum = '" & Me!ItemNum & "'"

What other events are on the form, and control?

 

[00trav]

Thanks, you were right with the ItemNum being a string. as i am still new to VB syntax, why is it that you need the double and single quotes for the the second string, and not the first.

thanks

 

[stix4t2]

00trav,

One technique you may try is adding the price as a column to the combo box. You have to change the column count and set the column width to show or hide the price. Then use this code to set the price.

me!price = itemNum.column(1)

note: 1 is a sample column number, so use the column number from the combo's rowsource. Use 0 to refer to the first column, 1 to refer to the second column, and so on.

 

[MajP]

To enter your question on the quotes. Assume Me.ItemNum = xz245

strFilter = "ItemNum = '" & Me!ItemNum & "'"
this produces the string: ItemNum = 'xz245'

You are referencing a string (xz245) inside of the bigger string.

 

[MajP]

Should read "To answer" not "To enter

 

[00trav]

thanks guys, I understand it now,

I got my form all up and running smooth now.

Thank you so much for your help.

Trav