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]

> 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.

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(strSubFrmCtl) = 0 Then
If Len(strSubFrm) = 0 Then
QryPrm = Forms(strFrm).Controls(strCtl)
Else
QryPrm = Forms(strFrm).Controls(strCtl).Form.Controls(strSu bFrm)
End If
Else
QryPrm =
Forms(strFrm).Controls(strCtl).Form.Controls(strSu bFrm).Form.Controls(strSubFrmCtl)
End If
End Function

[Mark]

"deko" <de**@nospam.com> wrote:

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")

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]

"deko" <de**@nospam.com> wrote:

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")

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]

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!txtMyId

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:

"deko" <de**@nospam.com> wrote:

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")

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

[deko]

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

coding:

SELECT * FROM qryMyQuery WHERE MyField_ID = forms!frmMain!txtMyId

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 = "dynamically 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("prmWhatever") = "parameter"
qdf.Execute

It all depends on the situation at hand.

[cjb_kjb]

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.AccountNumber, ........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.HolderId in the query - i.e. Select ..... from
BankAccount where holderId = gblHolder.HolderId 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]

> 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 "Transaction Type" and a "Transaction Account" combo box.

The Transaction Type RowSource looks like this:

SELECT tblTxType.TxType_ID, tblTxType.TxTypeName
FROM tblTxType INNER JOIN tblTxJournal ON tblTxType.TxType_ID =
tblTxJournal.TxType_ID
WHERE tblTxJournal.Entity_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.TxAcct_ID, tblTxAcct.TxAcctName
FROM tblTxAcct INNER JOIN tblTxJournal ON tblTxAcct.TxAcct_ID =
tblTxJournal.TxAcct_ID
WHERE (tblTxJournal.Entity_ID = QryPrm("frm1", "Entity_ID")) And
(tblTxJournal.TxType_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.