Recordset instead of update query?

[mswilson16]

At the minute I have a form where when it is closed it runs an update query. In each record there is a stock ordered, stock arrived and status field.

When "stock ordered" is = "stock arrived" then the query will update the query so that it reads "complete".

I have various different update queries that run certain procedures like this.

What I need to know is,

a) can this update be done by using a recordset instead of a query

b) will it be quicker using a recordset than a query???

cheers in advance

 

[jebry]

Hi!

a.) Yes this can be done using a recordset. Just open the table and use the .Edit. After making your change, use .Update.

b.) The update query will certainly be as fast, almost certainly faster. You will want to turn off the warnings prior to running the query and turn them back on after it is run.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[TerpFan2001]

Wilson:
You can also run an UPDATE SQL statement through CurrentDB.Execute. You could place this code in the form close event:

Dim sSQL as String

sSQL = "UPDATE [YourTableNameHere] SET [Status] = 'Complete' WHERE [StockOrdered] = [StockArrived]"

CurrentDB.Execute sSQL

If you have an existing query, the easiest way to capture the SQL statement is to go into the SQL view of the query. Then copy it and paste it into your variable (in this case sSQL). This is much faster than using a recordset and you don't have to worry about turning off "warnings".

 

[mswilson16]

Cheers TerpFan!!!!

That is exactly what I wanted.

 

[mswilson16]

Sorry I made I mistake when I tried the code it does not work. I get an run tine error "3061"

"too few parameters. expected 1."

The code that I am using is:

Dim sSQL As String
sSQL = "UPDATE [sale item query] SET [amount in stock] = [amount in stock]-[quantity] WHERE [sale no] = [Forms]![Sale]![Sale No]"
CurrentDb.Execute sSQL

Any Ideas???

 

[TerpFan2001]

Wilson:

Just a couple of ideas:
1. After UPDATE you should have the name of the table you want to update. It looks as though you are referencing a query.

2. You also need to change the where clause, it should read

WHERE [sale no] = " & [Forms]![Sale]![Sale No]

If you are running this code from your Sale Form, you can make a reference to the control [Sale No] by using Me

WHERE [sale no] = " & Me.[Sale No]

The above examples assume that [sale no] is a number data type. If it happens to be a Text field you need to surround the parameter with '

WHERE [sale no] = '" & Me.[Sale No] & "'"

Anyway, a good way to view the SQL statement that is being created is to use a Msgbox to display it before you run it.

MsgBox sSQL

This will help you with debugging by letting you see the statement. Hope this helps.