Select in .OPEN

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone help me with this piece of code? The run-time error "-2147217904" saying that No value given for one or more required parameters

rst2.Open
"SELECT * FROM tblWarehouseProduct " &
"WHERE WarehouseCode = '" & Me!WarehouseCode & "'" &
" AND ProductCode = '" & Me!ProductCode & "'",
cnn1, adOpenKeyset, adLockOptimistic, adCmdTex

The tblWarehouseCode has a primary key composed of WarehouseCode + ProductCode

Many Thanks
 
Jessie,

There's nothing wrong with your code, so the only remaining thing must be
the value of the controls. Add Debug.Print statements just before you open
the recordset:

Debug.Print "WarehouseCode: " & Me!WarehouseCode
Debug.Print "ProductCode: " & Me!ProductCode

rst2.Open _
"SELECT * FROM tblWarehouseProduct " & _
"WHERE WarehouseCode = '" & Me!WarehouseCode & "'" & _
" AND ProductCode = '" & Me!ProductCode & "'", _
cnn1, adOpenKeyset, adLockOptimistic, adCmdUnknown

If either of them are blank, you've found your problem.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Thanks Graham. Actually I had done all these troubleshooting steps.

Here is the result from the immediate window
?me!warehousecod
Whse0
?me!ProductCod
Dell Noteboo

BTW, I also have assigned the Select statement to a var. Here is the result
test = "SELECT * FROM tblWarehouseProduct " &
"WHERE WarehouseCode = '" & Me!WarehouseCode & "'" &
" AND ProductCode = '" & Me!ProductCode & "'
?tes
SELECT * FROM tblWarehouseProduct WHERE WarehouseCode = 'Whse01' AND ProductCode = 'Dell Notebook

So it looks good so far. I really canot figure out the problem.
 
Jessie,

OK, you've piqued my interest now!

Try this:
rst2.Open "SELECT * FROM tbl", cn

If rst2.AbsolutePosition > adPosUnknown Then
rst2.Find "WarehouseCode = '" & Me!WarehouseCode & _
"'AND ProductCode = '" & Me!ProductCode & "'"

If rst2.EOF Then
Debug.Print "No records"
Else
Debug.Print "WarehouseCode: " & rst2!WarehouseCode
Debug.Print "ProductCode: " & rst2!ProductCode
End If

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
I am not sure whether this is related to your problem or
not but the name "ProductCode" seems to be a global
variable in JET (or Access???).

Try this: Create a Query with the SQL String:

SELECT ProductCode;

When you run this, you should get something like:

{90280409-6000-11D3-8CFE-0050048383C9}

(in AXP. It looks like a ClassID to me).

A have had some weird problems with the name "ProductCode"
so I avoid this name. Try renaming your Field to ProdCode
in Table(s) / Queries / SQL and see what happens.

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
Thanks Graham. Actually I had done all these troubleshooting steps.

Here is the result from the immediate window.
?me!warehousecode
Whse01
?me!ProductCode
Dell Notebook

BTW, I also have assigned the Select statement to a var. Here is the result.
test = "SELECT * FROM tblWarehouseProduct " & _
"WHERE WarehouseCode = '" & Me!WarehouseCode & "'" & _
" AND ProductCode = '" & Me!ProductCode & "'"
?test
SELECT * FROM tblWarehouseProduct WHERE WarehouseCode
Click to expand...
= 'Whse01' AND ProductCode = 'Dell Notebook'
 
Did you discover that? Wow... Access 2000 reports
{00010409-78E1-11D2-B60F-006097C998E7} on my machine. That is bizarre.
 
Hi Graham, I tried your suggestion but nothing printed. That means rst2.AbsolutePosition > adPosUnknown is NOT true. After your code, I added Debug.Print rst2.GetString and it printed the whole recordsets. Any idea?
 
Van, this "ProductCode" has been using in another module in my application. If I need to change this, I have to change all over my modules.
 
Jessie,

Well, I'm stumped! Can you send me the database?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Jessie said:
Hi Graham, I tried your suggestion but nothing printed. That means
Click to expand...
rst2.AbsolutePosition > adPosUnknown is NOT true. After your code, I added
Debug.Print rst2.GetString and it printed the whole recordsets. Any idea?
 
Thanks Graham. Please don't worry about my problem anymore. I solved it using FILTER. Below is my solution

1. Select * from tblWarehouseProduc
2. .Filter = .....

I know this may not be the most efficent way, but it has solved my problem

Thanks again for your guys help.
 
Back
Top