Access 2003 VBA HELP HELP 2

[khaled99]

Hello,
I need help... I am working on my project and I have been getting this error message (run-time error ‘3265’ item not found in this collection) when I click on the debug it highlight this code:
[highlight] Set rstItem_Price = db.Recordsets(strSQL)[/highlight]
If some one can tell me how to can fix the code

this is the code i am working on:


Private Sub cmdCart_Click()
Dim db As DAO.Database
Set db = CurrentDb
Dim curItem_PricePrice As Currency
Dim curDiscountRate As Currency
Dim curDiscountTotal As Currency
Dim curSalesTax As Currency
Dim strSQL As String
strSQL = " SELECT * FROM tblItem WHERE Item_Price = """ & CStr(cboItem.Value) & """"
Dim rstItem_Price As DAO.Recordset 'get product price
Dim rstDetail As DAO.Recordset 'create order detail
Dim rstorderid As DAO.Recordset 'update shopping cart

'get price and discount rate of product
[highlight]Set rstItem_Price = db.Recordsets(strSQL)[/highlight]
With rstItem_Price
.Edit
curItem_Price = !Item_Price
.Update
End With

'create order detail
Set rstDetail = db![order details].OpenRecordset
With rstDetail
.AddNew
!Item_ID = cboItem.Value
!Ord_ID = mintorderid
!Item_Price = curProductPrice
End With

End Sub

 

[ZmrAbdulla]

Remove "s" from the end

Set rstItem_Price = db.Recordset[b][COLOR=red]s[/color][/b](strSQL)

Set rstItem_Price = db.Recordset(strSQL)

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[khaled99]

Zameer Abdulla i tried it but it did not work

 

[ZmrAbdulla]

And What about the redbold text here? Is there any spelling mistake there?

Dim db As DAO.Database
Set db = CurrentDb
Dim [b][COLOR=red]curItem_PricePrice[/color][/b] As Currency
Dim curDiscountRate As Currency
Dim curDiscountTotal As Currency
Dim curSalesTax As Currency
Dim strSQL As String
strSQL = " SELECT * FROM tblItem WHERE Item_Price = """ & CStr(cboItem.Value) & """"
Dim rstItem_Price As DAO.Recordset 'get product price
Dim rstDetail As DAO.Recordset 'create order detail
Dim rstorderid As DAO.Recordset 'update shopping cart

'get price and discount rate of product
Set rstItem_Price = db.Recordsets(strSQL)
With rstItem_Price
.Edit
[b][COLOR=red]curItem_Price[/color][/b] = !Item_Price
.Update
End With

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[willir]

Try some debugging. Access may not like your SQL statement.

Hit Ctrl-G to open up the Debug / Immediate window. then type the following...
? strSQL
This will display your SELECT statement.
SELECT * FROM tblItem WHERE Item_Price = " YourValue "


A couple of comments...
- You have a leading space in the SELECT statment
"[COLOR=yellow blue] [/color]SELECT..."
- CStr may also include a leading space in the value (to accommodate the negative sign).

Copy the SELECT statement (in red) as printed in the Debug / Immediate window to the clipboard.

Open up the QueryBuilder. From the menu, "Insert" -> "Query". Select "Design view". Do not add any tables. Switch from Design view to SQL view. From the menu, "View" -"SQL view".

Paste the query from your clipboard to the SQL view. Make sure the SQL statement is terminated with a semicolon, ";".

Run the query.

The query tool will generate the same error, but now the offending section will be highlighted. A common error is not providing the correct field / column name.

Correct SQL statement until it works. Make the same correction on the SQL statement in your VBA code.

Richard

 

[RoyVidar]

This is the general database forum. Specicific products have specific fora - for Access there's seven. You'll find them through a forum search at the top of the page.

You may also have a look at this faq faq181-2886, on how to get the most out of the membership.

Opening recordsets in Access with the DAO method, I think, use the openrecordset method:

[tt] Set rstItem_Price = db.openrecordset(strSQL)[/tt]

You'll also get a problem at the line

[tt]Set rstDetail = db![order details].OpenRecordset[/tt]

See the first suggestion, hit F1 while the cursor is whitin "openrecordset", do a search on openrecordset in the Access fora, you should find something to make it work.

Roy-Vidar

 

[ZmrAbdulla]

These are the Access Fora links Roy-Vidar talking about
Microsoft: Access Forms Forum
Microsoft: Access Modules (VBA Coding) Forum
Microsoft: Access Queries and JET SQL Forum
Microsoft: Access Reports Forum
Microsoft: Access Tables and Relationships Forum
Microsoft: Access Other topics Forum
Microsoft: Access Project (ADP) Forum


Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me