Solved: Using Access Form to Enter Parameter Value

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

willydigger

Thread Starter
Joined
Jul 24, 2003
Messages
112
I want to build a form that has several buttons.
Each button will hold a parameter value for a query.
When the user clicks on the button it will open the query and automatically provide the parameter data so that the query only opens with the select data.

For example, there are three buttons (1,2,3). When you click the "1" Button, it will open the query and enter "1" into the parameter so that the query opens with all data that fits the "1" criteria.

Can someone give me an example of the proper code?
 
Joined
Aug 5, 2005
Messages
3,086
It sounds like you're taking an odd approach. Have a gander at this FAQ from AccessVBA and see if that doesn't give you some ideas.

chris.
 

OBP

Joined
Mar 8, 2005
Messages
19,896
Chris, this is not actualy an odd approach at all as it can be used for multiple searching and Wild card searching.
willydigger, do not use the Form to pass parameters as such, use it as the basis of the criteria row of the query. Place a code like this in the Criteria row of your query -
Like "*" & [Forms]![Form Name]![Field Name] & "*"
where form Name is the name of your form and Field name is the name of your form which has a field on it containing what you want to search for, it can be a hidden field.
This code will find any record that has the data in the form's field anywhere within the Tables field ie pattern match. If you want it to only match exact data use this instead -
[Forms]![Form Name]![Field Name]
 
Joined
Aug 5, 2005
Messages
3,086
Originally posted by OBP:

do not use the Form to pass parameters as such
That's what I meant by "odd." If you're going to use a custom form, use textbox controls rather than parameter popups.

I meant the description of the proposed solution was unusual, not the requirement.

chris.
 

willydigger

Thread Starter
Joined
Jul 24, 2003
Messages
112
OBP said:
Chris, this is not actualy an odd approach at all as it can be used for multiple searching and Wild card searching.
willydigger, do not use the Form to pass parameters as such, use it as the basis of the criteria row of the query. Place a code like this in the Criteria row of your query -
Like "*" & [Forms]![Form Name]![Field Name] & "*"
where form Name is the name of your form and Field name is the name of your form which has a field on it containing what you want to search for, it can be a hidden field.
This code will find any record that has the data in the form's field anywhere within the Tables field ie pattern match. If you want it to only match exact data use this instead -
[Forms]![Form Name]![Field Name]
So what will the code look like in the button code? This is the standard code to open the query (a report of a query in this case):
Code:
Private Sub btnViewReport_Click()
    Dim stDocName As String

    stDocName = "Equipment Query"
    DoCmd.OpenReport stDocName, acPreview

End Sub
 
Joined
Aug 5, 2005
Messages
3,086
willydigger, did you read the FAQ I linked? It has code examples of what you need to do.

For now, you can use the WhereCondition argument of the OpenReport method to specify a criterion or criteria at run-time. For instance, if you wanted to generate a report of all the customers in a given state, and if on your form you had a textbox control named txtState that accepted valid state abbreviations, you could do something like this:

Code:
Private Sub cmdGetReport_Click()
  DoCmd.OpenReport "rptCustomersByState", _
                   acPreview, _
                   , _ ' necessary so required 3rd argument is not omitted
                   "[StateField] = '" & Me.txtState & "'"
End Sub
That's an extremely limited example. There are more--and better--examples in the FAQ, including a demo search routine that you can modify for use in your database.

chris.

[edit]
Originally posted by OBP:

Sorry Chris, I misunderstood.
No worries whatsoever (y)
[/edit]
 

willydigger

Thread Starter
Joined
Jul 24, 2003
Messages
112
Thanks for the help guys. I created a listbox and had the query criteria reference the listbox like OBP suggested.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top