RowSource in VBA

[Guest]

I know how to concatenate a SQL string and make it the RowSource of a combo
box in VBA on the fly, but how would I pass just the Where clause from VBA to
an existing query. (This is because the query is used in more places than
just this row source.)

Currently:

Dim strSelect as String
Dim strWhere as String
strSelect = "SELECT blah blah blah FROM blah blah blah"
strWhere = "WHERE blah blah blah = blah blah blah"
combo1.RowSource =strSelect & strWhere

If I put the Select clause into a query, I can just do this:

combo1.RowSource = query1

How, though, would I combine the two: use the query name for the Select
clause but pass the Where clause from VBA, since that is the only part that
needs to be dynamically altered?

 

[Marshall Barton]

I know how to concatenate a SQL string and make it the RowSource of a combo
box in VBA on the fly, but how would I pass just the Where clause from VBA to
an existing query. (This is because the query is used in more places than
just this row source.)

Currently:

Dim strSelect as String
Dim strWhere as String
strSelect = "SELECT blah blah blah FROM blah blah blah"
strWhere = "WHERE blah blah blah = blah blah blah"
combo1.RowSource =strSelect & strWhere

If I put the Select clause into a query, I can just do this:

combo1.RowSource = query1

How, though, would I combine the two: use the query name for the Select
clause but pass the Where clause from VBA, since that is the only part that
needs to be dynamically altered?

I think I might just use a Public Const for the SELECT part
so any procedure in your app can get to it.

In a standard module:
Public Const strSelect As String ="SELECT blah FROM blah"

and where ever you need to use it:
strWhere = "WHERE blah blah blah = blah blah blah"
combo1.RowSource =strSelect & strWhere


If you relly prefer to put the select in a saved query, then
the code to use would be:

Dim strSelect as String
Dim strWhere as String
strSelect = CurrentDb.QueryDefs!query1.SQL
. . .

 

[Guest]

Thanks, Marshall. This particular example is used as the RowSource for a
record-selector combo box on a form as well as the RecordSource for a report
that prints the same information.

I can see how it will work to base the report's RecordSource on the query
and pull the SQL from the query for the RowSource of the combo box. Do you
think it would be superior to instead pull both the report's RecordSource and
the control's RowSource from the Public Const? Will there be any
complications pulling the report's RecordSource from the Public Const while
sending the Where clause in from VBA in DoCmd.OpenReport?

 

[Guest]

One more note that I omitted from my prior post: I like to pre-check the
report to ensure it has records before opening it, and present a "No records
found" message rather than a report with errors on it because no records were
found.

The simplest way I know is to count the records in the RecordSource query
before running the report. Would this lend some impetus to using a query, or
can you suggest a way to use the Public Const select to accomplish this part?

 

[Marshall Barton]

I take it back Brian, both of those uses are better off
using a saved query. The report's record source can be set
to the saved query and the report can be opened and filtered
by the where clause (without the word Where) only if it will
have some data:

strWhere = "blah blah blah = blah blah blah"
If DCount("*", "query1", strWhere) > 0 Then
DoCmd.OpenReport "reportname", , , strWhere
Else
MsgBox "There is no data matching " & strWhere--
Marsh
MVP [MS Access]
End If

 

[Guest]

Thanks. I'm still stuck on one point. I am having to truncate the strSelect
resulting from the .SQL below by three characters:

Dim strSelect As String
Dim strOrderBy As String
strOrderBy = " ORDER BY stuff"

strWhere = " Where 1=1" 'ensures there is a strWhere to which to append
further ...and... phrases
strSelect = CurrentDb.QueryDefs("qryName").SQL
strSelect = Left(strSelect, Len(strSelect) - 3)
SetstrWhere 'this sub appends " and..." phrases to strWhere based on user
filter choices
Selector.RowSource = strSelect & strWhere & strOrderBy

It works like this:
Selector.RowSource = strSelect

but as soon as I add the Where & OrderBy clauses, it fails unless I perform
the three-character truncation. I understand that it could have something to
do with the trailing semicolon coming from the .sql, but why three
characters, and not one?

When I set it up as a public constant, it works fine without truncation, but
I lose most of the flexibility we are addressing here.

