|
|
|
Select in .OPEN |
|---|
| message from =?Utf-8?B?SmVzc2ll?= on 23 May 2004 |
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, adCmdText
The tblWarehouseCode has a primary key composed of WarehouseCode + ProductCode.
Many Thanks
|
| Graham R Seach replied to =?Utf-8?B?SmVzc2ll?= on 24 May 2004 |
Jessie,
Change adCmdText to adCmdUnknown.
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" <anonymous@discussions.microsoft.com> wrote in message
news:18868D53-F0A6-411C-9D82-B0CBF55F25E6@microsoft.com...
"-2147217904" saying that No value given for one or more required
parameters.
|
| =?Utf-8?B?SmVzc2ll?= replied to Graham R Seach on 23 May 2004 |
Changed but still got the same error. Any other suggestion please.
|
| Graham R Seach replied to =?Utf-8?B?SmVzc2ll?= on 24 May 2004 |
Jessie,
There's nothing wrong with your code, so the only remaining thing must be
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
"Jessie" <anonymous@discussions.microsoft.com> wrote in message
news:F5D91356-2D11-465C-A6E0-E70937566AEA@microsoft.com...
|
| Graham R Seach replied to Graham R Seach on 24 May 2004 |
Jessie,
Plus, are you sure the codes are string? Could they be numeric?
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
"Graham R Seach" <gseach@NOSPAMpacificdb.com.au> wrote in message
news:%23wMdwlUQEHA.1960@TK2MSFTNGP10.phx.gbl...
|
| =?Utf-8?B?SmVzc2ll?= replied to Graham R Seach on 23 May 2004 |
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?
|
| =?Utf-8?B?SmVzc2ll?= replied to =?Utf-8?B?SmVzc2ll?= on 23 May 2004 |
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.
|
| Graham R Seach replied to =?Utf-8?B?SmVzc2ll?= on 25 May 2004 |
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" <anonymous@discussions.microsoft.com> wrote in message
news:BB4F5FF0-9E53-422B-9B7C-CAE119AFE752@microsoft.com...
rst2.AbsolutePosition > adPosUnknown is NOT true. After your code, I added
Debug.Print rst2.GetString and it printed the whole recordsets. Any idea?
|
| =?Utf-8?B?SmVzc2ll?= replied to Graham R Seach on 24 May 2004 |
Thanks Graham. Please don't worry about my problem anymore. I solved it using FILTER. Below is my solution.
I know this may not be the most efficent way, but it has solved my problem.
Thanks again for your guys help.
|
| =?Utf-8?B?SmVzc2ll?= replied to Graham R Seach on 23 May 2004 |
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 = 'Whse01' AND ProductCode = 'Dell Notebook'
So it looks good so far. I really canot figure out the problem.
|
| Graham R Seach replied to =?Utf-8?B?SmVzc2ll?= on 24 May 2004 |
Jessie,
OK, you've piqued my interest now!
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
"Jessie" <anonymous@discussions.microsoft.com> wrote in message
news:90BAE879-CD14-48DE-A517-8950E0E23E99@microsoft.com...
ProductCode = 'Dell Notebook'
|
| Graham R Seach replied to Graham R Seach on 24 May 2004 |
Jessie,
I forgot - Find doesn't handle multi-column searches. Try searching on each
column, one at a time.
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
"Graham R Seach" <gseach@NOSPAMpacificdb.com.au> wrote in message
news:ePwe2AVQEHA.3852@TK2MSFTNGP10.phx.gbl...
|
| Van T. Dinh replied to =?Utf-8?B?SmVzc2ll?= on 23 May 2004 |
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???).
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)
troubleshooting steps.
Here is the result.
& "'" & _
= 'Whse01' AND ProductCode = 'Dell Notebook'
problem.
|
| Graham R Seach replied to Van T. Dinh on 24 May 2004 |
Geez, I didn't think of that. Nice one Van!
Although, I'd have thought the use of the bang operator would get around
that.
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
"Van T. Dinh" <VanThien.Dinh@discussions.microsoft.com> wrote in message
news:110a401c44152$fe7a2d00$a301280a@phx.gbl...
|
| Van T. Dinh replied to Graham R Seach on 25 May 2004 |
Hi Graham
The dot operator did fix it for me, IIRC.
|
| Van T. Dinh replied to Van T. Dinh on 25 May 2004 |
Actually, I got stuck with it for a short while.
|
| Brian Kastel replied to Van T. Dinh on 24 May 2004 |
"Van T. Dinh" <VanThien.Dinh@discussions.microsoft.com> wrote in message
news:esHa8GbQEHA.904@TK2MSFTNGP12.phx.gbl...
|
|
