×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Access 2003 VBA HELP HELP
2

Access 2003 VBA HELP HELP

Access 2003 VBA HELP HELP

(OP)
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:
Set rstItem_Price = db.Recordsets(strSQL)
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
Set rstItem_Price = db.Recordsets(strSQL)
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

RE: Access 2003 VBA HELP HELP

Remove "s" from the end

CODE

Set rstItem_Price = db.Recordsets(strSQL)

CODE

Set rstItem_Price = db.Recordset(strSQL)

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

RE: Access 2003 VBA HELP HELP

(OP)
Zameer Abdulla i tried it but it did not work

RE: Access 2003 VBA HELP HELP

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

CODE

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
Set rstItem_Price = db.Recordsets(strSQL)
With rstItem_Price
.Edit
curItem_Price = !Item_Price
.Update
End With

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

RE: Access 2003 VBA HELP HELP

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
" 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

RE: Access 2003 VBA HELP HELP

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:

Set rstItem_Price = db.openrecordset(strSQL)

You'll also get a problem at the line

Set rstDetail = db![order details].OpenRecordset

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close