SQL Syntax: Can anyone help?

[bobdydd]

Hi All

Access 2007 Windows XP

I have a table called:
UsystblApplication

Which has 2 fields called:
1. FieldName
2. Number

I want to be able to Update the reccord from a form which
has a control called:
1. Me.txtLastUsedRecordProject

And do it from a Command Button. I am using the code below,
and although I get the warning that I won't be able to undo it.
It just doesn't not alter. Here is the code I am using
Can anyone help?

Private Sub CmdLastUsedRecord_Click()

Dim strSQL As String
strSQL = "UPDATE UsystblApplication SET [Number]= " _
& txtLastUsedRecordProject _
& " WHERE UsystblApplication.Number= &
Me.txtLastUsedRecordProject"
DoCmd.RunSQL

End Sub

 

[Ken Snell \(MVP\)]

You didn't give the SQL statement to the RunSQL command:

Private Sub CmdLastUsedRecord_Click()

Dim strSQL As String
strSQL = "UPDATE UsystblApplication SET [Number]= " _
& txtLastUsedRecordProject _
& " WHERE UsystblApplication.Number= &
Me.txtLastUsedRecordProject"
DoCmd.RunSQL strSQL

End Sub

By the way, our recommendation here would be to use the .Execute method of
the CurrentDb object because it will let you trap for errors:


Private Sub CmdLastUsedRecord_Click()

Dim strSQL As String
Dim dbs As DAO.Database
strSQL = "UPDATE UsystblApplication SET [Number]= " _
& txtLastUsedRecordProject _
& " WHERE UsystblApplication.Number= &
Me.txtLastUsedRecordProject"
Set dbs = CurrentDb
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nothing

End Sub

 

[Guest]

I think you just have the end quote in the wrong place, so you have no
records to update. I believe it should be

Dim strSQL As String
strSQL = "UPDATE UsystblApplication SET [Number]= " _
& txtLastUsedRecordProject _
& " WHERE UsystblApplication.Number= " & Me.txtLastUsedRecordProject
DoCmd.RunSQL

 

[bubbles]

Hi All

Access 2007 Windows XP

I have a table called:
UsystblApplication

Which has 2 fields called:
1. FieldName
2. Number

I want to be able to Update the reccord from a form which
has a control called:
1. Me.txtLastUsedRecordProject

And do it from a Command Button. I am using the code below,
and although I get the warning that I won't be able to undo it.
It just doesn't not alter. Here is the code I am using
Can anyone help?

Private Sub CmdLastUsedRecord_Click()

Dim strSQL As String
strSQL = "UPDATE UsystblApplication SET [Number]= " _
& txtLastUsedRecordProject _
& " WHERE UsystblApplication.Number= &
Me.txtLastUsedRecordProject"
DoCmd.RunSQL

End Sub

1. What is the value of Me!txtLastUsedRecordProject ?

2. Try this:

strSQL = "UPDATE UsystblApplication SET [Number]= " & _
txtLastUsedRecordProject & _
" WHERE UsystblApplication.Number= " & _
Me!txtLastUsedRecordProject

3. Are you updating the correct field?

Bubbles