Creative Connections

You are here Microsoft Access

Welcome to the Creative Connections Microsoft Access blog

Having been asked by two clients recently if we could design and build them bespoke MS Access databases, we thought now would be a good time to start a blog sharing all the nifty tips and tricks that we have employed, using built in MS Access functions, Macro Tools and VBA.

 

The databases that we recently built were for an Electric Bike Retailer and a Roller Skating and Events Venue in Southampton and included the following main capabilities:

  • Customer data capture 
  • Customer contact management
  • Sales & events quotations
  • Sales order processing and invoicing
  • Stock control
  • Customer data extraction for marketing (producing mailing lists and email list for use with email marketing tools like MailChimp etc...) Bike service history
  • One click emailing from individual customer records (allowing both custom and standard emails to be sent)

How to stop an Access Dialog Warning Box Popping Up and Replace it with a Custom Message Box

Thursday, 02 February 2012 14:12 Derek

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

How to add a new record to a Microsoft Access table using VBA

Wednesday, 01 February 2012 13:59 Derek

How to add a new record to a Microsoft Access table using VBA / Adding a record to an Access table based on user input

Applies to:  Access 2007, 2010 (untested in earlier versions)

The following tutorial will show you how to add a new record to an Access table when a user clicks a button using VBA and SQL.

For this tutorial you will need to create the following:

A table called “tblAccessories”, with a field called “accessoryName” (the field data type should be text.)

Note: Data types are very important when performing this type of operation so always ensure that you are working with the correct data type that you are trying to update.

A form called “frmAddAccessory”

A field on the form called “textbox1”

A  command button on the form called “btnAdd”

This example will demonstrate how to collect user input in an access text box on a form and save it to a table.

1. On the form you have created as per the above instructions, select the command button called “btnAdd”

2. In the tools section of the  “Design” tab click on the item called “Property Sheet” and select the “Event ” tab

3. On the “On Click” property click on the little black down arrow / triangle and select “Event Procedure”

4. Click on the three dots next to the little black down arrow / triangle - this will open up the VBA window and you should see the following:

Private Sub btnAdd_Click()

End Sub

5. On a new line directly between the two lines above add the following 

DoCmd.RunSQL "INSERT INTO tblAccessories (accessoryName) " _
 & "VALUES(" _
 & "'" & Me!textbox1 & "') "

Save your changes and test your form / report

Note:  This method is only used for adding a new record to / row to a table, I will cover updating an existing record in a future tutorial or This e-mail address is being protected from spambots. You need JavaScript enabled to view it for help.

More Articles...

Page 3 of 7