USING AN ACCESS DATA ENTRY FORM – QUERYING A DATABASE WITH SOME VBA

It’s during day three of an Access training course teaching keen ‘developer’ students the art to Using Microsoft Access VBA data entry form and immediately they realise creating such a form is simple enough to do but realising the inflexibility for the everyday use when end-users get to test and use this in anger.

What I’m talking about here is where we have a typical scenario of entering in new records (say order transactions) by first having to select either an existing customer or having to create a new customer.

How will the data entry form handle both situations without the need to split the two into separate forms mimicking each other?

Think of a layout to a form containing a sub-form which has a relationship between the customer and existing transactions. Normally, users would select a customer from a drop-down list and hey presto! the sub-form displays its related orders. 

A new customer will not appear in this drop-down list since it doesn’t yet exist but we need a mechanism to add ‘on the fly‘ new customers and then create a relationship to the new transactions.

We therefore need to ideally add some VBA code and introduce a calling method to ‘listen‘ for a certain event to happen allowing us the smooth workflow of loading a pop-up form to create our new customer and return back to the main form to continue and process as an existing customer.

Using An Access Data Entry Form With Some VBA – Some Simple Steps

1. First build your main and sub-form required which can be generated using either the built in Form wizard feature or in later version using the Access templates provided. The look and feel can of course be enhanced later on. 

Make sure that you have related tables or queries handling both the main and sub-form and that they are related via the child and master links.

2. In the main form, you want to add a ‘combo-box’ which allows the selection of filtered customers but ensure this drop-down control is not set as ‘Limit To List‘ via its properties.

We need the ability to type into this control for any new customers that are not listed here.

3. Now we need to add VBA code to the controls event called ‘On Not In List‘ which automatically triggers a pop-up form to allow the completion of the new customer profile before continuing.

This is a separate form which had been created prior to adding code and is a simple to build single Access data entry form screen.

Ideally, you may want to prompt the user first before continuing to load another form which enhances the end-users workflow and a better working experience. Here’s a sample piece of VBA code one could add to the ‘On Not In List‘ event for the drop-down control.

Dim intResponse As Integer

intResponse = MsgBox("The Customer you have entered " & _
"is not in the list. " & vbCrLf & _
"Do you want to add the new customer?", vbYesNo, & _
"Add New Customer?")

If intResponse = vbYes Then
'Open the new customer form to fill in other information

DoCmd.OpenForm FormName:="frmNewCustomer", _
WindowMode:=acDialog, DataMode:=acFormAdd, _
OpenArgs:=NewData

Response = acDataErrAdded

Else

Response = acDataErrContinue
Me.cboCustomer.Dropdown

End If

I’m not going to explain he above code here – drop me an email should you want to know more about on what is going on here!

There’s a little more editing and perhaps VBA coding needed here to refine the overall workflow but you can get a feel to how powerful Microsoft Access and in particular working with the Using Microsoft Access VBA  data entry form processes is enhancing and making a database really user friendly indeed.

To learn more about Access forms or any aspect to this application, why not consider my 6 eBook bundle which is one of my best sellers covering the principles of database design through to building quality forms and reports.

Tags: access data entry form, access forms and vba, how do I query an access database using vba, using access forms, Using Microsoft Access vba, using vba in your database