 | | | | | | | Coding a set of queries | | |
| | | Pwyd |  |
|  Posted: Tue Aug 26, 2008 4:24 pm Post subject: Coding a set of queries |  |
| |  | |
So i discovered through time that when it states that it has "too few
parameters, expected #" that what it really means is that the query you're
trying to run depends on something else that also has parameters which, since
they aren't explicitly being stated at this time, are considered 'parameters
not provided.' At any rate, my question is, how do i explicitly state where
the query should look for those values? the query uses two seperate tables,
and it looks like this, in sql:
SELECT Count(ReversalMemo.RecordNum) AS CountOfRecordNum
FROM ReversalMemo, UserIDQuery_SN_to_ProcName
HAVING
(((ReversalMemo.Signee1)=[UserIDQuery_SN_to_ProcName].[ProcessorName]));
run on its own in query view, it works fine. in code in VB, not so good 
How do i explicitly provide the "parameter" it needs, or must i "recreate"
the query itself by finding the other table's value myself and coding the
logic used to determine the countofrecordnum's value? |
| |
| | | Ken Sheridan | |
| | Posted: Tue Aug 26, 2008 9:06 pm Post subject: RE: Coding a set of queries | |
| | | |
You need to loop through the query's parameters collection and evaluate the
parameters, e.g. with a procedure like this:
Public Sub OpenTempQuery(strSQL As String)
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strQdf As String
Static n As Integer
Set dbs = CurrentDb
n = n + 1
strQdf = "qdfTemp" & n
' create temporary QueryDef object
Set qdf = dbs.CreateQueryDef(strQdf, strSQL)
' evaluate QueryDef object's parameters
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
' open temporary query
DoCmd.OpenQuery strQdf
' delete temporary QueryDef object
dbs.QueryDefs.Delete qdf.Name
End Sub
You'd then call it, passing the SQL statement into the procedure as a string
expression, e.g.
Dim strSQL As String
strSQL = " SELECT COUNT(*) AS CountOfRows " & _
"FROM ReversalMemo, UserIDQuery_SN_to_ProcName " & _
"WHERE ReversalMemo.Signee1 = " & _
"UserIDQuery_SN_to_ProcName.ProcessorName"
OpenTempQuery strSQL
The parameters must be something which can be evaluated such as a reference
to a control on an open form such as Forms!MyForm!MyComboBox, and not a
simple system generated parameter prompt such as [Enter name:].
BTW note that in the SQL statement a WHERE clause should be used not a
HAVING clause, even though the latter will have the same effect in this case.
The HAVING clause results in this instance from designing the query in query
design view without changing the 'total' from 'Group By' to 'Where'. A
HAVING clause is used to restrict a query's result set after grouping and
usually on the basis of some aggregated value, e.g. HAVING SUM(Sales) >=
10000 to restrict the result set to rows with total sales of 10,000 currency
units or more. To restrict before grouping a WHERE clause should be used.
Ken Sheridan
Stafford, England
"Pwyd" wrote:
| Quote: | So i discovered through time that when it states that it has "too few
parameters, expected #" that what it really means is that the query you're
trying to run depends on something else that also has parameters which, since
they aren't explicitly being stated at this time, are considered 'parameters
not provided.' At any rate, my question is, how do i explicitly state where
the query should look for those values? the query uses two seperate tables,
and it looks like this, in sql:
SELECT Count(ReversalMemo.RecordNum) AS CountOfRecordNum
FROM ReversalMemo, UserIDQuery_SN_to_ProcName
HAVING
(((ReversalMemo.Signee1)=[UserIDQuery_SN_to_ProcName].[ProcessorName]));
run on its own in query view, it works fine. in code in VB, not so good
How do i explicitly provide the "parameter" it needs, or must i "recreate"
the query itself by finding the other table's value myself and coding the
logic used to determine the countofrecordnum's value?
|
|
| |
| | | Pwyd | |
| | Posted: Wed Aug 27, 2008 11:49 am Post subject: RE: Coding a set of queries | |
| | | |
oh wow. I understand what you're stating and the logic of it, but not all of
the code, i'll definately need some help with this. So the parameter i'm
speaking of is not one that pops up, requesting a parameter... its provided
by a second table, its never explicitly entered by the user. Does that
change anything? or perhaps i'm misunderstanding what you've stated.
Thanks for the info on the "having", i usually don't change the "group by"
unless i put in any criteria at all for that field, then i change it to a
where. What are the properties of a querydef object? what is its common
usage?
"Ken Sheridan" wrote:
| Quote: | You need to loop through the query's parameters collection and evaluate the
parameters, e.g. with a procedure like this:
Public Sub OpenTempQuery(strSQL As String)
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strQdf As String
Static n As Integer
Set dbs = CurrentDb
n = n + 1
strQdf = "qdfTemp" & n
' create temporary QueryDef object
Set qdf = dbs.CreateQueryDef(strQdf, strSQL)
' evaluate QueryDef object's parameters
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
' open temporary query
DoCmd.OpenQuery strQdf
' delete temporary QueryDef object
dbs.QueryDefs.Delete qdf.Name
End Sub
You'd then call it, passing the SQL statement into the procedure as a string
expression, e.g.
Dim strSQL As String
strSQL = " SELECT COUNT(*) AS CountOfRows " & _
"FROM ReversalMemo, UserIDQuery_SN_to_ProcName " & _
"WHERE ReversalMemo.Signee1 = " & _
"UserIDQuery_SN_to_ProcName.ProcessorName"
OpenTempQuery strSQL
The parameters must be something which can be evaluated such as a reference
to a control on an open form such as Forms!MyForm!MyComboBox, and not a
simple system generated parameter prompt such as [Enter name:].
BTW note that in the SQL statement a WHERE clause should be used not a
HAVING clause, even though the latter will have the same effect in this case.
The HAVING clause results in this instance from designing the query in query
design view without changing the 'total' from 'Group By' to 'Where'. A
HAVING clause is used to restrict a query's result set after grouping and
usually on the basis of some aggregated value, e.g. HAVING SUM(Sales) >=
10000 to restrict the result set to rows with total sales of 10,000 currency
units or more. To restrict before grouping a WHERE clause should be used.
Ken Sheridan
Stafford, England
"Pwyd" wrote:
So i discovered through time that when it states that it has "too few
parameters, expected #" that what it really means is that the query you're
trying to run depends on something else that also has parameters which, since
they aren't explicitly being stated at this time, are considered 'parameters
not provided.' At any rate, my question is, how do i explicitly state where
the query should look for those values? the query uses two seperate tables,
and it looks like this, in sql:
SELECT Count(ReversalMemo.RecordNum) AS CountOfRecordNum
FROM ReversalMemo, UserIDQuery_SN_to_ProcName
HAVING
(((ReversalMemo.Signee1)=[UserIDQuery_SN_to_ProcName].[ProcessorName]));
run on its own in query view, it works fine. in code in VB, not so good
How do i explicitly provide the "parameter" it needs, or must i "recreate"
the query itself by finding the other table's value myself and coding the
logic used to determine the countofrecordnum's value?
|
|
| |
| | | Ken Sheridan | |
| | Posted: Wed Aug 27, 2008 1:34 pm Post subject: RE: Coding a set of queries | |
| | | |
A parameter is a variable which a query can use in a number of ways, but most
frequently it restricts the rows returned. This is what a simple prompt of
the type you are probably used to does, but it can also be a reference to a
control on a form. Take a look at the Sales by Year query in the sample
Northwind database which comes with Access; this includes the criterion on
the ShippedDate column:
Is Not Null And Between [Forms]![Sales by Year Dialog]![BeginningDate] And
[Forms]![Sales by Year Dialog]![EndingDate]
The references to the [Forms]![Sales by Year Dialog]![BeginningDate] and
[Forms]![Sales by Year Dialog]![EndingDate]controls are parameters. The
procedure I sent you will work with a query containing this type of parameter
because it can be evaluated by the Eval function. A simple prompt would not
work, however, because that cannot be evaluated. If you look at the Sales
by Year query in SQL view you'll notice that the parameters are declared as
DateTime at the start of the SQL statement. In my experience people often
omit to do this, but with dates its actually quite important as otherwise a
parameter value entered as a date in short date format could be
misinterpreted as an arithmetical expression.
I'm not sure what you have in mind when you say "its provided by a second
table". A value in a column in another table is not a parameter.
One common cause of a 'parameter expected' error is because users
incorporate a reference to a control on a form in a literal SQL string in
code, e.g "WHERE LastName = Forms!MyForm!txtLastName". What should be done
in a case like that is to concatenate the value of the control into the SQL
string; "WHERE LastName = """ & Forms!MyForm!txtLastName & """". That's not
the case with the SQL you posted, however. The most likely culprit is a
parameter in the UserIDQuery_SN_to_ProcName, and my function would handle
that. Sometimes the error is just a result of a simple typo. If a column
name is entered in the string incorrectly Access will treat it as a
parameter; essentially anything it doesn't recognize as an object is treated
as a parameter. However, you say that the query opens fine using the same
SQL as a query rather than in code, which suggests there is no typo.
The fact that its opening as a query does beg the question why you want to
build and open via VBA at all. Why not just save the query and open it with
the OpenQuery method?
A querydef object is an object which defines a query (its short for 'query
definition'). Saved queries are querydef objects in the current databases
querydefs collection. A querydef object does not have to be a saved query,
however. One can be temporarily created in code. This is commonly done and
its rows accessed by means of a recordset object created with the
OpenRecordset method of the querydef object. In my function a querydef
object is created with the CreateQueryDef method of the database object and
saved by giving it name. Its then opened in datasheet view with the
OpenQuery method and finally deleted from the current databases querydefs
collection. So its not quite as temporary as a true temporary querydef
object.
As a simple example paste the following procedure into a standard module:
Public Sub OpenRstFromQuery(strQuery As String)
Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim prm As DAO.Parameter, rst As DAO.Recordset
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs(strQuery)
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset
With rst
Do While Not .EOF
Debug.Print .Fields(0); .Fields(1)
.MoveNext
Loop
End With
End Sub
Then in the debug window (aka immediate window) call it, passing it the name
of any simple query with at least two columns returned like so:
OpenRstFromQuery "NameOfQueryGoesHere"
You'll see that it lists the values from first two columns of the query to
the debug window.
Ken Sheridan
Stafford, England
"Pwyd" wrote:
| Quote: | oh wow. I understand what you're stating and the logic of it, but not all of
the code, i'll definately need some help with this. So the parameter i'm
speaking of is not one that pops up, requesting a parameter... its provided
by a second table, its never explicitly entered by the user. Does that
change anything? or perhaps i'm misunderstanding what you've stated.
Thanks for the info on the "having", i usually don't change the "group by"
unless i put in any criteria at all for that field, then i change it to a
where. What are the properties of a querydef object? what is its common
usage?
"Ken Sheridan" wrote:
You need to loop through the query's parameters collection and evaluate the
parameters, e.g. with a procedure like this:
Public Sub OpenTempQuery(strSQL As String)
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strQdf As String
Static n As Integer
Set dbs = CurrentDb
n = n + 1
strQdf = "qdfTemp" & n
' create temporary QueryDef object
Set qdf = dbs.CreateQueryDef(strQdf, strSQL)
' evaluate QueryDef object's parameters
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
' open temporary query
DoCmd.OpenQuery strQdf
' delete temporary QueryDef object
dbs.QueryDefs.Delete qdf.Name
End Sub
You'd then call it, passing the SQL statement into the procedure as a string
expression, e.g.
Dim strSQL As String
strSQL = " SELECT COUNT(*) AS CountOfRows " & _
"FROM ReversalMemo, UserIDQuery_SN_to_ProcName " & _
"WHERE ReversalMemo.Signee1 = " & _
"UserIDQuery_SN_to_ProcName.ProcessorName"
OpenTempQuery strSQL
The parameters must be something which can be evaluated such as a reference
to a control on an open form such as Forms!MyForm!MyComboBox, and not a
simple system generated parameter prompt such as [Enter name:].
BTW note that in the SQL statement a WHERE clause should be used not a
HAVING clause, even though the latter will have the same effect in this case.
The HAVING clause results in this instance from designing the query in query
design view without changing the 'total' from 'Group By' to 'Where'. A
HAVING clause is used to restrict a query's result set after grouping and
usually on the basis of some aggregated value, e.g. HAVING SUM(Sales) >=
10000 to restrict the result set to rows with total sales of 10,000 currency
units or more. To restrict before grouping a WHERE clause should be used.
Ken Sheridan
Stafford, England
"Pwyd" wrote:
So i discovered through time that when it states that it has "too few
parameters, expected #" that what it really means is that the query you're
trying to run depends on something else that also has parameters which, since
they aren't explicitly being stated at this time, are considered 'parameters
not provided.' At any rate, my question is, how do i explicitly state where
the query should look for those values? the query uses two seperate tables,
and it looks like this, in sql:
SELECT Count(ReversalMemo.RecordNum) AS CountOfRecordNum
FROM ReversalMemo, UserIDQuery_SN_to_ProcName
HAVING
(((ReversalMemo.Signee1)=[UserIDQuery_SN_to_ProcName].[ProcessorName]));
run on its own in query view, it works fine. in code in VB, not so good
How do i explicitly provide the "parameter" it needs, or must i "recreate"
the query itself by finding the other table's value myself and coding the
logic used to determine the countofrecordnum's value?
|
|
| |
| | | Pwyd | |
| | Posted: Wed Aug 27, 2008 2:26 pm Post subject: RE: Coding a set of queries | |
| | | |
I've had the same trouble with the openquery method. Perhaps we could try
that instead. Here, let me post the code, and yes, the query i'm trying to
run does refer to a value based on a second table. Here:
Public Function testfunction()
Dim adminQuery As Recordset
Dim processorQuery As Recordset
Dim recordCount As Recordset
Set adminQuery = CurrentDb.OpenRecordset("IsAdminQuery")
Set processorQuery = CurrentDb.OpenRecordset("IsProcessorQuery")
'Set recordCount = CurrentDb.OpenRecordset("CountOfUserExistingRecords")
If (adminQuery.BOF And adminQuery.EOF) Then
If (processorQuery.BOF And processorQuery.EOF) Then
DoCmd.Close
ElseIf (recordCount!RecordNum <= 0) Then
DoCmd.OpenForm ("Costpoint Processor New Record")
Else: DoCmd.OpenForm ("ReversalCostpoint Processor")
End If
Else: DoCmd.OpenForm ("Costpoint Reversals")
End If
End Function
The count of user existing records query is the problem. It looks like this:
SELECT Count(ReversalMemo.RecordNum) AS CountOfRecordNum
FROM ReversalMemo, UserIDQuery_SN_to_ProcName
HAVING
(((ReversalMemo.Signee1)=[UserIDQuery_SN_to_ProcName].[ProcessorName]));
The user_sn... query produces a user's true name based on a shortname from
their login. It's later used to filter their records. The purpose of this
particular part of the function is, if the user is listed as a processor, but
has 0 records (null or 0 result) the form opens up the new record form
instead of the processor form, which would be a blank white page since its
not for data entry.
Its basically a very simple decision tree. Is the user an admin? if yes,
open admin version. if no, is the user a processor? if yes, open the
processor form, showing them only their own records. if no, close. if the
processor is valid but has no records, start a new record.
The count of user records query uses two seperate tables in the same query
to determine if the record count. Running it through VB, it expects a
parameter value for the criteria i've set, since its not: concurrently
running, can't be determined without checking the row value determined by the
other query (user_sn_to...), I suppose, or explicitly defined. What else
can i provide that might help you see what i'm trying to accomplish, and the
simplest way to do it?
I'd also been advised to try a make-table query, to put the two values in
one table so that they could be easily read, but i can't compact on exit,
because i do not own the drive priveledges where the database is served, so i
would be left with lots of bloating :(
"Ken Sheridan" wrote:
| Quote: | A parameter is a variable which a query can use in a number of ways, but most
frequently it restricts the rows returned. This is what a simple prompt of
the type you are probably used to does, but it can also be a reference to a
control on a form. Take a look at the Sales by Year query in the sample
Northwind database which comes with Access; this includes the criterion on
the ShippedDate column:
Is Not Null And Between [Forms]![Sales by Year Dialog]![BeginningDate] And
[Forms]![Sales by Year Dialog]![EndingDate]
The references to the [Forms]![Sales by Year Dialog]![BeginningDate] and
[Forms]![Sales by Year Dialog]![EndingDate]controls are parameters. The
procedure I sent you will work with a query containing this type of parameter
because it can be evaluated by the Eval function. A simple prompt would not
work, however, because that cannot be evaluated. If you look at the Sales
by Year query in SQL view you'll notice that the parameters are declared as
DateTime at the start of the SQL statement. In my experience people often
omit to do this, but with dates its actually quite important as otherwise a
parameter value entered as a date in short date format could be
misinterpreted as an arithmetical expression.
I'm not sure what you have in mind when you say "its provided by a second
table". A value in a column in another table is not a parameter.
One common cause of a 'parameter expected' error is because users
incorporate a reference to a control on a form in a literal SQL string in
code, e.g "WHERE LastName = Forms!MyForm!txtLastName". What should be done
in a case like that is to concatenate the value of the control into the SQL
string; "WHERE LastName = """ & Forms!MyForm!txtLastName & """". That's not
the case with the SQL you posted, however. The most likely culprit is a
parameter in the UserIDQuery_SN_to_ProcName, and my function would handle
that. Sometimes the error is just a result of a simple typo. If a column
name is entered in the string incorrectly Access will treat it as a
parameter; essentially anything it doesn't recognize as an object is treated
as a parameter. However, you say that the query opens fine using the same
SQL as a query rather than in code, which suggests there is no typo.
The fact that its opening as a query does beg the question why you want to
build and open via VBA at all. Why not just save the query and open it with
the OpenQuery method?
A querydef object is an object which defines a query (its short for 'query
definition'). Saved queries are querydef objects in the current databases
querydefs collection. A querydef object does not have to be a saved query,
however. One can be temporarily created in code. This is commonly done and
its rows accessed by means of a recordset object created with the
OpenRecordset method of the querydef object. In my function a querydef
object is created with the CreateQueryDef method of the database object and
saved by giving it name. Its then opened in datasheet view with the
OpenQuery method and finally deleted from the current databases querydefs
collection. So its not quite as temporary as a true temporary querydef
object.
As a simple example paste the following procedure into a standard module:
Public Sub OpenRstFromQuery(strQuery As String)
Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim prm As DAO.Parameter, rst As DAO.Recordset
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs(strQuery)
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset
With rst
Do While Not .EOF
Debug.Print .Fields(0); .Fields(1)
.MoveNext
Loop
End With
End Sub
Then in the debug window (aka immediate window) call it, passing it the name
of any simple query with at least two columns returned like so:
OpenRstFromQuery "NameOfQueryGoesHere"
You'll see that it lists the values from first two columns of the query to
the debug window.
Ken Sheridan
Stafford, England
"Pwyd" wrote:
oh wow. I understand what you're stating and the logic of it, but not all of
the code, i'll definately need some help with this. So the parameter i'm
speaking of is not one that pops up, requesting a parameter... its provided
by a second table, its never explicitly entered by the user. Does that
change anything? or perhaps i'm misunderstanding what you've stated.
Thanks for the info on the "having", i usually don't change the "group by"
unless i put in any criteria at all for that field, then i change it to a
where. What are the properties of a querydef object? what is its common
usage?
"Ken Sheridan" wrote:
You need to loop through the query's parameters collection and evaluate the
parameters, e.g. with a procedure like this:
Public Sub OpenTempQuery(strSQL As String)
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strQdf As String
Static n As Integer
Set dbs = CurrentDb
n = n + 1
strQdf = "qdfTemp" & n
' create temporary QueryDef object
Set qdf = dbs.CreateQueryDef(strQdf, strSQL)
' evaluate QueryDef object's parameters
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
' open temporary query
DoCmd.OpenQuery strQdf
' delete temporary QueryDef object
dbs.QueryDefs.Delete qdf.Name
End Sub
You'd then call it, passing the SQL statement into the procedure as a string
expression, e.g.
Dim strSQL As String
strSQL = " SELECT COUNT(*) AS CountOfRows " & _
"FROM ReversalMemo, UserIDQuery_SN_to_ProcName " & _
"WHERE ReversalMemo.Signee1 = " & _
"UserIDQuery_SN_to_ProcName.ProcessorName"
OpenTempQuery strSQL
The parameters must be something which can be evaluated such as a reference
to a control on an open form such as Forms!MyForm!MyComboBox, and not a
simple system generated parameter prompt such as [Enter name:].
BTW note that in the SQL statement a WHERE clause should be used not a
HAVING clause, even though the latter will have the same effect in this case.
The HAVING clause results in this instance from designing the query in query
design view without changing the 'total' from 'Group By' to 'Where'. A
HAVING clause is used to restrict a query's result set after grouping and
usually on the basis of some aggregated value, e.g. HAVING SUM(Sales) >=
10000 to restrict the result set to rows with total sales of 10,000 currency
units or more. To restrict before grouping a WHERE clause should be used.
Ken Sheridan
Stafford, England
"Pwyd" wrote:
So i discovered through time that when it states that it has "too few
parameters, expected #" that what it really means is that the query you're
trying to run depends on something else that also has parameters which, since
they aren't explicitly being stated at this time, are considered 'parameters
not provided.' At any rate, my question is, how do i explicitly state where
the query should look for those values? the query uses two seperate tables,
and it looks like this, in sql:
SELECT Count(ReversalMemo.RecordNum) AS CountOfRecordNum
FROM ReversalMemo, UserIDQuery_SN_to_ProcName
HAVING
(((ReversalMemo.Signee1)=[UserIDQuery_SN_to_ProcName].[ProcessorName]));
run on its own in query view, it works fine. in code in VB, not so good
How do i explicitly provide the "parameter" it needs, or must i "recreate"
the query itself by finding the other table's value myself and coding the
logic used to determine the countofrecordnum's value?
|
|
| |
| | | Ken Sheridan | |
| | Posted: Wed Aug 27, 2008 3:30 pm Post subject: RE: Coding a set of queries | |
| | | |
How are you getting the current user's login name for the query? If you are
calling the Windows API GetUserName function that's not a parameter so should
be OK, but if you are getting it in some other way its possible it could be
being treated as a parameter.
Rather than creating recordset objects why not use the DLookup function to
get the value of any non-Null column from a query? Test for
IsNull(DLookup("ColumnName","QueryName")) to identify no rows returned, and
for DLookup("ColumnName","QueryName") = 0 to identify where the Count
operator returns a 0.
BTW don't use recordcount as an object variable's name; it’s a property name
in both DAO and ADO so should be avoided.
I also note that you refer to recordCount!RecordNum <= 0, but in the SQL
statement the column name is CountOfRecordNum.
Ken Sheridan
Stafford, England
"Pwyd" wrote:
| Quote: | I've had the same trouble with the openquery method. Perhaps we could try
that instead. Here, let me post the code, and yes, the query i'm trying to
run does refer to a value based on a second table. Here:
Public Function testfunction()
Dim adminQuery As Recordset
Dim processorQuery As Recordset
Dim recordCount As Recordset
Set adminQuery = CurrentDb.OpenRecordset("IsAdminQuery")
Set processorQuery = CurrentDb.OpenRecordset("IsProcessorQuery")
'Set recordCount = CurrentDb.OpenRecordset("CountOfUserExistingRecords")
If (adminQuery.BOF And adminQuery.EOF) Then
If (processorQuery.BOF And processorQuery.EOF) Then
DoCmd.Close
ElseIf (recordCount!RecordNum <= 0) Then
DoCmd.OpenForm ("Costpoint Processor New Record")
Else: DoCmd.OpenForm ("ReversalCostpoint Processor")
End If
Else: DoCmd.OpenForm ("Costpoint Reversals")
End If
End Function
The count of user existing records query is the problem. It looks like this:
SELECT Count(ReversalMemo.RecordNum) AS CountOfRecordNum
FROM ReversalMemo, UserIDQuery_SN_to_ProcName
HAVING
(((ReversalMemo.Signee1)=[UserIDQuery_SN_to_ProcName].[ProcessorName]));
The user_sn... query produces a user's true name based on a shortname from
their login. It's later used to filter their records. The purpose of this
particular part of the function is, if the user is listed as a processor, but
has 0 records (null or 0 result) the form opens up the new record form
instead of the processor form, which would be a blank white page since its
not for data entry.
Its basically a very simple decision tree. Is the user an admin? if yes,
open admin version. if no, is the user a processor? if yes, open the
processor form, showing them only their own records. if no, close. if the
processor is valid but has no records, start a new record.
The count of user records query uses two seperate tables in the same query
to determine if the record count. Running it through VB, it expects a
parameter value for the criteria i've set, since its not: concurrently
running, can't be determined without checking the row value determined by the
other query (user_sn_to...), I suppose, or explicitly defined. What else
can i provide that might help you see what i'm trying to accomplish, and the
simplest way to do it?
I'd also been advised to try a make-table query, to put the two values in
one table so that they could be easily read, but i can't compact on exit,
because i do not own the drive priveledges where the database is served, so i
would be left with lots of bloating :(
"Ken Sheridan" wrote:
A parameter is a variable which a query can use in a number of ways, but most
frequently it restricts the rows returned. This is what a simple prompt of
the type you are probably used to does, but it can also be a reference to a
control on a form. Take a look at the Sales by Year query in the sample
Northwind database which comes with Access; this includes the criterion on
the ShippedDate column:
Is Not Null And Between [Forms]![Sales by Year Dialog]![BeginningDate] And
[Forms]![Sales by Year Dialog]![EndingDate]
The references to the [Forms]![Sales by Year Dialog]![BeginningDate] and
[Forms]![Sales by Year Dialog]![EndingDate]controls are parameters. The
procedure I sent you will work with a query containing this type of parameter
because it can be evaluated by the Eval function. A simple prompt would not
work, however, because that cannot be evaluated. If you look at the Sales
by Year query in SQL view you'll notice that the parameters are declared as
DateTime at the start of the SQL statement. In my experience people often
omit to do this, but with dates its actually quite important as otherwise a
parameter value entered as a date in short date format could be
misinterpreted as an arithmetical expression.
I'm not sure what you have in mind when you say "its provided by a second
table". A value in a column in another table is not a parameter.
One common cause of a 'parameter expected' error is because users
incorporate a reference to a control on a form in a literal SQL string in
code, e.g "WHERE LastName = Forms!MyForm!txtLastName". What should be done
in a case like that is to concatenate the value of the control into the SQL
string; "WHERE LastName = """ & Forms!MyForm!txtLastName & """". That's not
the case with the SQL you posted, however. The most likely culprit is a
parameter in the UserIDQuery_SN_to_ProcName, and my function would handle
that. Sometimes the error is just a result of a simple typo. If a column
name is entered in the string incorrectly Access will treat it as a
parameter; essentially anything it doesn't recognize as an object is treated
as a parameter. However, you say that the query opens fine using the same
SQL as a query rather than in code, which suggests there is no typo.
The fact that its opening as a query does beg the question why you want to
build and open via VBA at all. Why not just save the query and open it with
the OpenQuery method?
A querydef object is an object which defines a query (its short for 'query
definition'). Saved queries are querydef objects in the current databases
querydefs collection. A querydef object does not have to be a saved query,
however. One can be temporarily created in code. This is commonly done and
its rows accessed by means of a recordset object created with the
OpenRecordset method of the querydef object. In my function a querydef
object is created with the CreateQueryDef method of the database object and
saved by giving it name. Its then opened in datasheet view with the
OpenQuery method and finally deleted from the current databases querydefs
collection. So its not quite as temporary as a true temporary querydef
object.
As a simple example paste the following procedure into a standard module:
Public Sub OpenRstFromQuery(strQuery As String)
Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim prm As DAO.Parameter, rst As DAO.Recordset
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs(strQuery)
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset
With rst
Do While Not .EOF
Debug.Print .Fields(0); .Fields(1)
.MoveNext
Loop
End With
End Sub
Then in the debug window (aka immediate window) call it, passing it the name
of any simple query with at least two columns returned like so:
OpenRstFromQuery "NameOfQueryGoesHere"
You'll see that it lists the values from first two columns of the query to
the debug window.
Ken Sheridan
Stafford, England
"Pwyd" wrote:
oh wow. I understand what you're stating and the logic of it, but not all of
the code, i'll definately need some help with this. So the parameter i'm
speaking of is not one that pops up, requesting a parameter... its provided
by a second table, its never explicitly entered by the user. Does that
change anything? or perhaps i'm misunderstanding what you've stated.
Thanks for the info on the "having", i usually don't change the "group by"
unless i put in any criteria at all for that field, then i change it to a
where. What are the properties of a querydef object? what is its common
usage?
"Ken Sheridan" wrote:
You need to loop through the query's parameters collection and evaluate the
parameters, e.g. with a procedure like this:
Public Sub OpenTempQuery(strSQL As String)
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strQdf As String
Static n As Integer
Set dbs = CurrentDb
n = n + 1
strQdf = "qdfTemp" & n
' create temporary QueryDef object
Set qdf = dbs.CreateQueryDef(strQdf, strSQL)
' evaluate QueryDef object's parameters
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
' open temporary query
DoCmd.OpenQuery strQdf
' delete temporary QueryDef object
dbs.QueryDefs.Delete qdf.Name
End Sub
You'd then call it, passing the SQL statement into the procedure as a string
expression, e.g.
Dim strSQL As String
strSQL = " SELECT COUNT(*) AS CountOfRows " & _
"FROM ReversalMemo, UserIDQuery_SN_to_ProcName " & _
"WHERE ReversalMemo.Signee1 = " & _
"UserIDQuery_SN_to_ProcName.ProcessorName"
OpenTempQuery strSQL
The parameters must be something which can be evaluated such as a reference
to a control on an open form such as Forms!MyForm!MyComboBox, and not a
simple system generated parameter prompt such as [Enter name:].
BTW note that in the SQL statement a WHERE clause should be used not a
HAVING clause, even though the latter will have the same effect in this case.
The HAVING clause results in this instance from designing the query in query
design view without changing the 'total' from 'Group By' to 'Where'. A
HAVING clause is used to restrict a query's result set after grouping and
usually on the basis of some aggregated value, e.g. HAVING SUM(Sales) >=
10000 to restrict the result set to rows with total sales of 10,000 currency
units or more. To restrict before grouping a WHERE clause should be used.
Ken Sheridan
Stafford, England
"Pwyd" wrote:
So i discovered through time that when it states that it has "too few
parameters, expected #" that what it really means is that the query you're
trying to run depends on something else that also has parameters which, since
they aren't explicitly being stated at this time, are considered 'parameters
not provided.' At any rate, my question is, how do i explicitly state where
the query should look for those values? the query uses two seperate tables,
and it looks like this, in sql:
SELECT Count(ReversalMemo.RecordNum) AS CountOfRecordNum
FROM ReversalMemo, UserIDQuery_SN_to_ProcName
HAVING
(((ReversalMemo.Signee1)=[UserIDQuery_SN_to_ProcName].[ProcessorName]));
run on its own in query view, it works fine. in code in VB, not so good
How do i explicitly provide the "parameter" it needs, or must i "recreate"
the query itself by finding the other table's value myself and coding the
logic used to determine the countofrecordnum's value?
|
|
| |
| | | Pwyd | |
| | Posted: Wed Aug 27, 2008 4:54 pm Post subject: RE: Coding a set of queries | |
| | | |
i'm using a GetUserID() public function built with the help of the users
here, and yes i believe it uses the windows API.
Does dlookup instantiate the query on its own, or does it require the query
is running when it is called?
as for the column name, yes, that is how its referred to by me, i was
unsure if that was correct. Does the recordset object adopt the field names
of the thing it contains, or is it still proper to refer to the name of the
query itself thats being run?
In addition, is the way i've contstructed it improper, or is dlookup just
more efficient, faster, etc?
"Ken Sheridan" wrote:
| Quote: | How are you getting the current user's login name for the query? If you are
calling the Windows API GetUserName function that's not a parameter so should
be OK, but if you are getting it in some other way its possible it could be
being treated as a parameter.
Rather than creating recordset objects why not use the DLookup function to
get the value of any non-Null column from a query? Test for
IsNull(DLookup("ColumnName","QueryName")) to identify no rows returned, and
for DLookup("ColumnName","QueryName") = 0 to identify where the Count
operator returns a 0.
BTW don't use recordcount as an object variable's name; it’s a property name
in both DAO and ADO so should be avoided.
I also note that you refer to recordCount!RecordNum <= 0, but in the SQL
statement the column name is CountOfRecordNum.
Ken Sheridan
Stafford, England
"Pwyd" wrote:
I've had the same trouble with the openquery method. Perhaps we could try
that instead. Here, let me post the code, and yes, the query i'm trying to
run does refer to a value based on a second table. Here:
Public Function testfunction()
Dim adminQuery As Recordset
Dim processorQuery As Recordset
Dim recordCount As Recordset
Set adminQuery = CurrentDb.OpenRecordset("IsAdminQuery")
Set processorQuery = CurrentDb.OpenRecordset("IsProcessorQuery")
'Set recordCount = CurrentDb.OpenRecordset("CountOfUserExistingRecords")
If (adminQuery.BOF And adminQuery.EOF) Then
If (processorQuery.BOF And processorQuery.EOF) Then
DoCmd.Close
ElseIf (recordCount!RecordNum <= 0) Then
DoCmd.OpenForm ("Costpoint Processor New Record")
Else: DoCmd.OpenForm ("ReversalCostpoint Processor")
End If
Else: DoCmd.OpenForm ("Costpoint Reversals")
End If
End Function
The count of user existing records query is the problem. It looks like this:
SELECT Count(ReversalMemo.RecordNum) AS CountOfRecordNum
FROM ReversalMemo, UserIDQuery_SN_to_ProcName
HAVING
(((ReversalMemo.Signee1)=[UserIDQuery_SN_to_ProcName].[ProcessorName]));
The user_sn... query produces a user's true name based on a shortname from
their login. It's later used to filter their records. The purpose of this
particular part of the function is, if the user is listed as a processor, but
has 0 records (null or 0 result) the form opens up the new record form
instead of the processor form, which would be a blank white page since its
not for data entry.
Its basically a very simple decision tree. Is the user an admin? if yes,
open admin version. if no, is the user a processor? if yes, open the
processor form, showing them only their own records. if no, close. if the
processor is valid but has no records, start a new record.
The count of user records query uses two seperate tables in the same query
to determine if the record count. Running it through VB, it expects a
parameter value for the criteria i've set, since its not: concurrently
running, can't be determined without checking the row value determined by the
other query (user_sn_to...), I suppose, or explicitly defined. What else
can i provide that might help you see what i'm trying to accomplish, and the
simplest way to do it?
I'd also been advised to try a make-table query, to put the two values in
one table so that they could be easily read, but i can't compact on exit,
because i do not own the drive priveledges where the database is served, so i
would be left with lots of bloating :(
"Ken Sheridan" wrote:
A parameter is a variable which a query can use in a number of ways, but most
frequently it restricts the rows returned. This is what a simple prompt of
the type you are probably used to does, but it can also be a reference to a
control on a form. Take a look at the Sales by Year query in the sample
Northwind database which comes with Access; this includes the criterion on
the ShippedDate column:
Is Not Null And Between [Forms]![Sales by Year Dialog]![BeginningDate] And
[Forms]![Sales by Year Dialog]![EndingDate]
The references to the [Forms]![Sales by Year Dialog]![BeginningDate] and
[Forms]![Sales by Year Dialog]![EndingDate]controls are parameters. The
procedure I sent you will work with a query containing this type of parameter
because it can be evaluated by the Eval function. A simple prompt would not
work, however, because that cannot be evaluated. If you look at the Sales
by Year query in SQL view you'll notice that the parameters are declared as
DateTime at the start of the SQL statement. In my experience people often
omit to do this, but with dates its actually quite important as otherwise a
parameter value entered as a date in short date format could be
misinterpreted as an arithmetical expression.
I'm not sure what you have in mind when you say "its provided by a second
table". A value in a column in another table is not a parameter.
One common cause of a 'parameter expected' error is because users
incorporate a reference to a control on a form in a literal SQL string in
code, e.g "WHERE LastName = Forms!MyForm!txtLastName". What should be done
in a case like that is to concatenate the value of the control into the SQL
string; "WHERE LastName = """ & Forms!MyForm!txtLastName & """". That's not
the case with the SQL you posted, however. The most likely culprit is a
parameter in the UserIDQuery_SN_to_ProcName, and my function would handle
that. Sometimes the error is just a result of a simple typo. If a column
name is entered in the string incorrectly Access will treat it as a
parameter; essentially anything it doesn't recognize as an object is treated
as a parameter. However, you say that the query opens fine using the same
SQL as a query rather than in code, which suggests there is no typo.
The fact that its opening as a query does beg the question why you want to
build and open via VBA at all. Why not just save the query and open it with
the OpenQuery method?
A querydef object is an object which defines a query (its short for 'query
definition'). Saved queries are querydef objects in the current databases
querydefs collection. A querydef object does not have to be a saved query,
however. One can be temporarily created in code. This is commonly done and
its rows accessed by means of a recordset object created with the
OpenRecordset method of the querydef object. In my function a querydef
object is created with the CreateQueryDef method of the database object and
saved by giving it name. Its then opened in datasheet view with the
OpenQuery method and finally deleted from the current databases querydefs
collection. So its not quite as temporary as a true temporary querydef
object.
As a simple example paste the following procedure into a standard module:
Public Sub OpenRstFromQuery(strQuery As String)
Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim prm As DAO.Parameter, rst As DAO.Recordset
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs(strQuery)
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset
With rst
Do While Not .EOF
Debug.Print .Fields(0); .Fields(1)
.MoveNext
Loop
End With
End Sub
Then in the debug window (aka immediate window) call it, passing it the name
of any simple query with at least two columns returned like so:
OpenRstFromQuery "NameOfQueryGoesHere"
You'll see that it lists the values from first two columns of the query to
the debug window.
Ken Sheridan
Stafford, England
"Pwyd" wrote:
oh wow. I understand what you're stating and the logic of it, but not all of
the code, i'll definately need some help with this. So the parameter i'm
speaking of is not one that pops up, requesting a parameter... its provided
by a second table, its never explicitly entered by the user. Does that
change anything? or perhaps i'm misunderstanding what you've stated.
Thanks for the info on the "having", i usually don't change the "group by"
unless i put in any criteria at all for that field, then i change it to a
where. What are the properties of a querydef object? what is its common
usage?
"Ken Sheridan" wrote:
You need to loop through the query's parameters collection and evaluate the
parameters, e.g. with a procedure like this:
Public Sub OpenTempQuery(strSQL As String)
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strQdf As String
Static n As Integer
Set dbs = CurrentDb
n = n + 1
strQdf = "qdfTemp" & n
' create temporary QueryDef object
Set qdf = dbs.CreateQueryDef(strQdf, strSQL)
' evaluate QueryDef object's parameters
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
' open temporary query
DoCmd.OpenQuery strQdf
' delete temporary QueryDef object
dbs.QueryDefs.Delete qdf.Name
End Sub
You'd then call it, passing the SQL statement into the procedure as a string
expression, e.g.
Dim strSQL As String
strSQL = " SELECT COUNT(*) AS CountOfRows " & _
"FROM ReversalMemo, UserIDQuery_SN_to_ProcName " & _
"WHERE ReversalMemo.Signee1 = " & _
"UserIDQuery_SN_to_ProcName.ProcessorName"
OpenTempQuery strSQL
The parameters must be something which can be evaluated such as a reference
to a control on an open form such as Forms!MyForm!MyComboBox, and not a
simple system generated parameter prompt such as [Enter name:].
BTW note that in the SQL statement a WHERE clause should be used not a
HAVING clause, even though the latter will have the same effect in this case.
The HAVING clause results in this instance from designing the query in query
design view without changing the 'total' from 'Group By' to 'Where'. A
HAVING clause is used to restrict a query's result set after grouping and
usually on the basis of some aggregated value, e.g. HAVING SUM(Sales) >=
10000 to restrict the result set to rows with total sales of 10,000 currency
units or more. To restrict before grouping a WHERE clause should be used.
Ken Sheridan
Stafford, England
"Pwyd" wrote:
So i discovered through time that when it states that it has "too few
parameters, expected #" that what it really means is that the query you're
trying to run depends on something else that also has parameters which, since
they aren't explicitly being stated at this time, are considered 'parameters
not provided.' At any rate, my question is, how do i explicitly state where
the query should look for those values? the query uses two seperate tables,
and it looks like this, in sql:
SELECT Count(ReversalMemo.RecordNum) AS CountOfRecordNum
FROM ReversalMemo, UserIDQuery_SN_to_ProcName
HAVING
(((ReversalMemo.Signee1)=[UserIDQuery_SN_to_ProcName].[ProcessorName]));
run on its own in query view, it works fine. in code in VB, not so good
How do i explicitly provide the "parameter" it needs, or must i "recreate"
the query itself by finding the other table's value myself and coding the
logic used to determine the countofrecordnum's value?
|
|
| |
| | | Ken Sheridan | |
| | Posted: Wed Aug 27, 2008 7:50 pm Post subject: RE: Coding a set of queries | |
| | | |
Your GetUserID function almost certainly calls the Widows API GetUserName
function, so I think we can rule that out as the mysterious parameter.
A query doesn't have to be open when the DLookup function (or any other
domain function such as DCount, DMax etc) is called.
If you create a recordset object based on a query then its Fields collection
is the set of columns returned by the query, so it does in effect "adopt the
field names". You cannot refer directly to the value of a field returned by
a query; you either have to create a recordset object (as my sample
OpenRstFromQuery procedure does) or use one of the domain function such as
DLookup.
But we are getting away from the original problem of what is the expected
parameter. You should be able to list the parameters for the SQL statement
with the following function:
Public Sub ListParameters(strSQL As String)
Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("", strSQL)
For Each prm In qdf.Parameters
Debug.Print prm.Name
Next prm
End Sub
Call it like so:
ListParameters "SELECT * FROM CountOfUserExistingRecords"
Any parameters expected by the CountOfUserExistingRecords query should be
listed to the debug window. This should point you in the direction of the
cause of the problem, and hopefully to a solution.
Ken Sheridan
Stafford, England
"Pwyd" wrote:
| Quote: | i'm using a GetUserID() public function built with the help of the users
here, and yes i believe it uses the windows API.
Does dlookup instantiate the query on its own, or does it require the query
is running when it is called?
as for the column name, yes, that is how its referred to by me, i was
unsure if that was correct. Does the recordset object adopt the field names
of the thing it contains, or is it still proper to refer to the name of the
query itself thats being run?
In addition, is the way i've contstructed it improper, or is dlookup just
more efficient, faster, etc?
"Ken Sheridan" wrote:
How are you getting the current user's login name for the query? If you are
calling the Windows API GetUserName function that's not a parameter so should
be OK, but if you are getting it in some other way its possible it could be
being treated as a parameter.
Rather than creating recordset objects why not use the DLookup function to
get the value of any non-Null column from a query? Test for
IsNull(DLookup("ColumnName","QueryName")) to identify no rows returned, and
for DLookup("ColumnName","QueryName") = 0 to identify where the Count
operator returns a 0.
BTW don't use recordcount as an object variable's name; it’s a property name
in both DAO and ADO so should be avoided.
I also note that you refer to recordCount!RecordNum <= 0, but in the SQL
statement the column name is CountOfRecordNum.
Ken Sheridan
Stafford, England
"Pwyd" wrote:
I've had the same trouble with the openquery method. Perhaps we could try
that instead. Here, let me post the code, and yes, the query i'm trying to
run does refer to a value based on a second table. Here:
Public Function testfunction()
Dim adminQuery As Recordset
Dim processorQuery As Recordset
Dim recordCount As Recordset
Set adminQuery = CurrentDb.OpenRecordset("IsAdminQuery")
Set processorQuery = CurrentDb.OpenRecordset("IsProcessorQuery")
'Set recordCount = CurrentDb.OpenRecordset("CountOfUserExistingRecords")
If (adminQuery.BOF And adminQuery.EOF) Then
If (processorQuery.BOF And processorQuery.EOF) Then
DoCmd.Close
ElseIf (recordCount!RecordNum <= 0) Then
DoCmd.OpenForm ("Costpoint Processor New Record")
Else: DoCmd.OpenForm ("ReversalCostpoint Processor")
End If
Else: DoCmd.OpenForm ("Costpoint Reversals")
End If
End Function
The count of user existing records query is the problem. It looks like this:
SELECT Count(ReversalMemo.RecordNum) AS CountOfRecordNum
FROM ReversalMemo, UserIDQuery_SN_to_ProcName
HAVING
(((ReversalMemo.Signee1)=[UserIDQuery_SN_to_ProcName].[ProcessorName]));
The user_sn... query produces a user's true name based on a shortname from
their login. It's later used to filter their records. The purpose of this
particular part of the function is, if the user is listed as a processor, but
has 0 records (null or 0 result) the form opens up the new record form
instead of the processor form, which would be a blank white page since its
not for data entry.
Its basically a very simple decision tree. Is the user an admin? if yes,
open admin version. if no, is the user a processor? if yes, open the
processor form, showing them only their own records. if no, close. if the
processor is valid but has no records, start a new record.
The count of user records query uses two seperate tables in the same query
to determine if the record count. Running it through VB, it expects a
parameter value for the criteria i've set, since its not: concurrently
running, can't be determined without checking the row value determined by the
other query (user_sn_to...), I suppose, or explicitly defined. What else
can i provide that might help you see what i'm trying to accomplish, and the
simplest way to do it?
I'd also been advised to try a make-table query, to put the two values in
one table so that they could be easily read, but i can't compact on exit,
because i do not own the drive priveledges where the database is served, so i
would be left with lots of bloating :(
"Ken Sheridan" wrote:
A parameter is a variable which a query can use in a number of ways, but most
frequently it restricts the rows returned. This is what a simple prompt of
the type you are probably used to does, but it can also be a reference to a
control on a form. Take a look at the Sales by Year query in the sample
Northwind database which comes with Access; this includes the criterion on
the ShippedDate column:
Is Not Null And Between [Forms]![Sales by Year Dialog]![BeginningDate] And
[Forms]![Sales by Year Dialog]![EndingDate]
The references to the [Forms]![Sales by Year Dialog]![BeginningDate] and
[Forms]![Sales by Year Dialog]![EndingDate]controls are parameters. The
procedure I sent you will work with a query containing this type of parameter
because it can be evaluated by the Eval function. A simple prompt would not
work, however, because that cannot be evaluated. If you look at the Sales
by Year query in SQL view you'll notice that the parameters are declared as
DateTime at the start of the SQL statement. In my experience people often
omit to do this, but with dates its actually quite important as otherwise a
parameter value entered as a date in short date format could be
misinterpreted as an arithmetical expression.
I'm not sure what you have in mind when you say "its provided by a second
table". A value in a column in another table is not a parameter.
One common cause of a 'parameter expected' error is because users
incorporate a reference to a control on a form in a literal SQL string in
code, e.g "WHERE LastName = Forms!MyForm!txtLastName". What should be done
in a case like that is to concatenate the value of the control into the SQL
string; "WHERE LastName = """ & Forms!MyForm!txtLastName & """". That's not
the case with the SQL you posted, however. The most likely culprit is a
parameter in the UserIDQuery_SN_to_ProcName, and my function would handle
that. Sometimes the error is just a result of a simple typo. If a column
name is entered in the string incorrectly Access will treat it as a
parameter; essentially anything it doesn't recognize as an object is treated
as a parameter. However, you say that the query opens fine using the same
SQL as a query rather than in code, which suggests there is no typo.
The fact that its opening as a query does beg the question why you want to
build and open via VBA at all. Why not just save the query and open it with
the OpenQuery method?
A querydef object is an object which defines a query (its short for 'query
definition'). Saved queries are querydef objects in the current databases
querydefs collection. A querydef object does not have to be a saved query,
however. One can be temporarily created in code. This is commonly done and
its rows accessed by means of a recordset object created with the
OpenRecordset method of the querydef object. In my function a querydef
object is created with the CreateQueryDef method of the database object and
saved by giving it name. Its then opened in datasheet view with the
OpenQuery method and finally deleted from the current databases querydefs
collection. So its not quite as temporary as a true temporary querydef
object.
As a simple example paste the following procedure into a standard module:
Public Sub OpenRstFromQuery(strQuery As String)
Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim prm As DAO.Parameter, rst As DAO.Recordset
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs(strQuery)
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset
With rst
Do While Not .EOF
Debug.Print .Fields(0); .Fields(1)
.MoveNext
Loop
End With
End Sub
Then in the debug window (aka immediate window) call it, passing it the name
of any simple query with at least two columns returned like so:
OpenRstFromQuery "NameOfQueryGoesHere"
You'll see that it lists the values from first two columns of the query to
the debug window.
Ken Sheridan
Stafford, England
"Pwyd" wrote:
oh wow. I understand what you're stating and the logic of it, but not all of
the code, i'll definately need some help with this. So the parameter i'm
speaking of is not one that pops up, requesting a parameter... its provided
by a second table, its never explicitly entered by the user. Does that
change anything? or perhaps i'm misunderstanding what you've stated.
Thanks for the info on the "having", i usually don't change the "group by"
unless i put in any criteria at all for that field, then i change it to a
where. What are the properties of a querydef object? what is its common
usage?
"Ken Sheridan" wrote:
You need to loop through the query's parameters collection and evaluate the
parameters, e.g. with a procedure like this:
Public Sub OpenTempQuery(strSQL As String)
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strQdf As String
Static n As Integer
Set dbs = CurrentDb
n = n + 1
strQdf = "qdfTemp" & n
' create temporary QueryDef object
Set qdf = dbs.CreateQueryDef(strQdf, strSQL)
' evaluate QueryDef object's parameters
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
' open temporary query
DoCmd.OpenQuery strQdf
' delete temporary QueryDef object
dbs.QueryDefs.Delete qdf.Name
End Sub
You'd then call it, passing the SQL statement into the procedure as a string
expression, e.g.
Dim strSQL As String
strSQL = " SELECT COUNT(*) AS CountOfRows " & _
"FROM ReversalMemo, UserIDQuery_SN_to_ProcName " & _
"WHERE ReversalMemo.Signee1 = " & _
"UserIDQuery_SN_to_ProcName.ProcessorName"
OpenTempQuery strSQL
The parameters must be something which can be evaluated such as a reference
to a control on an open form such as Forms!MyForm!MyComboBox, and not a
simple system generated parameter prompt such as [Enter name:].
BTW note that in the SQL statement a WHERE clause should be used not a
HAVING clause, even though the latter will have the same effect in this case.
The HAVING clause results in this instance from designing the query in query
design view without changing the 'total' from 'Group By' to 'Where'. A
HAVING clause is used to restrict a query's result set after grouping and
usually on the basis of some aggregated value, e.g. HAVING SUM(Sales) >=
10000 to restrict the result set to rows with total sales of 10,000 currency
units or more. To restrict before grouping a WHERE clause should be used.
Ken Sheridan
Stafford, England
"Pwyd" wrote:
So i discovered through time that when it states that it has "too few
parameters, expected #" that what it really means is that the query you're
trying to run depends on something else that also has parameters which, since
they aren't explicitly being stated at this time, are considered 'parameters
not provided.' At any rate, my question is, how do i explicitly state where
the query should look for those values? the query uses two seperate tables,
and it looks like this, in sql:
SELECT Count(ReversalMemo.RecordNum) AS CountOfRecordNum
FROM ReversalMemo, UserIDQuery_SN_to_ProcName
HAVING
(((ReversalMemo.Signee1)=[UserIDQuery_SN_to_ProcName].[ProcessorName]));
run on its own in query view, it works fine. in code in VB, not so good
How do i explicitly provide the "parameter" it needs, or must i "recreate"
the query itself by finding the other table's value myself and coding the
logic used to determine the countofrecordnum's value?
|
|
| |
| | | Pwyd | |
| | Posted: Thu Aug 28, 2008 11:52 am Post subject: RE: Coding a set of queries | |
| | | |
Ken, i'm not seeing the parameter list. The sub gets pasted in a module and
run with the call you stated. The call is pasted after the recordsets are
opened. Is this the correct placement?
"Ken Sheridan" wrote:
| Quote: | Your GetUserID function almost certainly calls the Widows API GetUserName
function, so I think we can rule that out as the mysterious parameter.
A query doesn't have to be open when the DLookup function (or any other
domain function such as DCount, DMax etc) is called.
If you create a recordset object based on a query then its Fields collection
is the set of columns returned by the query, so it does in effect "adopt the
field names". You cannot refer directly to the value of a field returned by
a query; you either have to create a recordset object (as my sample
OpenRstFromQuery procedure does) or use one of the domain function such as
DLookup.
But we are getting away from the original problem of what is the expected
parameter. You should be able to list the parameters for the SQL statement
with the following function:
Public Sub ListParameters(strSQL As String)
Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("", strSQL)
For Each prm In qdf.Parameters
Debug.Print prm.Name
Next prm
End Sub
Call it like so:
ListParameters "SELECT * FROM CountOfUserExistingRecords"
Any parameters expected by the CountOfUserExistingRecords query should be
listed to the debug window. This should point you in the direction of the
cause of the problem, and hopefully to a solution.
Ken Sheridan
Stafford, England
"Pwyd" wrote:
i'm using a GetUserID() public function built with the help of the users
here, and yes i believe it uses the windows API.
Does dlookup instantiate the query on its own, or does it require the query
is running when it is called?
as for the column name, yes, that is how its referred to by me, i was
unsure if that was correct. Does the recordset object adopt the field names
of the thing it contains, or is it still proper to refer to the name of the
query itself thats being run?
In addition, is the way i've contstructed it improper, or is dlookup just
more efficient, faster, etc?
"Ken Sheridan" wrote:
How are you getting the current user's login name for the query? If you are
calling the Windows API GetUserName function that's not a parameter so should
be OK, but if you are getting it in some other way its possible it could be
being treated as a parameter.
Rather than creating recordset objects why not use the DLookup function to
get the value of any non-Null column from a query? Test for
IsNull(DLookup("ColumnName","QueryName")) to identify no rows returned, and
for DLookup("ColumnName","QueryName") = 0 to identify where the Count
operator returns a 0.
BTW don't use recordcount as an object variable's name; it’s a property name
in both DAO and ADO so should be avoided.
I also note that you refer to recordCount!RecordNum <= 0, but in the SQL
statement the column name is CountOfRecordNum.
Ken Sheridan
Stafford, England
"Pwyd" wrote:
I've had the same trouble with the openquery method. Perhaps we could try
that instead. Here, let me post the code, and yes, the query i'm trying to
run does refer to a value based on a second table. Here:
Public Function testfunction()
Dim adminQuery As Recordset
Dim processorQuery As Recordset
Dim recordCount As Recordset
Set adminQuery = CurrentDb.OpenRecordset("IsAdminQuery")
Set processorQuery = CurrentDb.OpenRecordset("IsProcessorQuery")
'Set recordCount = CurrentDb.OpenRecordset("CountOfUserExistingRecords")
If (adminQuery.BOF And adminQuery.EOF) Then
If (processorQuery.BOF And processorQuery.EOF) Then
DoCmd.Close
ElseIf (recordCount!RecordNum <= 0) Then
DoCmd.OpenForm ("Costpoint Processor New Record")
Else: DoCmd.OpenForm ("ReversalCostpoint Processor")
End If
Else: DoCmd.OpenForm ("Costpoint Reversals")
End If
End Function
The count of user existing records query is the problem. It looks like this:
SELECT Count(ReversalMemo.RecordNum) AS CountOfRecordNum
FROM ReversalMemo, UserIDQuery_SN_to_ProcName
HAVING
(((ReversalMemo.Signee1)=[UserIDQuery_SN_to_ProcName].[ProcessorName]));
The user_sn... query produces a user's true name based on a shortname from
their login. It's later used to filter their records. The purpose of this
particular part of the function is, if the user is listed as a processor, but
has 0 records (null or 0 result) the form opens up the new record form
instead of the processor form, which would be a blank white page since its
not for data entry.
Its basically a very simple decision tree. Is the user an admin? if yes,
open admin version. if no, is the user a processor? if yes, open the
processor form, showing them only their own records. if no, close. if the
processor is valid but has no records, start a new record.
The count of user records query uses two seperate tables in the same query
to determine if the record count. Running it through VB, it expects a
parameter value for the criteria i've set, since its not: concurrently
running, can't be determined without checking the row value determined by the
other query (user_sn_to...), I suppose, or explicitly defined. What else
can i provide that might help you see what i'm trying to accomplish, and the
simplest way to do it?
I'd also been advised to try a make-table query, to put the two values in
one table so that they could be easily read, but i can't compact on exit,
because i do not own the drive priveledges where the database is served, so i
would be left with lots of bloating :(
"Ken Sheridan" wrote:
A parameter is a variable which a query can use in a number of ways, but most
frequently it restricts the rows returned. This is what a simple prompt of
the type you are probably used to does, but it can also be a reference to a
control on a form. Take a look at the Sales by Year query in the sample
Northwind database which comes with Access; this includes the criterion on
the ShippedDate column:
Is Not Null And Between [Forms]![Sales by Year Dialog]![BeginningDate] And
[Forms]![Sales by Year Dialog]![EndingDate]
The references to the [Forms]![Sales by Year Dialog]![BeginningDate] and
[Forms]![Sales by Year Dialog]![EndingDate]controls are parameters. The
procedure I sent you will work with a query containing this type of parameter
because it can be evaluated by the Eval function. A simple prompt would not
work, however, because that cannot be evaluated. If you look at the Sales
by Year query in SQL view you'll notice that the parameters are declared as
DateTime at the start of the SQL statement. In my experience people often
omit to do this, but with dates its actually quite important as otherwise a
parameter value entered as a date in short date format could be
misinterpreted as an arithmetical expression.
I'm not sure what you have in mind when you say "its provided by a second
table". A value in a column in another table is not a parameter.
One common cause of a 'parameter expected' error is because users
incorporate a reference to a control on a form in a literal SQL string in
code, e.g "WHERE LastName = Forms!MyForm!txtLastName". What should be done
in a case like that is to concatenate the value of the control into the SQL
string; "WHERE LastName = """ & Forms!MyForm!txtLastName & """". That's not
the case with the SQL you posted, however. The most likely culprit is a
parameter in the UserIDQuery_SN_to_ProcName, and my function would handle
that. Sometimes the error is just a result of a simple typo. If a column
name is entered in the string incorrectly Access will treat it as a
parameter; essentially anything it doesn't recognize as an object is treated
as a parameter. However, you say that the query opens fine using the same
SQL as a query rather than in code, which suggests there is no typo.
The fact that its opening as a query does beg the question why you want to
build and open via VBA at all. Why not just save the query and open it with
the OpenQuery method?
A querydef object is an object which defines a query (its short for 'query
definition'). Saved queries are querydef objects in the current databases
querydefs collection. A querydef object does not have to be a saved query,
however. One can be temporarily created in code. This is commonly done and
its rows accessed by means of a recordset object created with the
OpenRecordset method of the querydef object. In my function a querydef
object is created with the CreateQueryDef method of the database object and
saved by giving it name. Its then opened in datasheet view with the
OpenQuery method and finally deleted from the current databases querydefs
collection. So its not quite as temporary as a true temporary querydef
object.
As a simple example paste the following procedure into a standard module:
Public Sub OpenRstFromQuery(strQuery As String)
Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim prm As DAO.Parameter, rst As DAO.Recordset
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs(strQuery)
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset
With rst
Do While Not .EOF
Debug.Print .Fields(0); .Fields(1)
.MoveNext
Loop
End With
End Sub
Then in the debug window (aka immediate window) call it, passing it the name
of any simple query with at least two columns returned like so:
OpenRstFromQuery "NameOfQueryGoesHere"
You'll see that it lists the values from first two columns of the query to
the debug window.
Ken Sheridan
Stafford, England
"Pwyd" wrote:
oh wow. I understand what you're stating and the logic of it, but not all of
the code, i'll definately need some help with this. So the parameter i'm
speaking of is not one that pops up, requesting a parameter... its provided
by a second table, its never explicitly entered by the user. Does that
change anything? or perhaps i'm misunderstanding what you've stated.
Thanks for the info on the "having", i usually don't change the "group by"
unless i put in any criteria at all for that field, then i change it to a
where. What are the properties of a querydef object? what is its common
usage?
"Ken Sheridan" wrote:
You need to loop through the query's parameters collection and evaluate the
parameters, e.g. with a procedure like this:
Public Sub OpenTempQuery(strSQL As String)
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strQdf As String
Static n As Integer
Set dbs = CurrentDb
n = n + 1
strQdf = "qdfTemp" & n
' create temporary QueryDef object
Set qdf = dbs.CreateQueryDef(strQdf, strSQL)
' evaluate QueryDef object's parameters
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
' open temporary query
DoCmd.OpenQuery strQdf
' delete temporary QueryDef object
dbs.QueryDefs.Delete qdf.Name
End Sub
You'd then call it, passing the SQL statement into the procedure as a string
expression, e.g.
Dim strSQL As String
strSQL = " SELECT COUNT(*) AS CountOfRows " & _
"FROM ReversalMemo, UserIDQuery_SN_to_ProcName " & _
"WHERE ReversalMemo.Signee1 = " & _
"UserIDQuery_SN_to_ProcName.ProcessorName"
OpenTempQuery strSQL
The parameters must be something which can be evaluated such as a reference
to a control on an open form such as Forms!MyForm!MyComboBox, and not a
simple system generated parameter prompt such as [Enter name:].
BTW note that in the SQL statement a WHERE clause should be used not a
HAVING clause, even though the latter will have the same effect in this case. |
|
| |
| | | Pwyd | |
| | Posted: Thu Aug 28, 2008 12:00 pm Post subject: RE: Coding a set of queries | |
| | | |
All right, i've got it. the Immediate window wasn't displayed. it's looking
for the following:
Forms!UserIDHiddenForm!Text0
which is a call to the GetUserID() function, which simply returns the
shortname of the user. the shortname gets checked on a table to see whether
it exists. So how do i feed that into Countofuserexistingrecords... as a
parameter so that it has what it needs to complete its recordset?
Incidentally, if i understood correctly, using dlookup would circumvent this
problem because it takes care of everything required to run the lookup?
"Ken Sheridan" wrote:
| Quote: | Your GetUserID function almost certainly calls the Widows API GetUserName
function, so I think we can rule that out as the mysterious parameter.
A query doesn't have to be open when the DLookup function (or any other
domain function such as DCount, DMax etc) is called.
If you create a recordset object based on a query then its Fields collection
is the set of columns returned by the query, so it does in effect "adopt the
field names". You cannot refer directly to the value of a field returned by
a query; you either have to create a recordset object (as my sample
OpenRstFromQuery procedure does) or use one of the domain function such as
DLookup.
But we are getting away from the original problem of what is the expected
parameter. You should be able to list the parameters for the SQL statement
with the following function:
Public Sub ListParameters(strSQL As String)
Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("", strSQL)
For Each prm In qdf.Parameters
Debug.Print prm.Name
Next prm
End Sub
Call it like so:
ListParameters "SELECT * FROM CountOfUserExistingRecords"
Any parameters expected by the CountOfUserExistingRecords query should be
listed to the debug window. This should point you in the direction of the
cause of the problem, and hopefully to a solution.
Ken Sheridan
Stafford, England
"Pwyd" wrote:
i'm using a GetUserID() public function built with the help of the users
here, and yes i believe it uses the windows API.
Does dlookup instantiate the query on its own, or does it require the query
is running when it is called?
as for the column name, yes, that is how its referred to by me, i was
unsure if that was correct. Does the recordset object adopt the field names
of the thing it contains, or is it still proper to refer to the name of the
query itself thats being run?
In addition, is the way i've contstructed it improper, or is dlookup just
more efficient, faster, etc?
"Ken Sheridan" wrote:
How are you getting the current user's login name for the query? If you are
calling the Windows API GetUserName function that's not a parameter so should
be OK, but if you are getting it in some other way its possible it could be
being treated as a parameter.
Rather than creating recordset objects why not use the DLookup function to
get the value of any non-Null column from a query? Test for
IsNull(DLookup("ColumnName","QueryName")) to identify no rows returned, and
for DLookup("ColumnName","QueryName") = 0 to identify where the Count
operator returns a 0.
BTW don't use recordcount as an object variable's name; it’s a property name
in both DAO and ADO so should be avoided.
I also note that you refer to recordCount!RecordNum <= 0, but in the SQL
statement the column name is CountOfRecordNum.
Ken Sheridan
Stafford, England
"Pwyd" wrote:
I've had the same trouble with the openquery method. Perhaps we could try
that instead. Here, let me post the code, and yes, the query i'm trying to
run does refer to a value based on a second table. Here:
Public Function testfunction()
Dim adminQuery As Recordset
Dim processorQuery As Recordset
Dim recordCount As Recordset
Set adminQuery = CurrentDb.OpenRecordset("IsAdminQuery")
Set processorQuery = CurrentDb.OpenRecordset("IsProcessorQuery")
'Set recordCount = CurrentDb.OpenRecordset("CountOfUserExistingRecords")
If (adminQuery.BOF And adminQuery.EOF) Then
If (processorQuery.BOF And processorQuery.EOF) Then
DoCmd.Close
ElseIf (recordCount!RecordNum <= 0) Then
DoCmd.OpenForm ("Costpoint Processor New Record")
Else: DoCmd.OpenForm ("ReversalCostpoint Processor")
End If
Else: DoCmd.OpenForm ("Costpoint Reversals")
End If
End Function
The count of user existing records query is the problem. It looks like this:
SELECT Count(ReversalMemo.RecordNum) AS CountOfRecordNum
FROM ReversalMemo, UserIDQuery_SN_to_ProcName
HAVING
(((ReversalMemo.Signee1)=[UserIDQuery_SN_to_ProcName].[ProcessorName]));
The user_sn... query produces a user's true name based on a shortname from
their login. It's later used to filter their records. The purpose of this
particular part of the function is, if the user is listed as a processor, but
has 0 records (null or 0 result) the form opens up the new record form
instead of the processor form, which would be a blank white page since its
not for data entry.
Its basically a very simple decision tree. Is the user an admin? if yes,
open admin version. if no, is the user a processor? if yes, open the
processor form, showing them only their own records. if no, close. if the
processor is valid but has no records, start a new record.
The count of user records query uses two seperate tables in the same query
to determine if the record count. Running it through VB, it expects a
parameter value for the criteria i've set, since its not: concurrently
running, can't be determined without checking the row value determined by the
other query (user_sn_to...), I suppose, or explicitly defined. What else
can i provide that might help you see what i'm trying to accomplish, and the
simplest way to do it?
I'd also been advised to try a make-table query, to put the two values in
one table so that they could be easily read, but i can't compact on exit,
because i do not own the drive priveledges where the database is served, so i
would be left with lots of bloating :(
"Ken Sheridan" wrote:
A parameter is a variable which a query can use in a number of ways, but most
frequently it restricts the rows returned. This is what a simple prompt of
the type you are probably used to does, but it can also be a reference to a
control on a form. Take a look at the Sales by Year query in the sample
Northwind database which comes with Access; this includes the criterion on
the ShippedDate column:
Is Not Null And Between [Forms]![Sales by Year Dialog]![BeginningDate] And
[Forms]![Sales by Year Dialog]![EndingDate]
The references to the [Forms]![Sales by Year Dialog]![BeginningDate] and
[Forms]![Sales by Year Dialog]![EndingDate]controls are parameters. The
procedure I sent you will work with a query containing this type of parameter
because it can be evaluated by the Eval function. A simple prompt would not
work, however, because that cannot be evaluated. If you look at the Sales
by Year query in SQL view you'll notice that the parameters are declared as
DateTime at the start of the SQL statement. In my experience people often
omit to do this, but with dates its actually quite important as otherwise a
parameter value entered as a date in short date format could be
misinterpreted as an arithmetical expression.
I'm not sure what you have in mind when you say "its provided by a second
table". A value in a column in another table is not a parameter.
One common cause of a 'parameter expected' error is because users
incorporate a reference to a control on a form in a literal SQL string in
code, e.g "WHERE LastName = Forms!MyForm!txtLastName". What should be done
in a case like that is to concatenate the value of the control into the SQL
string; "WHERE LastName = """ & Forms!MyForm!txtLastName & """". That's not
the case with the SQL you posted, however. The most likely culprit is a
parameter in the UserIDQuery_SN_to_ProcName, and my function would handle
that. Sometimes the error is just a result of a simple typo. If a column
name is entered in the string incorrectly Access will treat it as a
parameter; essentially anything it doesn't recognize as an object is treated
as a parameter. However, you say that the query opens fine using the same
SQL as a query rather than in code, which suggests there is no typo.
The fact that its opening as a query does beg the question why you want to
build and open via VBA at all. Why not just save the query and open it with
the OpenQuery method?
A querydef object is an object which defines a query (its short for 'query
definition'). Saved queries are querydef objects in the current databases
querydefs collection. A querydef object does not have to be a saved query,
however. One can be temporarily created in code. This is commonly done and
its rows accessed by means of a recordset object created with the
OpenRecordset method of the querydef object. In my function a querydef
object is created with the CreateQueryDef method of the database object and
saved by giving it name. Its then opened in datasheet view with the
OpenQuery method and finally deleted from the current databases querydefs
collection. So its not quite as temporary as a true temporary querydef
object.
As a simple example paste the following procedure into a standard module:
Public Sub OpenRstFromQuery(strQuery As String)
Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim prm As DAO.Parameter, rst As DAO.Recordset
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs(strQuery)
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset
With rst
Do While Not .EOF
Debug.Print .Fields(0); .Fields(1)
.MoveNext
Loop
End With
End Sub
Then in the debug window (aka immediate window) call it, passing it the name
of any simple query with at least two columns returned like so:
OpenRstFromQuery "NameOfQueryGoesHere"
You'll see that it lists the values from first two columns of the query to
the debug window.
Ken Sheridan
Stafford, England
"Pwyd" wrote:
oh wow. I understand what you're stating and the logic of it, but not all of
the code, i'll definately need some help with this. So the parameter i'm
speaking of is not one that pops up, requesting a parameter... its provided
by a second table, its never explicitly entered by the user. Does that
change anything? or perhaps i'm misunderstanding what you've stated.
Thanks for the info on the "having", i usually don't change the "group by"
unless i put in any criteria at all for that field, then i change it to a
where. What are the properties of a querydef object? what is its common
usage?
"Ken Sheridan" wrote:
You need to loop through the query's parameters collection and evaluate the
parameters, e.g. with a procedure like this:
Public Sub OpenTempQuery(strSQL As String)
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strQdf As String
Static n As Integer
Set dbs = CurrentDb
n = n + 1
strQdf = "qdfTemp" & n
' create temporary QueryDef object
Set qdf = dbs.CreateQueryDef(strQdf, strSQL)
' evaluate QueryDef object's parameters
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
' open temporary query
DoCmd.OpenQuery strQdf
' delete temporary QueryDef object
dbs.QueryDefs.Delete qdf.Name
End Sub
You'd then call it, passing the SQL statement into the procedure as a string
expression, e.g.
Dim strSQL As String
strSQL = " SELECT COUNT(*) AS CountOfRows " & _
"FROM ReversalMemo, UserIDQuery_SN_to_ProcName " & _
"WHERE ReversalMemo.Signee1 = " & _
"UserIDQuery_SN_to_ProcName.ProcessorName"
OpenTempQuery strSQL
The parameters must be something which can be evaluated such as a reference
to a control on an open form such as Forms!MyForm!MyComboBox, and not a
simple system generated parameter prompt such as [Enter name:].
BTW note that in the SQL statement a WHERE clause should be used not a
HAVING clause, even though the latter will have the same effect in this case. |
|
| |
| Page 1 of 2 .:. Goto page 1, 2 Next | |
|
|
|
