Ms Access Tip – How to Use Variables in the Access Query Design Form
MS Access Tip – How to use variables in the Access query design form.
In this example you are going to learn how to enter or select data in one form and open another form with more data related to the field you just selected by taking advantage of global variables in the query design form. To help achieve this, you will use a global variable entered in the criteria of a field while creating the query in the Access query design form. The result is a powerful user interface function that allows a user to look up more data, or enter more data related to a selected record. Users can use it to look up pricing histories, addresses, inventory locations, really anything where you would have more data about a key field in your database.
You can achieve this by learning to use global variables. These variables can be accessed by any form, query, macro, or other modules while you are in the Access session. Use a module to store these variables in.
There are several simple steps to achieve this functionality.
Create a global variable in a module
Create your data table.
Create the source query for the “look up” or pop up form
Create the pop up form with the source query as the data source
Create the form to enter the selection from and enter the VBA code to the pertinent control events on the form to open the look up form.
1) Create a module with the following few lines of code.
Option Compare Database
Public DFname As String
Public Function GetVFname () As String
GetVFname = VFname
End Function
2) Create a table with the following fields for demonstration sake. ID as an autonumber. Then create Fname, Lname , Street, City, St, Zip, County, Phone as text fields and lastly add DOB as a date field.
Set the form to be a pop up form. While in design mode, make sure the form is selected and then on the “Other” tab turn the Pop Up setting to yes. Save the table naming it Client.
3) Next create a query to use as the data source for your form. Use the Client table as the source and select all the fields from the table field list using the Windows shift select feature and drag them down to the field view portion of the Query by Example form. Now in the ID field, set the Criteria as:
GetVFname ()
Close the query and save it as IDQry
4) Create a form using the query you just created as the data source. So, on the Data tab on the form select IDQry. Just put all the fields on the form for this example. Save it as ClientData.
5) Create a second form and use the client table as the data source. This form is used to demonstrate selecting a record with a double click event and getting the pop up form with more information related to the selected record.
In a real application you may want to access data from other tables/forms in the same way. Pull in the Id, Fname and Lname fields only. Close and save the form as NameLst.
Put the following code on the double click event of the Id field. This will open the “ClientData” form with the selected Client.
Private Sub ID_DblClick(Cancel As Integer)
VFname = Me.ID.Value
DoCmd.OpenForm “ClientData”, acNormal
End Sub
Now to see this example work, simply open the NameLst form and double click on the ID field in any record.
This example was created with Access 2002. There are more than likely many different ways to accomplish the same functionality, but we have found that this general approach is the most scalable to larger applications.
(ArticlesBase SC #290713)
Article Tags:
ms access
,Database Help
,Access Examples
,Conditional Formatting
,Color Coding
,Continous Forms
How to use Microsoft Access - Using Data Forms - part 9 of 13
How to use Microsoft Access - Using Data Forms - part 9 of 13 in this educational video from dizzo95. (03:31)
How to Link Data in Microsoft access
Wondering why Access won't display your data? It's probably because a control on the form isn't bound to a data source. (03:59)
How to use Microsoft Access - Creating Queries and Reports - part 11
How to use Microsoft Access - Creating Queries and Reports - part 11 in this educational video from dizzo95. (05:33)
Small Business Advice - How to Gather Forms
Monica Baker of the IRS discusses where to get the proper small business tax forms from the IRS small business website. (00:59)
How-to make a Body Form, Threadbanger Projects
Need a body form but dont have the cash for one ? Well this week Threadbanger Projects shows you how to make a replica of yourself using your old scraps,some packaging tape and an old christmas tree.You'll have to see it to believe it ! (06:53)
Color coding adds value to a form as it easily directs the user's eye to critical data. This is a powerful technique that allows a user to quickly evaluate a pages of data for errors or warning information.
An event planner is someone who plans and makes the arrangements for special occasions such as weddings, birthday party and retirement parties, charity fundraisers, civic functions such as parades, and corporate conventions.
Aarkstore announce a new report "Hunt Consolidated Inc. - SWOT Analysis" through its vast collection of market reserach report.
Aarkstore announce a new report "Sun Life Financial Inc. - SWOT Analysis" through its vast collection of market reserach report.
Aarkstore announce a new report "Schawk Inc - SWOT Analysis" through its vast collection of market reserach report.
You might be a small business with one or two vehicles or a company with a large fleet either way keeping fuel receipts and filling out expense claims takes up valuable time and resources. Having a business fuel card account will help you manage your fuel costs, keep track of your fleet and simplify your management procedures.
The content may vary from being in various forms like images, files, audio files, electronic documents and web content. It is observed that Content Management System helps in controlling of a large body of content which in turn is very beneficial for the organizations.
Meeting and seminars are vital events for every company that provide boost to the company growth and success. Organizing meeting and seminars is not an easy job and requires service of event Management Company that proficiently handles events like meeting and seminars.
Today everything has become so planned and organized that even while organizing an event one loves to organize in a planned manner. Planning and organizing an event is not an easy task and one requires advice and assistance of experts like event planner companies.
This example demonstrates using an array and SQL statements to create the similar records. It also has a function to replace records if you want to change the amount value and update the data table. The replace function assumes that the description and date are the same and replaces every occurrence of the existing record with the latest amount value. For safekeeping the replace function makes a copy of the data table before executing.
In this example you are going to learn how to enter or select data in one form and open another form with more data related to the field you just selected by taking advantage of global variables in the query design form
Color coding adds value to a form as it easily directs the user's eye to critical data. This is a powerful technique that allows a user to quickly evaluate a pages of data for errors or warning information.
There are so many people telling you that you can make such a huge return on your investment these days that you just can't believe any of them any longer. I know, I have received so many get rich emails that I should be earning about a billion a day now! How about investing in something that you really believe in. Yourself.
Database data entry can be tedious at times. You can make your databases more user friendly if they have a way to select data to enter rather than typing in the same data over and over. Access provides a form control called a combo box to help with this task but it can lead to trouble.
Many times it is necessary to provide your users with an easy way to filter or search for data in large table of information. This tip will show you one quick and easy way to solve the problem by giving the user a field to enter a search string and updating the form with those fields that fit the search pattern. It's quick and easy, so give it a try. Read about how Access was a big success solving a license tracking issue in this article.
When you have a list box it is handy to be able to multiselect to perform some action on multiple items in the listbox. Here is a simple set of instructions to get you started. These instructions will create a quick way to multiselect and delete rows from a table.
At times you will find it very handy to know what a user was doing in your application at a specific time. This is especially useful with multi-user database applications for debugging or security reasons. With this tool implemented in your applications you will be able to see what event happened and at what time.
0