Need some Access VBA Help

[Florida Doc]

Hello,

I'm trying to write a subroutine that will update the usermodified field when a person clicks the save user button on a form. However, I keep getting the error: "Object variable or With block variable not set".

The table name is Users, the field I want to modify is UserModified, the field containing the username in the table is Username, and the form has a control called Username.

Here's my code:

Private Sub SaveChanges_Click()
On Error GoTo Err_SaveChanges_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Dim mySQL As String
Dim con As Object

con = Application.CurrentProject.Connection

MsgBox "Username to modify is " & Me![Username]
mySQL = "UPDATE [Users] SET [Users].[UserModified] =" & Now()
mySQL = mySQL & " WHERE [Users].[Username]=" & Me![Username]

MsgBox "SQL command is:" & mySQL

DoCmd.SetWarnings True
DoCmd.RunSQL mySQL

'Close the connection
Set con = Nothing

Exit_SaveChanges_Click:
Exit Sub

Err_SaveChanges_Click:
MsgBox Err.Description
Resume Exit_SaveChanges_Click

Any idea what's wrong?

Thanks!!!

 

[Florida Doc]

bump

 

[dawtips]

well, you aren't actually using the 'con' variable anywhere.

you might want to think about creating an actual stored update query and calling that on a button press

 

[Florida Doc]

Thanks! As I'm sure you can tell, I'm new to VBA.

 

[craigmp]

If you still want to use VBA here is an update to your sql syntax for the first line.

mySQL = "UPDATE [Users] SET [Users].[UserModified] =# " & Now() & " #"

With access you need to put the date inside # # if your field in the table is defined as a date.