RowSource in VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brian - 24 Sep 2005 19:15 GMT

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?

Reply to this Message

Marshall Barton - 24 Sep 2005 20:26 GMT

>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
[quoted text clipped - 16 lines]
>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
. . .

Signature

Marsh
MVP [MS Access]

Reply to this Message

Brian - 24 Sep 2005 21:13 GMT

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?

> >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
[quoted text clipped - 34 lines]
> strSelect = CurrentDb.QueryDefs!query1.SQL
> . . .

Reply to this Message

Brian - 24 Sep 2005 21:17 GMT

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?

Reply to this Message

Marshall Barton - 24 Sep 2005 23:34 GMT

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

Signature

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
[quoted text clipped - 43 lines]
>> strSelect = CurrentDb.QueryDefs!query1.SQL
>> . . .

Reply to this Message

Brian - 25 Sep 2005 00:13 GMT

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
[quoted text clipped - 57 lines]
> >> strSelect = CurrentDb.QueryDefs!query1.SQL
> >> . . .

Reply to this Message

Marshall Barton - 25 Sep 2005 01:18 GMT

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

Signature

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:
[quoted text clipped - 88 lines]
>> >> strSelect = CurrentDb.QueryDefs!query1.SQL
>> >> . . .

Reply to this Message

Brian - 25 Sep 2005 01:36 GMT

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
[quoted text clipped - 106 lines]
> >> >> strSelect = CurrentDb.QueryDefs!query1.SQL
> >> >> . . .

Reply to this Message

Dirk Goldgar - 25 Sep 2005 02:03 GMT

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

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Brian - 25 Sep 2005 02:21 GMT

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.

> > 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
[quoted text clipped - 5 lines]
> case there's another one in a literal or (God forbid) a field name
> somewhere?

Reply to this Message

Marshall Barton - 25 Sep 2005 05:04 GMT

>> 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
[quoted text clipped - 5 lines]
>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.

Signature

Marsh
MVP [MS Access]

Reply to this Message