How To Create An Access Vba Custom Menu

How To Create An Access Vba Custom Menu

Step by Step on how to create an ms access menu 

UDEMY SALE – IT Category Page-Dev & ITCategorySale-Discount:All Dev & IT Courses are $10, All others are $15-StartDate:12/14-EndDate; 12/23

In this article we will explain how to build a simple back and forth menu using Microsoft Access and its forms. A proper navigation menu is essential to any desktop software solution. The final users should have a central point they can use to direct themselves to the required working window. There are several possible approaches, using the Microsoft Office Ribbon, or if developing using Visual Studio, creating a proper MDI rich desktop solution. Being able to utilize VBA programming is essential. We will present a set of forms that will interact with each other and a main menu form to call their opening event. We will go step by step and show you how to create an access vba custom menu.

To create a simple access menu using VBA we will start by creating three forms and the underlying sample tables:

  1. i) frmMainMenu
  2. ii) frmClient

iii) frmSupplier

  1. iv) tblClient
  2. v) tblSupplier

As a side note, please pay attention to the naming convention, all use the suffix frm and tbl. Naming conventions are important when developing with any programming language. There is no dogma here, just define one and use it along all the development. If you lack imagination just google for examples.

These three forms will require two tables so we can have form controls bounded to data. We are assuming readers already have a basic Microsoft Access knowledge. The example tables could be similar to the ones presented in the next images (not much fields are required for this sample tutorial we present just some basic ones).

The next step will be the form creation. The easiest and fast approach will be the Microsoft Access wizard so the user should press the form creation wizard button as presented in the next picture

After that the user should select the required table (in this example tblClient) and press the next button. After that select the justified layout.

Press the next button, change form prefix to frm and hit finish. The desired user form will be presented with Microsoft Access defaults.

The reader should repeat the previous steps and create a second form to manage Suppliers’ data. The final form layout should be similar to the one presented below.

Each one of the previous forms will have its own back button so the user will be able to navigate to the main distribution menu window. The whole point is after-all to create a main access vba custom menu to organize our database.

Let’s add a back button to the frmClient form. For this

academic tutorial any position is fine but if the reader is looking for professional looking layouts, positioning, standard sizing, naming and layouts are very important details to consider.

To add a button the reader should put the form in design mode (right click on it and select design from the context menu)

When in design mode and from the top ribbon click the button control and then click on the desired form position (we will put it in the form’s header)

Once the button wizard initiates hit the cancel button. From this point onwards the properties will be defined manually. Regarding the created button please define its properties as follows. First set the button control name to cmdBack.

 

After that change its caption to Back

The final layout should be as follows

The reader is invited to execute the similar sequence to the Suppliers form. The steps are: put the form in design mode, select a button control, click on the form’s header to position it, cancel the wizard and manually set the Name and Caption properties accordingly. Later on, the proper button click events will be properly handled.

The Supplier form should look like

Now let’s create the initial startup menu. To create a blank new form go into the create menu and select blank new form

Next put the form in design mode and add three buttons, Clients, Suppliers and Exit

When adding each button cancel the button wizard and manually set the buttons’ name and caption properties. You should have a button called cmdClient with caption Clients, a button called cmdSupplier with a caption Suppliers and a button called cmdExit with a caption Exit.

Click the save button and name the form frmMenu

In the next steps we will be handling the required events for the created buttons and forms. We will start by the frmMenu form. The logic will be: when the user clicks the Clients button the actual frmMenu form will hide and the clients’ window will be shown. A similar logic will apply to the Suppliers’ window. This means no two windows will be visible at same time, to navigate from Suppliers to Clients the user must pass through the main menu window.

Let’s define the on click event for the Clients button. The reader should press the three dots on the event area and access the Visual Basic editor after selecting the option code editor from the prompted window.

For the on click event related to the cmdCLient button the following VBA code must be inserted.

Private Sub cmdClient_Click()

On Error GoTo cmdClient_Click_err: ‘Error handler label

Me.Visible = False‘Hides the menu form by setting visible property to false

DoCmd.OpenForm “frmClient”‘Opens the form associated to the clicked button

Exit Sub

cmdClient_Click_err: ‘Error handler code, in case an error happens, otherwise sequence flow will end in Exit Sub

