Run time error 3075 and/or 2001

[Katy]

Hi All

I've been wrestling with this for a while. I have tried many variations of the below code. Basically, I have a combo box for ItemReferenceNumber and I want it to bring up the price as allocated in tblProducts:

Private Sub ItemReferenceNumber_AfterUpdate()
Dim strFilter As String
strFilter = "ItemReferenceNumber = " & Me!ItemReferenceNumber & "'"
Me!ItemUnitPrice = DLookup("Price", "tblProducts", strFilter)
End Sub

I either get 'Run time error 3075 Syntax eror in string in query expression with the above code' or 'run time error 2001 you have cancelled the previous operation' for the below code:

Private Sub ItemReferenceNumber_AfterUpdate()
Dim strFilter As String
strFilter = "ItemReferenceNumber = " & Me!ItemReferenceNumber
Me!ItemUnitPrice = DLookup("Price", "tblProducts", strFilter)
End Sub

ItemReferenceNumber is text and is the PK for tblProducts

Any suggestions gratefully received

Katy

 

[Rabbie]

This string looks to have an extra ' at the end - strFilter = "ItemReferenceNumber = " & Me!ItemReferenceNumber & "'"

Actually I think i needs one just after the =

You do need the ' round the Me!ItemReferenceNumber when you generate the string

 

[Katy]

Thanks. I tried:

strFilter = "ItemReferenceNumber = " & Me!ItemReferenceNumber & ""

and it came up with the 2001 error so the syntax error is gone. Looking at previous threads, this code should now work but I just can't seem to get it!

 

[Katy]

Thanks for that

I got "Run Time Error 451 Property let procedure not defined and property get procedure did not return and object." So I changed it to this:

strFilter = "ItemReferenceNumber = " & Me!ItemReferenceNumber.Column(0)

That got rid of the run time error 451.

I added:

MsgBox "strFilter = [" & strFilter & "]"

to check what it was looking at and got:

strFilter=[ItemReferenceNumber = ts5] (which is correct)

then Run Time Error 2001 came up again!

btw the form is a subform, not sure if that makes any difference.

Katy

 

[Rabbie]

Some silly questions for you.

Is there an field in tblProducts called ItemReferenceNumber

Is there one holding the value "ts5"

Because if the answer to both these questions is Yes then i am stumped for the moment.

It shouldnt be a problem that its a subform as you seem to be picking up the right info from the combo box.

 

[Katy]

Yes and yes, to be honest, I'm glad it's not just me. I lifted this code out of Northwind (why reinvent the wheel?!) where it is also in a subform and it works fine there but is also included within loads of other coding. I will set up a new database with just this table in and see if it works then

 

[pbaldy]

Given that this is a string value:

ItemReferenceNumber = ts5

I think the end result may need to look like:

ItemReferenceNumber = 'ts5'

 

[Katy]

Done it!!
Thanks guys, the end result looked like this:
strFilter = "ItemReferenceNumber = '" & Me!ItemReferenceNumber.Column(0) & "'"
and it worked.
Phew