Select in .OPEN

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
 
G

Graham R Seach

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
 
G

Guest

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.
 
G

Graham R Seach

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
 
V

Van T. Dinh

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'
 
B

Brian Kastel

Did you discover that? Wow... Access 2000 reports
{00010409-78E1-11D2-B60F-006097C998E7} on my machine. That is bizarre.
 
G

Guest

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?
 
G

Guest

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.
 
G

Graham R Seach

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?
 
G

Guest

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

codes with FILTER 10
How do I open a txt file? 8
newbie needs examples of creating a recordset and inserting datausing it in Access 2007? 6
need some SQL Update Help 1
Not Responding - Open Recordset 6
ADO File Open Error 2
Connection Problem (Oracle and Access join) 5
Work Around for Like '*active*' 5

Top