Microsoft Access Programming: Learn How to Use The VBA InputBox Function
Let’s get some prospective here regarding the VBA Inputbox function by first identifying if there is a real need to use this function at all considering we can build your own from scratch and take full control by using MS Access forms – right?
This is true but when using VBA and learning about Microsoft Access programming, procedures which may require some parameter value to be passed internally or as part of the workflow and rely on an outside form (as user’s can build and define) this could be ‘clunky’ to use.
The inputbox function can be used in a module (using VBA code), called in a macro in an expression or simulated as a custom built form. But in this article I want to start the more traditional VBA approach.
You will need to start by being in the VBA programming interface known as the VB editor and create a new module and add the following code:
Sub MyFunctionBox() Dim strName As String 'Declare a variable 'Call the VBA InputBox function... strName = InputBox("What is your name?", "VBA InputBox Function") 'Terminate if the cancel or empty value is found button is clicked! If strName = Empty Then Exit Sub 'If not empty, then welcome the person in a message box... MsgBox "Hello " & strName & " and welcome to VBA programming!", vbInformation End Sub
You may need to take a quick peak at the components of the Visual Basic editor interface if you are not familiar with this environment too.
You can run the above VBA code and see how it will look by placing the cursor anywhere in between the opening and closing signatures (sub…end sub) and press the F5 function key (which executes the procedure).
Now the above example doesn’t really do it any justice or have any real added value to your Access database but in isolation it explains how Microsoft Access programming is really easy to create and call and in this example, it passes a value via variable (called strName) which is then displayed in a message box statement (another pre-defined VBA call).
Notice the one line of VBA code to test if the user either pressed the ‘Cancel’ button or left the field empty which is a logical test and terminates the procedure early. I call this a one-liner IF and is required to gracefully handle the emptiness of the VBA Inputbox function.
Microsoft Access Programming: VBA InputBox Function Can Be Used With Other Objects
So where do you really use this type of objects in your database? There are several ways to call a procedure into other objects too:
- As a function procedure call using the Function…End Function block which returns a value.
- Once it is a public function, you can pretty much use it anywhere as if it were a standard Access function and therefore a query would benefit from this as an alternative parameter query call.
- It could be wrapped into a macro object instead of trying to create a similar function directly in a macro which can sometimes being a little inflexible depending on which version of MS Access you use.
Here’s an example which extends the level of Microsoft Access programming at little further using a function call directly into a query as an alternative way to add a parameter.
This example function can be called into one or more queries or even into a form or a report and of course, the VBA code can be extended as you add more data sets having just one place to manage modular based functions.
If you want to know more about how to program with the application, I do offer online coaching chargeable by the hour and provide on-going support too