Update query not working...

[raghuprabhu]

Hi All,

The following is the code behind the click event of a button to close a modal form.

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
Dim conn As ADODB.Connection
Dim sSql As String
 
'    DoCmd.SetWarnings False
'    DoCmd.OpenQuery "qryUpdate", acViewNormal, acEdit
'    DoCmd.SetWarnings True
'    DoCmd.Close
'    Forms("frmMain").Form("subFrmUpdatedOn").Requery
 
    sSql = "UPDATE tblUpdatedOn SET tblUpdatedOn.updateDate = Date();"  '<<<<<< What is wrong with this sql code. It is not updating.
'    Debug.Print sSql
    Set conn = CurrentProject.Connection
    conn.Execute sSql
    Forms("frmMain").Form("subFrmUpdatedOn").Requery
Exit_cmdClose_Click:
    Exit Sub
Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click
 
End Sub

Basically

sSql = "UPDATE tblUpdatedOn SET tblUpdatedOn.updateDate = Date();" is not working. Is the syntex wrong?

It is working ok with

DoCmd.OpenQuery "qryUpdate", acViewNormal, acEdit

I don't like to use queries in the database. I use code behind the queries. Any help would be appreciated.



Thank you
Raghu Prabhu
Melbourne,

 

[John Big Booty]

There is nothing wrong with your SQl syntax. Although be aware that your SQL statement, as it stands, will update the field updateDate of all records in your table tblUpdatedOn to the current Date.

The problem is that you are not actually running the SQl statement.

You need to add the line;

DoCmd.RunSQL sSql

to actually run the SQL statement.

 

[mdlueck]

The problem is that you are not actually running the SQl statement.

I think perhaps the OP thinks this LOC is running the query...

conn.Execute sSql

I have coded my queries as ADO or DAO objects, so not familiar with their syntax.

 

[vbaInet]

sSql = "UPDATE tblUpdatedOn SET tblUpdatedOn.updateDate = Date();" is not working. Is the syntex wrong?

How do you know it's not working on that line? Comment out the error handling code, run it again, hit Debug (when it errors) and tell us exactly:

1. the line that gets highlighted
2. the error message.