Form field values in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know I can specify form and field names in the criteria of a query.
However, I can't seem to be able to specify variables in the criteria of a
query.
Is this not possible?
I would like to run a query from either of two forms, the entry and the edit
forms, so I don't want to use the form and field names if it is possible to
do it another way.

Any help much appreciated!
Thx,
Kelly
 
There's more than one way to skin a cat.

Instead of doing that in the query, have generic code in the query, such as "
[Enter Info1 Here]" (without the quotes), "[Enter Info2 Here]", etc. (they
must be unique). In each form that uses the query, simply have code as
follows:

Private Sub Whatever()
Dim QDF as QueryDef, RST as Recordset

Set QDF = CurrentDb.QueryDefs("QueryNameInQuotesHere")
With QDF
.Parameters("Enter Info1 Here") = Something
.Parameters("Enter Info2 Here") = SomethingElse
Set RST = .OpenRecordset(dbOpenDynaset)

This assumes the query is not an action query. Access the data in the
recordset the way you did before. If it is an action query (any query that
permanently adds, deletes, or modifies records in a table), don't bother
DIMming a recordset. Simply follow the .Parameter statements with the
following line:
.Execute
In any event, don't forget to end your With with
End With
End Sub

HTH
 
Ok - thx!
I also thought of another way...
use docmd.runsql and -- it is an update query -- set the fields to the form
vars in the proc using the passed form name parm (instead of using a saved
query).
Not sure whether your method or mine uses fewer resources, but they should
both work!


OfficeDev18 via AccessMonster.com said:
There's more than one way to skin a cat.

Instead of doing that in the query, have generic code in the query, such as "
[Enter Info1 Here]" (without the quotes), "[Enter Info2 Here]", etc. (they
must be unique). In each form that uses the query, simply have code as
follows:

Private Sub Whatever()
Dim QDF as QueryDef, RST as Recordset

Set QDF = CurrentDb.QueryDefs("QueryNameInQuotesHere")
With QDF
.Parameters("Enter Info1 Here") = Something
.Parameters("Enter Info2 Here") = SomethingElse
Set RST = .OpenRecordset(dbOpenDynaset)

This assumes the query is not an action query. Access the data in the
recordset the way you did before. If it is an action query (any query that
permanently adds, deletes, or modifies records in a table), don't bother
DIMming a recordset. Simply follow the .Parameter statements with the
following line:
.Execute
In any event, don't forget to end your With with
End With
End Sub

HTH
I know I can specify form and field names in the criteria of a query.
However, I can't seem to be able to specify variables in the criteria of a
query.
Is this not possible?
I would like to run a query from either of two forms, the entry and the edit
forms, so I don't want to use the form and field names if it is possible to
do it another way.

Any help much appreciated!
Thx,
Kelly
Click to expand...
Click to expand...
 
I know I can specify form and field names in the criteria of a query.
However, I can't seem to be able to specify variables in the criteria of a
query.
Is this not possible?
I would like to run a query from either of two forms, the entry and the edit
forms, so I don't want to use the form and field names if it is possible to
do it another way.
Click to expand...

You will need to write VBA code to construct the SQL string from
scratch. I'd say it's much easier to use two queries - or, maybe
better, use just one form! Why is it necessary to have two forms to do
what one form can do alone (you can toggle the form's DataEntry
property on and off, for example).

John W. Vinson[MVP]
 
Yeah, they'll both work, Kelly, but like John Vinson says, you'll have to
code the SQL statement from scratch - in two different forms. Talk about the
probability of human error! It's MUCH better to use the stored query and use
programable parameters, than doing the manual thing.
Ok - thx!
I also thought of another way...
use docmd.runsql and -- it is an update query -- set the fields to the form
vars in the proc using the passed form name parm (instead of using a saved
query).
Not sure whether your method or mine uses fewer resources, but they should
both work!
There's more than one way to skin a cat.
Click to expand...
[quoted text clipped - 35 lines]
Click to expand...
 
Back
Top