Need Help executing an update in form

[ismith]

Hello,

Ive got a form where a customer can place an order. now at the bottom i want to put an execute statement which will look at the product ID that has been ordered and make sure that it dont allow that one to be entered again. This is the coding ive got atm to do that:

db.Execute "UPDATE tblProduct SET chosen = True WHERE ProductID = " & cmbProductID

the error message im getting is:


Run-time error '3061': Too few parameters. Expected 1.


anyone know how i can sort this out?

Thanks..

[Robeen]

Can you post the entire code - rather than just the one line that is generating the error?

[ismith]

Private Sub addprod()
Set db = CurrentDb
Set orderbasket = db.OpenRecordset("tblOrderBasket")
Set productDetails = db.OpenRecordset("SELECT * FROM tblProduct WHERE ProductID = " & Me.cmbProductID.Value)

If MsgBox("Would you like to order another item?", vbQuestion + vbYesNo, "Confirmation") = vbNo Then
     Set order = db.OpenRecordset("tblOrder")
     
     orderbasket.AddNew
     
     orderbasket("OrderNo") = txtOrderNo.Value
     orderbasket("ProductID") = cmbProductID.Value
     orderbasket("Description") = txtDescription.Value
     orderbasket("Category") = txtCategory.Value
     orderbasket("Size") = txtSize.Value
     orderbasket("Stock") = txtStock.Value
     orderbasket("Quantity") = cmbQuantity.Value
     orderbasket("Price") = txtPrice.Value
     orderbasket("TotalPrice") = txtTotal.Value
     
     'This finds the product currently being bought in the product table so the
     'stock levels can be changed.
     
     productDetails.Edit

     cmbProductID = productDetails("ProductID")
     
     'This changes the stock levels to minus the quantity being bought.
     
     productDetails("Stock") = productDetails("Stock") - (Me.cmbQuantity.Value)
     orderbasket("Stock") = productDetails("Stock")
     
     'This updates the tables
     orderbasket.Update
     productDetails.Update
     
     'This populates the list box to show all the products being ordered for the customer.
     
     lstOrder.RowSource = ""
     
     strsql = "SELECT tblOrderBasket.OrderBasketID, tblOrderBasket.ProductID, tblOrderBasket.[Description], tblOrderBasket.[Category], tblOrderBasket.[Size] ,tblOrderBasket.[Stock], tblOrderBasket.[Quantity],tblOrderBasket.[Price], tblOrderBasket.[TotalPrice]"
     strsql = strsql & "FROM tblOrderBasket WHERE tblOrderBasket.[OrderNo] = " & Forms![frmOrder]![txtOrderNo] & ";"

     Me![lstOrder].RowSource = strsql
        
     'This ensures any item that is selected will not appear for the same order again
     db.Execute "UPDATE tblProduct SET chosen = True WHERE ProductID = " & cmbProductID
     lstOrder.Requery
     cmbProductID.Requery

Thats my code

[ismith]

Anyone know whats wrong?

Need urgent help.

[Robeen]

Hi ismith,
I thought I responded to this . . .
I set this code up to run off a command button on a test form:

 
Private Sub cmdUpdateRecord_Click()
Dim db As Database
Set db = CurrentDb
db.Execute "UPDATE [Copy Of 12-09-2011] SET [Chosen] = True WHERE ID = " & Combo35
End Sub

It works fine - and I was able to Update 'Chosen' field to True on several records without any problem

Am I missing something?

Was that db.execute line the one that was giving you the error? Or was it another line of code that was the problem?

[ismith]

It was db.execute.

What i wanna do there is remove what the database was looking at but make sure that the same item cant be ordered again.

[ismith]

Still giving me the same error as given above. Ive tried the [] brackets aswell. Not sure how to sort it out.

When i press debug the db.execute line is highlighted..

[Robeen]

can you post your db here?

[ismith]

my whole database? how do i do that?

ProductID Description Category Stock Size Price
1 Wallpaper Paste Brush PaintBrush 4 £3.00
2 Wallpaper Hanging Brush PaintBrush 16 £3.00
3 Artist Brush (10 Pack) PaintBrush 12 £3.50
4 Standard Paint Brush PaintBrush 13 £3.00
5 Vinal Silk (White) Paint - Emulsion 9 2.5l £6.99


Thats how my database is.
Thats my tblProduct

[Robeen]

When you reply to a post - click 'Go Advanced' [below where you are typing].
Then - below the new window that opens - look under 'Additional Options' below the 'Submit Reply' -> you will see a 'Manage Attachments' button.
If your DB is small enough - it will let you post it.
If not - you can zip it - or strip it down to the 'bare necessities' for this problem and then try posting it here.

[ismith]

Mate ive tried it. ive deleted everything besides my Product table and my order form but its still bigger than 500kb. what u want me to do?

[ssanfu]

Anyone know whats wrong?

Need urgent help.

In your code, didn't see where you declared your variables:

Dim db as Database
Dim orderbasket as Recordset
Dim productDetails as Recordset

Do you have the first two lines as:

Option Compare Database
Option Explicit

It looks like you are using ADO... I use DAO so this might be wrong, but why can't you just set "chosen = True" by using the .Edit command like the other fields? Something like:

     <snip>
     productDetails.Edit

     cmbProductID = productDetails("ProductID")
     
     'This changes the stock levels to minus the quantity being bought.
     
     productDetails("Stock") = productDetails("Stock") - (Me.cmbQuantity.Value)
     productDetails("chosen") = TRUE
     orderbasket("Stock") = productDetails("Stock")
<snip>

Then you don't have to execute the action query.

[ismith]

In your code, didn't see where you declared your variables:

Dim db as Database
Dim orderbasket as Recordset
Dim productDetails as Recordset

Do you have the first two lines as:

Option Compare Database
Option Explicit

It looks like you are using ADO... I use DAO so this might be wrong, but why can't you just set "chosen = True" by using the .Edit command like the other fields? Something like:

     <snip>
     productDetails.Edit

     cmbProductID = productDetails("ProductID")
     
     'This changes the stock levels to minus the quantity being bought.
     
     productDetails("Stock") = productDetails("Stock") - (Me.cmbQuantity.Value)
     productDetails("chosen") = TRUE
     orderbasket("Stock") = productDetails("Stock")
<snip>

Then you don't have to execute the action query.

So will this prevent that productID from being ordered again?

And yes i do have the option and they are DAO.Recordset.

[ssanfu]

So will this prevent that productID from being ordered again?

If that is the way you have programmed your database. I was trying to provide an answer to the action query problem. Another set of eyes/ideas....

And yes i do have the option and they are DAO.Recordset.

Then this is how I would have my code:

' at the top of every code page
Option Compare Database
Option Explicit


Private Sub addprod()
   Dim db as DAO.Database
   Dim orderbasket as DAO.Recordset
   Dim productDetails as DAO.Recordset

   Set db = CurrentDb
   Set orderbasket = db.OpenRecordset("tblOrderBasket")
   Set productDetails = db.OpenRecordset("SELECT * FROM tblProduct WHERE ProductID = " & Me.cmbProductID)

All I'm saying is that you have opened a recordset based on "tblProduct", filtered on [ProductID] with every field included. One of the fields is "chosen". Since you are on that record, then set the field to TRUE. No need to run the UPDATE query later in the code.

BTW, you don't need to add ".Value", since the value property is the default property. (Saves a lot of typing.)

[ismith]

Ive got this sorted.

Thanks for all your help guys..