Discussion Locked

Pass parameter from Access form to query

0 helpful
7 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I want to know how to pass parameters retrieved from an Access form to a parameterized query, so as to have a viable user interface.

7 replies

by miss kitty

After you have defined the parameter on the form tab as a dialog, go into design view on the query and go to the field where you want to address the parameter dialog. In the criteria area enter the info. Below is an example of a dialog I made using the date dialog on the forms tab. My actual field name is [DATE] and is being brought in from a Visual Basic application. This is getting dates < or = the date field in my table.
<=[FORMS]![DATE Dialog]![DATE]
Hope this helps,
Cathy

by shounak

The technique u mentioned works for passing parameters to Access Reports. I want to try passing parameters to Access queries.

by miss kitty

It is the same way. In your Access Query,
code as follows:
[FORMS]![Sticket Vendor Dialog]![VENDORID]

then in VB:

' Execute Query
Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objRS As New ADODB.Recordset
Dim objParam As ADODB.Parameter
Dim count As Integer

'set up the database pointer
objConn.Open
"Provider=Microsoft.Jet.OLEDB.4.0;Persist
Security Info=False;Data
Source=E:\ACCESS\CONTRACT
HAULERS\HAULER.mdb;User ID=ADMIN"
objRS.CursorType = adOpenDynamic
objRS.LockType = adLockOptimistic

'setting the Active Connection for the
command object
objCmd.ActiveConnection = objConn

'Providing the query name as commandtext

'Providing the query name as commandtext
objCmd.CommandText = "[STICKET QUERY]"
objCmd.CommandType = adCmdStoredProc

'create the parameter and populate it
Set objParam =
objCmd.CreateParameter("@VENDORID",
adLongVarChar, adParamInput, 6, 0)
objCmd.Parameters.Append objParam

'Assign the values for the parameters
objCmd.Parameters("@VENDORID").Value =
CStr(txtVendor)

'Set the recordset to the command object
result
Set objRS = objCmd.Execute

This query is selecting all the tickets for a
certain vendor id.

Good Luck. If you need anymore help, email
me. I don't always get back to Tech to check
things.
Cathy
covcat@riverstonegrp.com

This discussion is closed to new replies.