MS Access VBA to run an update query after clicking a form's button
Hello Experts,
I am new to Access VBA programming. I am trying to build a code that reads the value in one of my form's text box and update a table (which has only one row of data/recordset). I tried various ways but I am getting error (I have attached the screenshot below). I have attached below the code I am using. I am using Access 2003. Please help.
Kindly let me know if you have any questions/concerns.
Thank you so much.
I am new to Access VBA programming. I am trying to build a code that reads the value in one of my form's text box and update a table (which has only one row of data/recordset). I tried various ways but I am getting error (I have attached the screenshot below). I have attached below the code I am using. I am using Access 2003. Please help.
Kindly let me know if you have any questions/concerns.
Thank you so much.
Private Sub CmdSetValues_Click()
Dim AsOfDate As Date
Dim strSQL As String
AsOfDate = Me.txtAsOfDate
strSQL = "UPDATE tblAsOfDate SET tblAsOfDate.[As of Date] = #" & AsOfDate & "#"
CurrentDb.Execute (strSQL)
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am attaching below the screenshot of my form design.
untitled.JPG
untitled.JPG
what format is the date entered in the textbox ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No, since i was testing this form so ensured that I was entering a valid date in the txtAsOfDate box (at one point of time I also experimented with CDate function but was getting the same error). Also the table tblAsOfDate contains only one record set (which I am using to pass arguments to other queries) so I didn't used the where clause.
no worries. what format do you enter the date in?
ASKER
Thank you so much rockiroads for your prompt response. I was able to fix the problem. I just made a new form and used the same old code. I think I made some error while setting the property of the form which was leading me to this problem. Actually I was trying to put the form on full screen mode, remove min-max buttons, navigator buttons, etc. May be I did something wrong somewhere. But the good news is that my new form is working fine.
I really appreciate all your help.
Thanks
Shashank
I really appreciate all your help.
Thanks
Shashank
Sometimes this kind of corruption occurs. Perhaps next time, just try creating another control first before going the whole way with the form. Other thing possibly is when entering name via the properties, you may of inadvertently entered a non alphanumeric character
Using the intellisense menu is handy as when you do Me followed by dot (Me.) it lists all the properties and methods available to you.
Glad you got it going though, good luck with the rest of your project.
Using the intellisense menu is handy as when you do Me followed by dot (Me.) it lists all the properties and methods available to you.
Glad you got it going though, good luck with the rest of your project.
ASKER
After that I tried using my form but I am facing the same problem again. Can you please tell me if the code I have written is correct?
Thanks
Shashank