Global Variable problem

[FranS37]

I set a global variable in the declarations section of a form - Public PONumber as Long

Then in the click event of a command button on the form I use:

POnumber = Me![PurchaseOrderNo]
Set dbs = CurrentDb
stSql = "SELECT * FROM [InventoryTransactions]"
stSql = stSql & " WHERE [PurchaseOrderNo] = PONumber"
stSql = stSql & " ORDER BY gponumber;"
Debug.Print stSql
Set rs = dbs.OpenRecordset(stSql, dbOpenDynaset, dbSeeChanges)

The debug.print shows that the variable in the SQL is not finding the number even though
POnumber = Me![PurchaseOrderNo] is finding it

I get an error 'Too few parameters. Expected 1' when the on the set rs = ...

I guess there's something wrong with my SQL.
Can anyone help?

Thanks.

Fran

 

[jedel]

Fran,

It looks to me that you have split up your code. A Simple way to see if you have written your code properly is to write a query and look at it in SQL view.

Try this code:

POnumber = Me![PurchaseOrderNo]
Set dbs = CurrentDb
stSql = "SELECT * FROM [InventoryTransactions]WHERE ((( nameoftable.[PurchaseOrderNo]) = Forms!Formname!PONumber))ORDER BY Nameoftable.gponumber;"
Debug.Print stSql
Docmd.RunSQL stsql
Set rs = dbs.OpenRecordset(stSql, dbOpenDynaset, dbSeeChanges)

I can't make any garuntees that this code will work, but you can see how the SQL is all in one now?

Hope this helps

Cheers

Dean

 

[danvlas]

stSql = stSql & " WHERE [PurchaseOrderNo] = PONumber"
is wrong

stSql = stSql & " WHERE [PurchaseOrderNo] = " & PONumber
is right

Good luck





Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html