[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

MS Access VBA to run an update query after clicking a form's button

Posted on 2009-05-20
9
Medium Priority
?
1,618 Views
Last Modified: 2013-11-27
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.
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

Open in new window

untitled.bmp
0
Comment
Question by:sstampf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 24432750
In the vba editor, click on Debug menu option then select Compile. If there are any code problems, it will get highlighted. It is also worth checking for missing references, so again from vba editor, click on Tools then References. if any are listed as missing, the word MISSING will appear towards the top
0
 
LVL 12

Author Comment

by:sstampf
ID: 24432932
Thanks for your quick response rockiroads, I tried both the options you mentioned above. There are no missing references and after compiling I didn't got any error.
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
0
 
LVL 12

Author Comment

by:sstampf
ID: 24432972
I am attaching below the screenshot of my form design.
untitled.JPG
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 65

Expert Comment

by:rockiroads
ID: 24433101
what format is the date entered in the textbox ?
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 2000 total points
ID: 24433109
Do note the sql you have will update all records because there is no where clause specified

Nothing wrong with your code that I can see, only thing might be the date entry
0
 
LVL 12

Author Comment

by:sstampf
ID: 24433303
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.
 
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24435631
no worries. what format do you enter the date in?
0
 
LVL 12

Author Comment

by:sstampf
ID: 24439419
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24440200
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.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question