Query Data with a User Input Form
Let’s say you have multiple users within access that you need to let query specific data, but not edit it. In a sense, you want them to see data that’s only important to them, learn from it, and leave it untouched.
A form that allows users to input criteria for their search, and passes that criteria to a query may be an easy solution to your problem. Let’s learn to Query Data with a User Input Form.
What We’ll cover:
In this example, I’ll cover a few things. I’ll cover how to:
- Set up a form that allows users to input a criteria
- Use the form input by a user to specify the returned data of a query.
- VBA code of how to create the on click event of the form button to generate this query for the user automatically.
Real Life Example
So let’s get started. I’ll give a brief overview of the project I’m working on to catch you up to speed on what I’m doing.
In my example, I was asked to make a way for employees to sign in to a Microsoft access database and return information about defects that happened in their manufacturing area. So, I needed to create a form that verified the user, query a table for only that user’s information, and give them the ability to see only the defects that they’re interested in.
They don’t need to waste time looking at every defect that they’ve ever had in the plant – this would be a waste of time. But perhaps, maybe all the defects within a given month or year.
They also don’t need access to their co-workers data within the table. This way rivalry and blaming each other for not receiving production based bonuses doesn’t occur within the plant.
Let’s Get Started.
1st – In order to query data with criteria from a form, you’re going to have access to the data your trying to query, and also a form which will allow users to input the data.
All the data in my example was input into an excel document by our quality department. This works out perfectly for this application, as I don’t want users to be able to edit my tables in my database.
Whenever you sync an excel worksheet to an access database, the excel table is still editable, but the access table that is created from the sync isn’t. Exactly what I wanted to happen.
The Form I created is found above. It’s simple enough, a Clock Number of the employee and the password the user will use to verify themselves.
In this example, I’m creating a simple log on for users. The information I want to pass to my query is the Clock Number. That way whenever the user submits their clock number and their password, after verification, it returns information from a table only about the given user.
2nd – We need to create the Query on the Table
It may be difficult to view the criteria fields in the picture above. But don’t worry bout those right now. We just need to have the query set up with the parameters we want. The above lists all fields I want my query to retrieve from the table named ‘DATA’.
Relating the Query Set to the Form
Since I’m relating the query set to the form, I want the criteria of a field to be that of one in which I collect on the form the user fills out. In this scenario, I want to return information only of the user from the table ‘DATA’.
I’ve done this by inputting [Forms]![LoginForm].[txtUserName] as the criteria of the field for the query
This is saying: Under my Forms, Check the login form for the field ‘txtUserName’ – Note that the field on the form must be spelled exactly this way, or else Access will not recognize it as the field you’re requested. In this instance, Access will prompt you to enter the field manually whenever you submit the form.
You’ll also notice I have input criteria for the date. In my specific example, I want users, once verified, to be able to tell the query between what dates to pull their given information. I’m going to use these in a different fashion however that I’ll cover a bit below.
The criteria I entered in these blanks were:
Between [Enter the Start Date] and [Enter the End Date]
Relating the Form to the Query Set
To make the form connect with the query, we need to assign the field that we want to interact with the query the same name.
‘[Forms]![LoginForm].[txtUserName]’ was the criteria I put into the field for the clock number under the query.
To get this to work, my form must be ‘LoginForm’
And the field I want it to be related to on the form is the clock number field.
To connect it, we must alter the default name of the field to the one we’ve chosen. Enter Design view, and then change the field name to ‘txtUserName’
The Property Sheet will be to the right of the form in design view, and we’ll have to change the name to look something along the lines of this:
The Relationship is formed – but how do we run the query from the form?
Once we have these, we’ve built the relationship of the form and the query. But how do we get the form and query to interact with each other? I’ve done it with VBA code that’s connected to me login button.
Enter design view again, right click on the button of your form, and then click build event – the first option of the right click menu. Once clicked, you should be at a screen ready for VBA input. This is known as the code builder.
(Note, you can use the macro builder here and all will be well. In fact, it’s encouraged by Microsoft to do it that way for security reasons. However, I just like writing code even though I’m no good at it. )
The VBA Code
At this point, you will see an on click function for your button as [whatever your button’s name]_Click()
I named my button as ‘btnLogin’ so the function for the on click event of this button will be btnLogin_Click()
Now, to run the query, you’ll need the following code:
DoCmd.OpenQuery “ClockDefectRun”, , acReadOnly
Me.Visible = False
What this is saying, is to:
Do command: Open The Query called “ClockDefectRun” in a read only mode. The query I created for this example is in fact called “ClockDefectRun”
Take the currently active item, (Me) – the form currently being used – and hide it.
*This is an important step. You only need to hide the form at this point, not close it. Access will not allow you to query from a form that has been closed. Therefore, if you close the form you will receive the prompt of inputting the field required for the query.
My form and VBA had the additional step of validating the user – but that isn’t really the purpose of this guide. IT’s just to pass the form input to the query set as a criteria. Once you press the button the following events should happen:
- The Clock Number will pass to the query to only access the records that fit that criteria.
- Since we didn’t create Form inputs for the date Criteria, We will be prompted to enter this once the button is clicked.
Let’s set what happens:
Input of Data into Forms
After the login button is pressed, We are asked to enter a Start Date and an End Date – like I stated before, this is because we didn’t enter these parameters into our form. However – note that we weren’t asked the ‘txtUserName’ field. This was the key parameter we wanted to take from the form and pass to the Query.
Just like how we wanted, it only pulled records from the given clock number – that it pulled from the form. And then it only pulled the forms that were within the data range that we entered upon the prompt.
And that’s it! We covered how to connect a query to a form input and use that as a criteria for the query to run. This tutorial could of been explained in a lot fewer lines, but it took me a while to figure this out myself. So I wanted to write a very thorough tutorial for those who need it.
If you have any questions feel free to leave a comment below!