I have a form in Microsoft Access. It has an Update button
On the click event of the update button i want to update certain database
records. I have the following code written for it. 


Private Sub update_Click()

Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb
strSQL = "update order_file where set status='Accept' where status_code=1"
Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)

End Sub

It however gives me some kind of Automation Error.


I donot want to use the Macro Builder / Expression Builder feature.
I would want to use the code builder feature so that later i cud write 
complex queries based on certain if's and else's conditions.

I m pretty new to Access

Cud anybody let me know what the error is ?

It looks like the error is in the line:
strSQL = "update order_file where set status='Accept' where
status_code=1"
You have an extra "where" in then line. It should read:
strSQL = "update order_file set status='Accept' where status_code=1"
Try that and see if it works. Also, always a good idea to put square
brackets around table and field names, eg [status]

Richard Lobel
President
NoClassroom.com
Live Software training 
Right over the Internet 
richard@n...
Tel:  (xxx) xxx-xxxx
Fax:  (xxx) xxx-xxxx

*****ORIGINAL MESSAGE*****

I have a form in Microsoft Access. It has an Update button
On the click event of the update button i want to update certain
database
records. I have the following code written for it. 


Private Sub update_Click()

Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb
strSQL = "update order_file where set status='Accept' where
status_code=1"
Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)

End Sub

It however gives me some kind of Automation Error.

After looking at the code again I also wonder why you are using a
querydef instead of simply executing your update statement. Right after
you set your string value, how about:
Dbs.Execute strSQL
That way you can avoid using the querydef at all.

Richard Lobel
President
NoClassroom.com
Live Software training 
Right over the Internet 
richard@n...
Tel:  (xxx) xxx-xxxx
Fax:  (xxx) xxx-xxxx

Try:

       'Set SQL statement
        strSQL = "UPDATE order_file " _
                    & "SET Status='Accept'  " _
                     & "WHERE Status_Code=1;"

       'Set Database
        Set dbs = CurrentDb

       'Execute SQL statement
        dbs.Execute strSQL, dbSQLPassThrough

  


____________________________________________________

John Fejsa
Systems Analyst/Computer Programmer
Hunter Centre for Health Advancement
Locked Bag 10, WALLSEND NSW 2287
Phone: (02) 4924 6336 Fax: (02) 4924 6209
www.hcha.org.au
____________________________________________________

The doors we open and close each day decide the lives we live
____________________________________________________

CONFIDENTIALITY & PRIVILEGE NOTICE
The information contained in this email message is intended for the named addressee only.  If you are not the intended recipient you
must not copy, distribute, take any action reliant on, or disclose any details of the information in this email to any other person
or organisation.  If you have received this email in error please notify us immediately. 
>>> vdeshpande@m... 27/08/2002 6:14:01 >>>
I have a form in Microsoft Access. It has an Update button
On the click event of the update button i want to update certain database
records. I have the following code written for it. 


Private Sub update_Click()

Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb
strSQL = "update order_file where set status='Accept' where status_code=1"
Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)

End Sub

It however gives me some kind of Automation Error.


I donot want to use the Macro Builder / Expression Builder feature.
I would want to use the code builder feature so that later i cud write 
complex queries based on certain if's and else's conditions.

I m pretty new to Access

Cud anybody let me know what the error is ?