Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Automatically Entering a Parameter Value In Access

Posted on 2007-12-04
5
Medium Priority
?
2,358 Views
Last Modified: 2013-11-27
I have a query that when you run it you get an Enter Parameter Value window.  I want to run this query along with several others in a macro and I don't want to have to enter this value every time I run the macro.  Is  there a way to automatically populate the value as the macro runs the queries?  Please advise and thank you very much.

Steve
0
Comment
Question by:submarinerssbn731
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 20404058
If you are always going to use the same value then chnage the query to use that value instead of using a parameter field.
0
 

Expert Comment

by:LouieGee
ID: 20404310
There are some things to note:
1) The use of the "PARAMETERS" keyword in the query itself
2) You can get a count of a query's parameter's by referencing the parameters.count property
3) You can refer to a query's parameters as a collection(by name) or index(by number)

Public Sub PQry()
Dim DB As Database
Dim Q As QueryDef
Dim I%
Set DB = CurrentDb
Set Q = DB.QueryDefs("pquery")
Debug.Print Q.Parameters.Count

For I = 0 To Q.Parameters.Count - 1
Debug.Print Q.Parameters(I).Name, "'"; Q.Parameters(I).Value; "'"
Q.Parameters(I).Value = "10101"
Debug.Print Q.Parameters(I).Name, "'"; Q.Parameters(I).Value; "'"

Next
End Sub




The code snippet below was taken from the Access 97 help file on PARAMETERS:


This example requires the user to provide a job title and then uses that job title as the criteria for the query.
This example calls the EnumFields procedure, which you can find in the SELECT statement example.
 
Sub ParametersX()
 
	Dim dbs As Database, qdf As QueryDef
	Dim rst As Recordset
	Dim strSql As String, strParm As String
	Dim strMessage As String
	Dim intCommand As Integer
	
	' Modify this line to include the path to Northwind
	' on your computer.
	Set dbs = OpenDatabase("NorthWind.mdb")
	
	' Define the parameters clause.
	strParm = "PARAMETERS [Employee Title] TEXT; "
 
	' Define an SQL statement with the parameters
	' clause.
	strSql = strParm & "SELECT LastName, FirstName, " _
 
& "EmployeeID " _
		& "FROM Employees " _
		& "WHERE Title =[Employee Title];"
	
	' Create a QueryDef object based on the 
	' SQL statement.
	Set qdf = dbs.CreateQueryDef _
		("Find Employees", strSql)
	
	Do While True
		strMessage = "Find Employees by Job " _
			& "title:" & Chr(13) _
			& "  Choose Job Title:" & Chr(13) _
			& "   1 - Sales Manager" & Chr(13) _
			& "   2 - Sales Representative" & Chr(13) _
			& "   3 - Inside Sales Coordinator"
 
intCommand = Val(InputBox(strMessage))
		
		Select Case intCommand
			Case 1
				qdf("Employee Title") = _
					"Sales Manager"
			Case 2
				qdf("Employee Title") = _
					"Sales Representative"
			Case 3
				qdf("Employee Title") = _
					"Inside Sales Coordinator"
			Case Else
				Exit Do
		End Select
		
		' Create a temporary snapshot-type Recordset.
		Set rst = qdf.OpenRecordset(dbOpenSnapshot)
 
' Populate the Recordset.
		rst.MoveLast
			
	' Call EnumFields to print the contents of the 
	' Recordset. Pass the Recordset object and desired
	' field width.
		EnumFields rst, 12
	Loop
	
	' Delete the QueryDef because this is a
	' demonstration.
	dbs.QueryDefs.Delete "Find Employees"
	
	dbs.Close
 
End Sub

Open in new window

0
 

Accepted Solution

by:
LouieGee earned 200 total points
ID: 20404619
Oops, you said macro not VB, I spoke too soon

You could create a form with a text box and have the query reference that text box.
The text box can be bound to a table if you want to keep the same criteria for a while (days, weeks, months)
 or the text box can be unbound if you want the criteria to only be stored for the limited time that the form is open.

You would create a form with a text box on it and name the text box something appropriate like maybe "Text0"
In your parameter query, put a reference to that form's text box instead of the parameter.
That would look something like this: [forms]![formname]![text0]
Whenever you ran the query it would look for the textbox on the form and filter your query with that.
You could use multiple text boxes to filter multiple fields if you wanted.
This works well if you have multiple queries that filter on the same criteria.
A little complicated but, this will work.
0
 

Author Closing Comment

by:submarinerssbn731
ID: 31412608
This worked.  Thanks!
0
 

Expert Comment

by:detmers
ID: 26010383
I had this same need... I wanted to run 4 queries, each with the same parameter.  I ended up doing the following:

1.  Created a form to get the parameter
- I named the form SelectForm
- in my case, I wanted to select a field from an existing table, so I used a combo box that selected the field I wanted to pass.
- I named the field SelectParameter

2.  Modified the queries I ran to accept the value from the form I just created.
- in criteria for each query, I put =[Forms]![SelectForm]![SelectParameter]

3.  On the SelectForm form I created, I put a command button to run each of the queries.

4.  I can now run the form, select the value I want, and it runs all 4 queries.  You can add some fancier stuff to turn off the messages and look for errors, but I was happy just to get this far.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question