Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Access 2003 VBA HELP HELP 2

Status
Not open for further replies.
khaled99

khaled99

MIS
Mar 12, 2005
5
0
0
US
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
 
Sort by date Sort by votes
Remove "s" from the end
Code: 
Set rstItem_Price = db.Recordset[b][COLOR=red]s[/color][/b](strSQL)
Code: 
Set rstItem_Price = db.Recordset(strSQL)

Zameer Abdulla
[sub]Jack of Visual Basic Programming, Master in Dining & Sleeping[/sub]
Visit Me
 
Upvote 0 Downvote
And What about the redbold text here? Is there any spelling mistake there?
Code: 
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
[sub]Jack of Visual Basic Programming, Master in Dining & Sleeping[/sub]
Visit Me
 
Upvote 0 Downvote
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

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Ashok Kumar J
  • Locked
  • Question
i am not able to print report on barcode printer.... please help!..... it does not print at all
Replies
0
Views
79
Ashok Kumar J
Ashok Kumar J
TopTeller
  • Locked
  • Question
Is ms-access a true recommendation for students to use with VB? 2
Replies
4
Views
124
SamBones
SamBones
DBServices
  • Locked
  • Question
Need help on using SQL Server tables linked to Access as a front-end
Replies
3
Views
103
Darrylles
Darrylles
JAFrank
  • Locked
  • Question
I could really use help with a SQL query 1
Replies
7
Views
138
JAFrank
JAFrank
gmagerr
  • Locked
  • Question
Help with Normalization 1
Replies
5
Views
90
gmagerr
gmagerr

Part and Inventory Search

Sponsor

Back
Top