Select Case Err.Number

Case Else‘Generic error handler that displays the error number and message

MsgBox Err.Number & ” – ” & Err.Description, vbCritical + vbOKOnly, “System Error …”

End Select

End Sub

The reader should have the code similar to

Pay attention to the presented error handler. This tutorial will not get in detail regarding them but it is a good programming practice to handle errors properly. This applies to any programming language. The code sequence is simply: hide the menu form and open the Clients form.

To show the menu form again the user must press the back button on clients’ form. Let’s create the required event on the Client’s back button. Another option to build the default on click event is to right click on the control and select the build event option from the context menu. Please check the image below

After selecting the pointed option insert the following code in the back button on click event

Private Sub cmdBack_Click()

On Error GoTo cmdBack_Click_err:

DoCmd.Close //Close Method

Form_frmMenu.Visible = True

Exit Sub

cmdBack_Click_err:

Select Case Err.Number

Case Else

MsgBox Err.Number & ” – ” & Err.Description, vbCritical + vbOKOnly, “System Error …”

End Select

End Sub

The final code should be similar to

This on click event will close the opened Clients’ window and will show the Menu form again.

So right now we have the VBA code that opens the Clients’ window from the Menu window and we created the VBA code that closes the Client window and opens the menu window again. The same logic must be applied to the Suppliers button in the menu window and to the back button in the Suppliers window. The code for the Suppliers button in the menu window is

Private Sub cmdSupplier_Click()

On Error GoTo cmdSupplier_Click_err:

Me.Visible = False

DoCmd.OpenForm “frmSupplier”

Exit Sub

cmdSupplier_Click_err:

Select Case Err.Number

Case Else

MsgBox Err.Number & ” – ” & Err.Description, vbCritical + vbOKOnly, “System Error …”

End Select

End Sub

The required code for the back button in the Suppliers form is

Private Sub cmdBack_Click()

On Error GoTo cmdBack_Click_err:

DoCmd.Close

Form_frmMenu.Visible = True

Exit Sub

cmdBack_Click_err:

Select Case Err.Number

Case Else

MsgBox Err.Number & ” – ” & Err.Description, vbCritical + vbOKOnly, “System Error …”

End Select

End Sub

The code is almost the same one should only change the form name and the method name.

The underlying logic is exactly the same and is easily extended to more forms.

To test the window workflow the reader should close all forms and open the menu form.

Clicking on the Clients button will close the open menu form and will show the Clients’ window.

Pressing the back button will close the Clients’ window and will take the user to the main menu window again.

Hitting the Suppliers button will work on a similar manner but it will open the Suppliers window.

To finalize the navigation logic we will handle the main menu Exit button on click event. This button will allow the user to quit the desktop Access database solution prompting the user if the application should really be terminated or not.

To implement this event right click on the Exit button located in the frmMainMenu and select the build event option -> code builder to access the VBA editor. You should be able to see the following event

The required VBA code to produce the desired outcome is the following

Private Sub cmdExit_Click()

On Error GoTo cmdExit_Click_err:

If MsgBox(“Do you really want to quit the application?”, vbYesNo + vbQuestion, “System question …”) = vbYes Then

DoCmd.Quit

End If

Exit Sub

cmdExit_Click_err:

Select Case Err.Number

Case Else

MsgBox Err.Number & ” – ” & Err.Description, vbCritical + vbOKOnly, “System Error …”

End Select

End Sub

The code presented above will ask the user if the application should indeed be closed. The sample use the MsgBox VBA native method that allows a large combination of configurations and layouts depending on the passed parameters.

Once the main menu form does not have controls bounded to any table it does not make sense to have the records selector active. To disable it just put the Menu form in design mode and change the property Record Selectors to No, this way the left arrow in the menu form will not appear.

Lastly the menu form should open when the Access database solution is executed. To define the startup form please go into the file tab

Then click Options

In the Current Database entry, select as Display Form the frmMenu form and it will start when the application opens.

We hope this tutorial helps the readers understanding Microsoft Access as this is just a very small sample of its capabilities. We have now created an access vba custom menu that we can be proud of! Microsoft Access is able to produce truly professional looking software solutions.

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *