Pass a parameter to a parameter query from a data access page

[Matt]

How can I pass a parameter from a dropdown list in a data access page to a
parameter query?

I've seen this article,
http://support.microsoft.com/default.aspx?scid=kb;en-us;253976, but I can't
get it to work. Can someone please help??

 

[Ken Snell \(MVP\)]

See if this sample script helps; it shows how to add parameters to a stored
query and then run the query.
-----
Script to save a current record and run an action query that uses a value of
a control
on the DAP as a value in the query's SET clause:

----------------------------------------------------------------------------------

EXAMPLE USING AN ADODB.COMMAND OBJECT AND ADODB.RECORDSET OBJECT
(with two parameters in the stored query)
----

Dim comd
dim rst
dim par1
dim par2


Dim vbs_adParamInput
Dim vbs_adInteger
dim vbs_adCmdStoredProc
dim vbs_adCmdText
Dim vbs_adOpenDynamic
Dim vbs_adLockOptimistic
Dim vbs_adOpenForwardOnly
Dim vbs_adOpenKeyset
Dim vbs_adOpenStatic
Dim vbs_adLockReadOnly
Dim vbs_adLockPessimistic


vbs_adParamInput = 1
vbs_adInteger=3
vbs_adCmdStoredProc=4
vbs_adCmdText=1
vbs_adOpenDynamic=2
vbs_adLockOptimistic=3
vbs_adOpenForwardOnly=0
vbs_adOpenKeyset=1
vbs_adOpenStatic=3
vbs_adLockReadOnly=1
vbs_adLockPessimistic=2


MSODSC.DataPages(0).Save

Set comd = CreateObject("ADODB.Command")

Set comd.ActiveConnection = MSODSC.Connection
comd.CommandText = "qry_AddNullRecords_2_tblItemDetails"
comd.CommandType = vbs_adCmdStoredProc
comd.CommandTimeout = 15

' sets value of maintypeid field
Set par1 = CreateObject("ADODB.Parameter")
par1.Type = vbs_adInteger
par1.Size = 4
par1.Direction = vbs_adParamInput
par1.Value = 0

comd.Parameters.Append par1


' sets value of mainid field
Set par2 = CreateObject("ADODB.Parameter")
par2.Type = vbs_adInteger
par2.Size = 4
par2.Direction = vbs_adParamInput
par2.Value = wellid.value

comd.Parameters.Append par2


Set rst = comd.Execute


Set rst = Nothing
Set par1 = Nothing
Set par2 = Nothing
Set comd = Nothing

 

[Matt]

I know that I need to manipulate the two par1 and par2 variables, and I'm
pretty sure I need to make "qry_AddNullRecords_2_tblItemDetails" my query
name. Is there anything else I'm missing? Also, will this take care of my
DAP if the parameter I want to pass comes from a dropdown list? I was having
trouble trying to figure out the onchange stuff and trying to tie that in
with the onload of the page. I was running into many dead ends, and that
stupid parameter query pop-up dialog always came up. I want to be able to
load the page to either the first value of my dropdown list, or nothing to
start, and then be able to update the page with my stored queries when the
user changes the dropdown value. And I don't want that parameter query
pop-up to come up at all.


Thanks Ken

 

[Ken Snell \(MVP\)]

Yes, you would change "qry_AddNullRecords_2_tblItemDetails" to the actual
query name.

Sounds to me, from your description, that you want to pass a parameter to
the DAP's source recordset query based on a choice you make in a dropdown
element in this DAP itself? Perhaps you can explain in more detail what you
want to do.

It sounds like the DAP's source recordset query has a parameter in it? And
you want to give it a value when the DAP opens? Don't know that you can do
this. But you can remove the parameter from the query, let the DAP be bound
to the full query, and then filter the recordset query during the DAP's
opening event before data binding occurs, which essentially is the same as
"parameterizing" the query.

 

[Matt]

Thanks Ken,

That's exactly what I want to do. I want to "remove the parameter from the
query, let the DAP be bound to the full query, and then filter the recordset
query during the DAP's opening event before data binding occurs." The only
problem is that the query my DAP is based on, "HistoryQuery", is not the
query I want to plug the parameter into, "EmployeeQuery". I have a system of
queries set up where the first one, "EmployeeQuery", takes in the parameter,
10 more retrieve information based on that query's parameter, and then one
more, "HistoryQuery", takes the info from those 10 and combines it all. My
DAP is based off of that last query, "HistoryQuery", that combines all the
queries together and delivers me one record of info. I guess what I want to
know is how can I pass a parameter/variable to one query from the DAP, while
basing that DAP off another query that is linked to the original query.

 

[Ken Snell \(MVP\)]

Perhaps this sample database does what you seek?
http://www.cadellsoftware.org/SampleDBs.htm#FilterDAP

 

[Matt]

Thanks Ken,

I feel like I'm almost there. I've changed my query system so that the DAP
is based off of "HistoryQuery" and can also pass the parameter to that same
query. My pop-up thing works, I just keep running into the same problem.
When I try running the DAP, it gives me "Data provider failed while executing
a provider command." and then "Data type mismatch in criteria expression."
I've narrowed the problem down to this script:

<SCRIPT language=vbscript event=BeforeInitialBind(dscei) for=MSODSC>
<!--
dim EmployeeID

EmployeeID = window.dialogarguments
MsgBox EmployeeID
MSODSC.RecordsetDefs("HistoryQuery").serverfilter = "EmpID = " & EmployeeID

-->
</SCRIPT>

The MsgBox gives me the correct employee ID, so that means up until the
serverfilter thing everything works...I think. Here is "HistoryQuery" that
this DAP is based on:

SELECT AdditionalQuery.Additional, DevActionQuery.DevAction,
FRFCompQuery.FRFComp, FRFJobQuery.FRFJob, FRFLeaderQuery.FRFLeader,
FRFOrgQuery.FRFOrg, FRFOtherQuery.FRFOther, FRFPressureQuery.FRFPressure,
PossibleQuery.Possible, ReadinessQuery.Readiness, TimeFrameQuery.TimeFrame,
UpdaterQuery.Updater, WillingQuery.Willing, EmployeeQuery.*,
UpdaterQuery.EntryDate, EmployeeQuery.EmployeeID AS EmpID
FROM AdditionalQuery, DevActionQuery, EmployeeQuery, FRFCompQuery,
FRFJobQuery, FRFLeaderQuery, FRFOrgQuery, FRFOtherQuery, FRFPressureQuery,
PossibleQuery, ReadinessQuery, TimeFrameQuery, UpdaterQuery, WillingQuery;

EmployeeQuery.EmployeeID is what I need to filter so I gave it a name, "EmpID"

Can you see what I'm doing wrong?

Thanks Again Ken,
Matt

 

[Ken Snell \(MVP\)]

Is EmpID a numeric field or a text field?

 

[Matt]

It's a text, eventhough the input is a seven digit number like 1012777. Is
that bad? I can try changing the type to numeric if that would fix it.

 

[Ken Snell \(MVP\)]

No, that's ok, but you need to change the syntax to handle text; you need to
delimit the value from the EmployeeID variable with ' characters:

MSODSC.RecordsetDefs("HistoryQuery").serverfilter = "EmpID = '" & EmployeeID
& "'"

 

[Matt]

It Worked! Thanks Ken your the man.

No, that's ok, but you need to change the syntax to handle text; you need to
delimit the value from the EmployeeID variable with ' characters:

MSODSC.RecordsetDefs("HistoryQuery").serverfilter = "EmpID = '" & EmployeeID
& "'"