Databound list box populated with parameterized query?

[Mark]

Hello.

I am developing an Access 2000 form, and I want to populate a
databound list box using a saved query that requires parameters.
Is there any way to do this? I tried creating a DAO querydef
object and setting the parameters, and then assigned the querydef
object to the list box "rowsource" property. This did not work.

Thanks
-Mark

[deko]

Re: Databound list box populated with parameterized query?

> I am developing an Access 2000 form, and I want to populate a[color=blue]
> databound list box using a saved query that requires parameters.
> Is there any way to do this? I tried creating a DAO querydef
> object and setting the parameters, and then assigned the querydef
> object to the list box "rowsource" property. This did not work.[/color]

I use a function to pass parameters to queries most of the time. If I need
to pass a file path or some other string that may contain quotes or
potential problem characters, then I use a parameter query.

If you can get the value needed from a control, or put it in a hidden
textbox on a form, you can use this function in the query.

example:

SELECT * FROM qryMyQuery WHERE MyField_ID = QryPrm("frmMain ", "txtMyId")

Public Function QryPrm(ByVal strFrm As String, ByVal strCtl As String, _
Optional ByVal strSubFrm As String, Optional ByVal strSubFrmCtl As
String) _
As Variant
If Len(strSubFrmCt l) = 0 Then
If Len(strSubFrm) = 0 Then
QryPrm = Forms(strFrm).C ontrols(strCtl)
Else
QryPrm = Forms(strFrm).C ontrols(strCtl) .Form.Controls( strSubFrm)
End If
Else
QryPrm =
Forms(strFrm).C ontrols(strCtl) .Form.Controls( strSubFrm).Form .Controls(strSu bFrmCtl)
End If
End Function

[Mark]

Re: Databound list box populated with parameterized query?

"deko" <deko@nospam.co m> wrote:[color=blue]
>
> If you can get the value needed from a control, or put it in a hidden
> textbox on a form, you can use this function in the query.
>
> example:
>
> SELECT * FROM qryMyQuery WHERE MyField_ID = QryPrm("frmMain ", "txtMyId")[/color]

That works, thank you very much.

I find it odd that Access does not support a syntax for executing queries
with parameters,
without resorting to object references for the parameters. But as long as
there is a way to
do it, I guess I won't complain too much.

-Mark

[Mark]

Re: Databound list box populated with parameterized query?

"deko" <deko@nospam.co m> wrote:[color=blue]
>
> If you can get the value needed from a control, or put it in a hidden
> textbox on a form, you can use this function in the query.
>
> example:
>
> SELECT * FROM qryMyQuery WHERE MyField_ID = QryPrm("frmMain ", "txtMyId")[/color]

That works, thank you very much.

I find it odd that Access does not support a syntax for executing queries
with parameters,
without resorting to object references for the parameters. But as long as
there is a way to
do it, I guess I won't complain too much.

-Mark

[cjb_kjb]

Re: Databound list box populated with parameterized query?

Maybe I'm missing something here ...but

I cant see that using the function in this case is any different than
coding:

SELECT * FROM qryMyQuery WHERE MyField_ID = forms!frmMain!t xtMyId

seeing you have the form and the control hard coded as parameters in
the query anyway.

It would be nice if the form and control names could be provided at run
time but I can't see a simple way of doing this.

Mark wrote:[color=blue]
> "deko" <deko@nospam.co m> wrote:[color=green]
> >
> > If you can get the value needed from a control, or put it in a hidden
> > textbox on a form, you can use this function in the query.
> >
> > example:
> >
> > SELECT * FROM qryMyQuery WHERE MyField_ID = QryPrm("frmMain ", "txtMyId")[/color]
>
> That works, thank you very much.
>
> I find it odd that Access does not support a syntax for executing queries
> with parameters,
> without resorting to object references for the parameters. But as long as
> there is a way to
> do it, I guess I won't complain too much.
>
> -Mark[/color]

[deko]

