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 add a new record to a Microsoft Access table using VBA / Adding a record to an Access table based on user input

Wednesday, 01 February 2012 13:59 Derek

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.

How to Lock a text box on an Access form / How to unlock a text box on an Access Form

Tuesday, 31 January 2012 12:38 Derek

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

The following tutorial shows you how you can use VBA to lock or unlock a text box on an access form following the update of another text box or after the click of a button.

For this tutorial you will need to create the following fields /controls on your form:

A text box called “textbox1” (the box that will be updated)

A text box called “textbox2” (the box that will be locked)

A command button Called “btnLocked” 

1. Select the text box called “textbox1”

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

3. On the “After Update” 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 textbox1_AfterUpdate() 

End Sub 

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

Me.textbox2.locked  = True

6. Go back to your form and select the command button called “btnLocked”

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

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

9. 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 btnLocked_Click()

End Sub

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

Me.textbox2.Locked = False

Save your changes and test your form / report

Note:  The second text box will only be locked once the user has finished entering data and clicked away from the field.

More Articles...

Page 1 of 5

  • «
  •  Start 
  •  Prev 
  •  1 
  •  2 
  •  3 
  •  4 
  •  5 
  •  Next 
  •  End 
  • »