Variable problem from 2003-2007

[martinhough]

Hi

I have a routine that appeared to work fine in ms Access 2003 but since being upgraded to 07 now prompts me for the value of a variable (testid). When I toggle the section concerned the variable seems to be holding the correct value, but is still prompting me for one. Extract of code in question posted below. Red section is where it breaks, prompting me for 'testid' value.

Many thanks

testid = rsdetails("ProductID")


SQL2 = "UPDATE ProductOrders SET ProductOrders.ProductProcessed = True WHERE (((ProductOrders.ProductID)= [testid]));"

If rsdetails("product authorised") = -1 And rsdetails("productprocessed") <> -1 Then
DoCmd.SetWarnings False
DoCmd.RunSQL SQL2
Recalc
' DoCmd.OpenQuery "ProductsProcessedUpdate", acViewNormal
DoCmd.SetWarnings True
End If

rsdetails.MoveNext
Loop
rsdetails.Requery

 

[vbaInet]

This:

SQL2 = "UPDATE ProductOrders SET ProductOrders.ProductProcessed = True WHERE (((ProductOrders.ProductID) = " & [testid] & "));"

or this

SQL2 = "UPDATE ProductOrders SET ProductOrders.ProductProcessed = True WHERE (((ProductOrders.ProductID) = '" & [testid] & "'));"

 

[nanscombe]

Shouldn't that be ...

If ProductID is a number

SQL2 = "UPDATE ProductOrders SET ProductOrders.ProductProcessed = True WHERE (((ProductOrders.ProductID) = " & testid & "));"

or, If ProductID is a string

SQL2 = "UPDATE ProductOrders SET ProductOrders.ProductProcessed = True WHERE (((ProductOrders.ProductID) = '" & testid & "'));"


If you put the variable in [] doesn't it become an input prompt in SQL?

 

[martinhough]

Ah, you're a star! Yes, that worked. Thanks. Why did that not show in my 03 version then?

 

[vbaInet]

I don't see how that would work in 2003 if testid is a variable. Is testid also the name of a field?

 

[nanscombe]

Ah yes, of course. If the code were on a form, it could be getting the value from a control called testid.

Silly me, a lot of the code I built was in modules.