APPLIES TO: ACCESS 2007, 2010 (UNTESTED IN EARLIER VERSIONS)
(This post should be read in conjunction with my last post)
Following on from yesterdays blog item on how to use VBA and SQL to add a new record to a MS Access database table, this tutorial will show you a couple of tricks to tidy up the operation.
If you completed the tutorial in my last post, you will have noticed that when you click the button to add your item to the table, the Access dialog box shown below pops up:
Whilst this can be a useful safeguard to prevent the accidental addition of records or a good way of knowing that the record has been added, it may also confuse end users.
You can very easily stop this message from showing by adding the following code immediately before the code that you used to add the record to the database table (see my last post):
DoCmd.SetWarnings False
If you want to go one step further and add your own dialog box that tells the user that the update has been successful, you can add the following line of code immediately after the code used to add the record to the database table (again see my last post):
MsgBox “Update successful”
And finally for the really brave, if you wanted the dialog box to include the value that was being updated in the dialog text, you could do the following:
1. Immediately before the code that you use to add the record to the database table add these lines of code:
Dim strMyText As String
strMyText = Me.textbox1
2. then after the code used to add the record to the database table add:
MsgBox strMyText & ” was added to the database”
The full code (including the sql to add the record to the database) would be:
Private Sub btnAdd_Click()
Dim strMyText As String
strMyText = Me.textbox1
DoCmd.SetWarnings False
DoCmd.RunSQL “INSERT INTO tblAccessories (accessoryName) ” _
& “VALUES(” _
& “‘” & Me!textbox1 & “‘) “
MsgBox strMyText & ” was added to the database”
End Sub