dynaset recordset won't work

[eric]

I get a too few parameters error trying to create a
recordset. The code below is in a different .mdb file

Dim str As String, db As Database, rst As Recordset
Set db = OpenDatabase("c:\testdata.mdb")

str = "SELECT [PO Parts].[PO#], [PO Parts].Description,
[PO Parts].Qty, " & _
"[PO Parts].QtyRecd2, [PO Parts].TotQtyRecd2, [PO
Parts].OrgOrdQty " & _
"FROM [PO Parts] ORDER BY [PO Parts].[Description] &
CStr([PO Parts].[PO#]);"
Set rst = db.OpenRecordset(str, dbOpenDynaset)

 

[Doug]

-----Original Message-----
I get a too few parameters error trying to create a
recordset. The code below is in a different .mdb file

Dim str As String, db As Database, rst As Recordset
Set db = OpenDatabase("c:\testdata.mdb")

str = "SELECT [PO Parts].[PO#], [PO Parts].Description,
[PO Parts].Qty, " & _
"[PO Parts].QtyRecd2, [PO Parts].TotQtyRecd2, [PO
Parts].OrgOrdQty " & _
"FROM [PO Parts] ORDER BY [PO Parts].[Description] &
CStr([PO Parts].[PO#]);"
Set rst = db.OpenRecordset(str, dbOpenDynaset)
.
Check the ORDER BY clause. The & should be AND

 

[Tim Ferguson]

Dim str As String, db As Database, rst As Recordset
Set db = OpenDatabase("c:\testdata.mdb")

str = "SELECT [PO Parts].[PO#], " & _
" [PO Parts].Description, " & _
" [PO Parts].Qty, " & _
" [PO Parts].QtyRecd2, " & _
" [PO Parts].TotQtyRecd2, " & _
" [PO Parts].OrgOrdQty " & _
"FROM [PO Parts] " & _
"ORDER BY [PO Parts].[Description] & CStr([PO Parts].[PO#]);"

Set rst = db.OpenRecordset(str, dbOpenDynaset)

There are a number of problems with this, although I cannot tell which is
causing your particular error.

Firstly Str is the name of a VBA function -- the compiler really should
have choked on that, although it obviously did not.

Next, you are using stacks of illegal characters in table names: # and
space will get you into trouble in any db environment less shaggy than
Access.

Next, the Order By clause is odd, although it is not exactly illegal. It is
normal to specify multiple sorting criteria as a list, so "ORDER BY
Description, [PO#]" would be better. Remember that CStr() will result in
alphabetic sorting like 1, 11, 12, 13, ... 2, 21, which may not be what you
want. Using the normal method will sort in normal numerical order.

Finally, your Missing Parameter error nearly always indicates a misspelt
field name somewhere. I would look carefully at things like TotQtyRecd2 --
this is one of the reasons why it's better to use normal (english) words
because you can spot the errors more easily.

By the way, how normalised is QtyRecd2..?

Hope that helps


Tim F