Re: Databound list box populated with parameterized query?

> I cant see that using the function in this case is any different than[color=blue]
> coding:
>
> SELECT * FROM qryMyQuery WHERE MyField_ID = forms!frmMain!t xtMyId[/color]

It's not. That function is just my way to get variables into queries. I
find it easy to work with, debug and troubleshoot.

There's different ways to do it - that's just my preference, most of the
time.

I also like to do this (for SELECT queries):

Dim db as DAO.Database
Dim qdfs as DAO.Querydefs
Dim qdf as DAO.Querydef
Set db = CurrentDB
Set qdfs = db.Querydefs
Set qdf = qdfs(strQryName )
strSql = "dynamicall y created statement here"
qdf.SQL = strSql

And this (for simple action queries):

db.Execute strSql, dbFailOnError

And sometimes this (for more complex action queries):

qdf.Parameters( "prmWhateve r") = "parameter"
qdf.Execute

It all depends on the situation at hand.

[cjb_kjb]

Re: Databound list box populated with parameterized query?

Thanks for that.

Your idea of using a function I think may have solved a problem for me.

I have a financial application where records may be kept for a number
of Clients (/holders/entities - call them what you like). I have forms
such as Cheque (check), Direct deposit, Payments, Bank Statement etc
which all have a BankAccount combo box.
I only want it to show bank accounts for the holder who I am working on
in the drop down list, not bank accounts for every one, so the row
source of the combo box on each form is "Select
BankAccount.Acc ountNumber, ........from BankAccount where holderId =
forms!formname! holderId. As I can only work with 1 holder at a time
regardless of which form I use the HolderId will be the same. This
means instead of having a separate query on each combo box with only
the formname different in the WHERE clause I could have a common query
if I had a common HolderId reference point. I in fact do have that. I
have a class module calle gblHolder that looks up and holds a number of
attributes about a holder. Whenever I change holders this gets
populated so I dont have to have queries to read the database for the
same holder info all the time.

I tried using gblHolder.Holde rId in the query - i.e. Select ..... from
BankAccount where holderId = gblHolder.Holde rId but this didn't work -
seems queries wont look at class modules.

But using your method I can call a function that looks up gblHolder and
returns HolderId to the query. Now I can use a generalised query in
all forms that have a BankAccount combo box instead of a slightly
different one on each form.

Thanks

[deko]

Re: Databound list box populated with parameterized query?

> Your idea of using a function I think may have solved a problem for me.

Glad to hear it. In once scenario, similar to what you described, I have a
form with a "Transactio n Type" and a "Transactio n Account" combo box.

The Transaction Type RowSource looks like this:

SELECT tblTxType.TxTyp e_ID, tblTxType.TxTyp eName
FROM tblTxType INNER JOIN tblTxJournal ON tblTxType.TxTyp e_ID =
tblTxJournal.Tx Type_ID
WHERE tblTxJournal.En tity_ID = QryPrm("frm1", "Entity_ID" )
UNION SELECT 0, "<All Transaction Types>" FROM tblTxType
ORDER BY TxTypeName;

And the Transaction Account RowSource looks like this:

SELECT tblTxAcct.TxAcc t_ID, tblTxAcct.TxAcc tName
FROM tblTxAcct INNER JOIN tblTxJournal ON tblTxAcct.TxAcc t_ID =
tblTxJournal.Tx Acct_ID
WHERE (tblTxJournal.E ntity_ID = QryPrm("frm1", "Entity_ID" )) And
(tblTxJournal.T xType_ID = QryPrm("frm1", "cbx1TxType ") Or QryPrm("frm1",
"cbx1TxType ") = 0)
UNION SELECT 0, "<All Accounts>" FROM tblTxAcct
ORDER BY TxAcctName;

So TxTypes are filtered to only those Types containing transactions for the
current Entity, and TxAccts are filtered to only those Accounts containing
transactions, belonging to the selected Type, belonging to the current
Entity.