Am I making this too complex? I am just trying to give the users a little
report that shows the same records that appear in the RowSource of the form's
record selector, based on their choices in a number of combo boxes used as
filters (the SetstrWhere Sub looks at each of these and appends another " and
...." phrase to strWhere if the user selected the particular filter).

I take it back Brian, both of those uses are better off
using a saved query. The report's record source can be set
to the saved query and the report can be opened and filtered
by the where clause (without the word Where) only if it will
have some data:

strWhere = "blah blah blah = blah blah blah"
If DCount("*", "query1", strWhere) > 0 Then
DoCmd.OpenReport "reportname", , , strWhere
Else
MsgBox "There is no data matching " & strWhere--
Marsh
MVP [MS Access]
End If
--
Marsh
MVP [MS Access]

One more note that I omitted from my prior post: I like to pre-check the
report to ensure it has records before opening it, and present a "No records
found" message rather than a report with errors on it because no records were
found.

The simplest way I know is to count the records in the RecordSource query
before running the report. Would this lend some impetus to using a query, or
can you suggest a way to use the Public Const select to accomplish this part?

 

[Marshall Barton]

I never noticed that before. There seems to be a new line
after the semicolon. Not sure I want to rely on that so how
about:

strSelect = Left(strSelect, InStr(strSelect, ";") - 1)

I don't think you're making it too complex for for your
stated goal, but then I may or may not like the UI you've
designed until I saw it in action.

Note that putting the WHERE 1=1 in the query is not
productive. I would leave it out and append the word WHERE
along with the appropriate criteria expression. When you
build the expression, you can add a final step to remove the
initial AND:
If Len(strWhere) > 0 Then
strWhere = "WHERE & Mid(strWhere, 6)
End If

 

[Guest]

Thank you so much. This did exactly what I needed while condensing &
simplifying my code.

I never noticed that before. There seems to be a new line
after the semicolon. Not sure I want to rely on that so how
about:

strSelect = Left(strSelect, InStr(strSelect, ";") - 1)

I don't think you're making it too complex for for your
stated goal, but then I may or may not like the UI you've
designed until I saw it in action.

Note that putting the WHERE 1=1 in the query is not
productive. I would leave it out and append the word WHERE
along with the appropriate criteria expression. When you
build the expression, you can add a final step to remove the
initial AND:
If Len(strWhere) > 0 Then
strWhere = "WHERE & Mid(strWhere, 6)
End If
--
Marsh
MVP [MS Access]

Thanks. I'm still stuck on one point. I am having to truncate the strSelect
resulting from the .SQL below by three characters:

Dim strSelect As String
Dim strOrderBy As String
strOrderBy = " ORDER BY stuff"

strWhere = " Where 1=1" 'ensures there is a strWhere to which to append
further ...and... phrases
strSelect = CurrentDb.QueryDefs("qryName").SQL
strSelect = Left(strSelect, Len(strSelect) - 3)
SetstrWhere 'this sub appends " and..." phrases to strWhere based on user
filter choices
Selector.RowSource = strSelect & strWhere & strOrderBy

It works like this:
Selector.RowSource = strSelect

but as soon as I add the Where & OrderBy clauses, it fails unless I perform
the three-character truncation. I understand that it could have something to
do with the trailing semicolon coming from the .sql, but why three
characters, and not one?

When I set it up as a public constant, it works fine without truncation, but
I lose most of the flexibility we are addressing here.

Am I making this too complex? I am just trying to give the users a little
report that shows the same records that appear in the RowSource of the form's
record selector, based on their choices in a number of combo boxes used as
filters (the SetstrWhere Sub looks at each of these and appends another " and
..." phrase to strWhere if the user selected the particular filter).

 

[Dirk Goldgar]

I never noticed that before. There seems to be a new line
after the semicolon. Not sure I want to rely on that so how
about:

strSelect = Left(strSelect, InStr(strSelect, ";") - 1)

How about using InStrRev to search for the terminal semicolon, just in
case there's another one in a literal or (God forbid) a field name
somewhere?

 

[Guest]

Good thought. Thanks. While I am quite certain neither of those would ever
happen, it does just seem like good insurance to search from the end.

 

[Marshall Barton]

"Marshall Barton" wrote

How about using InStrRev to search for the terminal semicolon, just in
case there's another one in a literal or (God forbid) a field name
somewhere?

Absolutely right Dirk. I knew there was something nagging
at me about what I wrote.