Advanced Queries

[Woodies_46@hotmail.com]

Hi all,

I'm just a little bit stuck... what i have is a form with tick boxs and
text boxs and stuff like that on it and a search button.

What I would like to be able to do is for the user to tick and fill in
some of the spaces and when they hit seacrh i would like it to do is
only show those colums that the has selected and if they don't select
anything it shows a message saying "Please select a box"

If it is possible, that would be great

Thanks

[DFS]

Re: Advanced Queries

Woodies_46@hotm ail.com wrote:[color=blue]
> Hi all,
>
> I'm just a little bit stuck... what i have is a form with tick boxs
> and text boxs and stuff like that on it and a search button.
>
> What I would like to be able to do is for the user to tick and fill in
> some of the spaces and when they hit seacrh i would like it to do is
> only show those colums that the has selected and if they don't select
> anything it shows a message saying "Please select a box"
>
> If it is possible, that would be great
>
> Thanks[/color]

To make it easier, name the screen controls the same as the underlying
columns. Then iterate the controls on the screen. Attach code to your
Search button, something like this:

'VARS
dim sqlStr as string, ctl as Control
sqlStr = ""

'ITERATE CONTROLS
for each ctl in Me.Controls
if ctl.ControlType = acCheckBox then
if ctl = True then
sqlStr = sqlStr & ctl.Name & ", "
endif
elseif ctl.ControlType = acTextBox then
if not isnull(ctl) then
sqlStr = sqlStr & ctl.Name & ", "
endif
endif
next ctl

'NO CONTROLS CHOSEN OR COMPLETED
if sqlStr = "" then
msgbox "Please select a box",,"System Name"
exit sub
endif

'TRIM AND DROP TRAILING COMMA
sqlStr = trim(sqlStr)
sqlStr = left(sqlStr,len (sqlStr)-1)

'SHOW DATA
sqlStr = "SELECT " & sqlStr & " FROM Table WHERE blah blah;
Me.Subform.Reco rdSource = sqlStr


Note: this code doesn't build any WHERE criteria into the SQL statement - it
just checks if the control is True or Not Null.

[Larry Linson]

Re: Advanced Queries

That can be very confusing. I'd suggest naming the Control to the name of
the underlying Field plus a prefix "ctl" for Control.

I use a more granular form of the Reddick Naming Convention, but if you use
"ctl" it will prevent the confusion of having both the underlying field and
displaying control having the same name, and be easier to use in code.

Larry Linson
Microsoft Access MVP


"DFS" <nospam@dfs_.co m> wrote in message
news:LR%Tf.772$ qe.62@bignews1. bellsouth.net.. .[color=blue]
> Woodies_46@hotm ail.com wrote:[color=green]
>> Hi all,
>>
>> I'm just a little bit stuck... what i have is a form with tick boxs
>> and text boxs and stuff like that on it and a search button.
>>
>> What I would like to be able to do is for the user to tick and fill in
>> some of the spaces and when they hit seacrh i would like it to do is
>> only show those colums that the has selected and if they don't select
>> anything it shows a message saying "Please select a box"
>>
>> If it is possible, that would be great
>>
>> Thanks[/color]
>
> To make it easier, name the screen controls the same as the underlying
> columns. Then iterate the controls on the screen. Attach code to your
> Search button, something like this:
>
> 'VARS
> dim sqlStr as string, ctl as Control
> sqlStr = ""
>
> 'ITERATE CONTROLS
> for each ctl in Me.Controls
> if ctl.ControlType = acCheckBox then
> if ctl = True then
> sqlStr = sqlStr & ctl.Name & ", "
> endif
> elseif ctl.ControlType = acTextBox then
> if not isnull(ctl) then
> sqlStr = sqlStr & ctl.Name & ", "
> endif
> endif
> next ctl
>
> 'NO CONTROLS CHOSEN OR COMPLETED
> if sqlStr = "" then
> msgbox "Please select a box",,"System Name"
> exit sub
> endif
>
> 'TRIM AND DROP TRAILING COMMA
> sqlStr = trim(sqlStr)
> sqlStr = left(sqlStr,len (sqlStr)-1)
>
> 'SHOW DATA
> sqlStr = "SELECT " & sqlStr & " FROM Table WHERE blah blah;
> Me.Subform.Reco rdSource = sqlStr
>
>
> Note: this code doesn't build any WHERE criteria into the SQL statement -
> it
> just checks if the control is True or Not Null.
>
>
>[/color]

[DFS]

Re: Advanced Queries

Larry Linson wrote:[color=blue]
> That can be very confusing.[/color]

Why?

[color=blue]
> I'd suggest naming the Control to the
> name of the underlying Field plus a prefix "ctl" for Control.[/color]

Access defaults form control names to the column names, so unless he created
the form with no recordsource it already had the names set.

[color=blue]
> I use a more granular form of the Reddick Naming Convention, but if
> you use "ctl" it will prevent the confusion of having both the
> underlying field and displaying control having the same name, and be
> easier to use in code.[/color]

You mean harder to use. You would have to strip off "ctl" each time you
wanted to substitute the control name into the SQL.




[color=blue]
> Larry Linson
> Microsoft Access MVP
>
>
> "DFS" <nospam@dfs_.co m> wrote in message
> news:LR%Tf.772$ qe.62@bignews1. bellsouth.net.. .[color=green]
>> Woodies_46@hotm ail.com wrote:[color=darkred]
>>> Hi all,
>>>
>>> I'm just a little bit stuck... what i have is a form with tick boxs
>>> and text boxs and stuff like that on it and a search button.
>>>
>>> What I would like to be able to do is for the user to tick and fill
>>> in some of the spaces and when they hit seacrh i would like it to
>>> do is only show those colums that the has selected and if they
>>> don't select anything it shows a message saying "Please select a
>>> box"
>>>
>>> If it is possible, that would be great
>>>
>>> Thanks[/color]
>>
>> To make it easier, name the screen controls the same as the
>> underlying columns. Then iterate the controls on the screen.
>> Attach code to your Search button, something like this:
>>
>> 'VARS
>> dim sqlStr as string, ctl as Control
>> sqlStr = ""
>>
>> 'ITERATE CONTROLS
>> for each ctl in Me.Controls
>> if ctl.ControlType = acCheckBox then
>> if ctl = True then
>> sqlStr = sqlStr & ctl.Name & ", "
>> endif
>> elseif ctl.ControlType = acTextBox then
>> if not isnull(ctl) then
>> sqlStr = sqlStr & ctl.Name & ", "
>> endif
>> endif
>> next ctl
>>
>> 'NO CONTROLS CHOSEN OR COMPLETED
>> if sqlStr = "" then
>> msgbox "Please select a box",,"System Name"
>> exit sub
>> endif
>>
>> 'TRIM AND DROP TRAILING COMMA
>> sqlStr = trim(sqlStr)
>> sqlStr = left(sqlStr,len (sqlStr)-1)
>>
>> 'SHOW DATA
>> sqlStr = "SELECT " & sqlStr & " FROM Table WHERE blah blah;
>> Me.Subform.Reco rdSource = sqlStr
>>
>>
>> Note: this code doesn't build any WHERE criteria into the SQL
>> statement - it
>> just checks if the control is True or Not Null.[/color][/color]

[Larry Linson]

Re: Advanced Queries


"DFS" <nospam@dfs_.co m> wrote in message
news:R24Uf.2226 $lM3.372@bignew s3.bellsouth.ne t...[color=blue]
> Larry Linson wrote:[color=green]
>> That can be very confusing.[/color]
>
> Why?[/color]

Have you never encountered a situation where you tried to use a Control that
the Wizard had named the same as its Control Source and gotten the result
#Name, meaning "Access can't determine what you are trying to reference"? It
can happen.

And, Access will determine, as best it can, which to use in other
situations. And, of course, Access always is correct, but sometimes _we mere
mortals_ assumed it would use the other.
[color=blue][color=green]
>> I'd suggest naming the Control to the
>> name of the underlying Field plus a
>> prefix "ctl" for Control.[/color]
>
> Access defaults form control names to the
> column names, so unless he created
> the form with no recordsource it already
> had the names set.[/color]

Creating the Wizard to use the same name for the Control as already used in
the ControlSource was not One of The Best Design Decisions Ever Made in
Redmond, Washington, USA, although when it rises up to bite you in the
tender places, you may think it One of the More Memorable Design Decisions
Ever Made in Redmond, Washington, USA.
[color=blue][color=green]
>> I use a more granular form of the Reddick
>> Naming Convention, but if you use "ctl" it
>> will prevent the confusion of having both the
>> underlying field and displaying control having
>> the same name, and be easier to use in code.[/color]
>
> You mean harder to use. You would have
> to strip off "ctl" each time you wanted to
> substitute the control name into the SQL.[/color]

It's not polite to tell someone what they mean. No, I have already ruled out
using the Control Source as the name of the Control, so I mean "easier to
use than a more granular form of the Reddick Naming Convention".

Larry Linson
Microsoft Access MVP

[DFS]

Re: Advanced Queries

Larry Linson wrote:[color=blue]
> "DFS" <nospam@dfs_.co m> wrote in message
> news:R24Uf.2226 $lM3.372@bignew s3.bellsouth.ne t...[color=green]
>> Larry Linson wrote:[color=darkred]
>>> That can be very confusing.[/color]
>>
>> Why?[/color]
>
> Have you never encountered a situation where you tried to use a
> Control that the Wizard had named the same as its Control Source and
> gotten the result #Name, meaning "Access can't determine what you are
> trying to reference"? It can happen.[/color]

I sometimes see #Name? when viewing bound forms with no datasource, but that
doesn't have anything to do with the name of the control vs. the column.
Does it?

[color=blue]
> And, Access will determine, as best it can, which to use in other
> situations. And, of course, Access always is correct, but sometimes
> _we mere mortals_ assumed it would use the other.[/color]

[color=blue][color=green][color=darkred]
> >> I'd suggest naming the Control to the
> >> name of the underlying Field plus a
> >> prefix "ctl" for Control.[/color]
> >
> > Access defaults form control names to the
> > column names, so unless he created
> > the form with no recordsource it already
> > had the names set.[/color]
>
> Creating the Wizard to use the same name for the Control as already
> used in the ControlSource was not One of The Best Design Decisions
> Ever Made in Redmond, Washington, USA, although when it rises up to
> bite you in the tender places, you may think it One of the More
> Memorable Design Decisions Ever Made in Redmond, Washington, USA.[/color]

It hasn't caused me any problems. I like that design, actually.


[color=blue][color=green][color=darkred]
> >> I use a more granular form of the Reddick
> >> Naming Convention, but if you use "ctl" it
> >> will prevent the confusion of having both the
> >> underlying field and displaying control having
> >> the same name, and be easier to use in code.[/color]
> >
> > You mean harder to use. You would have
> > to strip off "ctl" each time you wanted to
> > substitute the control name into the SQL.[/color]
>
> It's not polite to tell someone what they mean.[/color]

Nor is it polite to tell someone their solution "can be very confusing" and
that your solution is "easier to use in code".





[color=blue]
> No, I have already
> ruled out using the Control Source as the name of the Control, so I
> mean "easier to use than a more granular form of the Reddick Naming
> Convention".
>
> Larry Linson
> Microsoft Access MVP[/color]

[Woodies_46@hotmail.com]

Re: Advanced Queries

Ok Guys I tried the code that DFS gave me.. and changed
ME.Subform.reco rdsource to Me.resultssubfo rm.SourceObject and now i'm
getting this error that displays "The form name you entered doesn't
follow Microsoft Access object-naming rules"... I don't understand why.

Could some one please help

[Lyle Fairfield]

Re: Advanced Queries

"DFS" <nospam@dfs_.co m> wrote in
news:LR%Tf.772$ qe.62@bignews1. bellsouth.net:
[color=blue]
> To make it easier, name the screen controls the same as the underlying
> columns.[/color]

This is bad advice and wrong.

Having two objects with the same name can be confusing to the developer,
his/her colleagues and to the database and its code. The practice is very
bad form and can lead to errors of logic and to run-time errors as well.

The fact that Access wizards default to this behaviour only underlies that
it is amateur and inappropriate.

--
Lyle Fairfield

[DFS]

Re: Advanced Queries

Lyle Fairfield wrote:[color=blue]
> "DFS" <nospam@dfs_.co m> wrote in
> news:LR%Tf.772$ qe.62@bignews1. bellsouth.net:
>[color=green]
>> To make it easier, name the screen controls the same as the
>> underlying columns.[/color]
>
> This is bad advice and wrong.
>
> Having two objects with the same name can be confusing to the
> developer, his/her colleagues and to the database and its code. The
> practice is very bad form and can lead to errors of logic and to
> run-time errors as well.
>
> The fact that Access wizards default to this behaviour only underlies
> that it is amateur and inappropriate.[/color]

What it underlies is the fact that MS actually recommends this convention.
Microsoft vs Lyle Fairfield/Larry Linson? Let me think...

I don't recommend it for every case, but I've been using forms with controls
named after the columns in the datasource for 10+ years, in 100 deployed
systems, in 1,000,000+ lines of code, and NEVER have I had a problem with
errors of logic, or run-time, or compilation, or corruption, or anything
else that could be attributed to matching names. I must have created 2,000
forms (75% datasheets, 25% single or continuous) by starting with the
Autoform: Columnar/Tabular/Datasheet, which generates control names based on
the columns in the query or table. Sometimes they get renamed, sometimes
they don't. Either way, never a problem.

You would have to be mighty dense to get confused (or to write code that
gets confused) between a form text box named STATUS_ID and a column named
STATUS_ID. And I think you're not dense, so why say it?

[Keith Wilby]

Re: Advanced Queries

"DFS" <nospam@dfs_.co m> wrote in message
news:g7aUf.487$ Pe.435@bignews6 .bellsouth.net. ..[color=blue]
> Lyle Fairfield wrote:[color=green]
>> "DFS" <nospam@dfs_.co m> wrote in
>> news:LR%Tf.772$ qe.62@bignews1. bellsouth.net:
>>[color=darkred]
>>> To make it easier, name the screen controls the same as the
>>> underlying columns.[/color]
>>
>> This is bad advice and wrong.
>>
>> Having two objects with the same name can be confusing to the
>> developer, his/her colleagues and to the database and its code. The
>> practice is very bad form and can lead to errors of logic and to
>> run-time errors as well.
>>
>> The fact that Access wizards default to this behaviour only underlies
>> that it is amateur and inappropriate.[/color]
>
> What it underlies is the fact that MS actually recommends this convention.
> Microsoft vs Lyle Fairfield/Larry Linson? Let me think...
>
> I don't recommend it for every case, but I've been using forms with
> controls
> named after the columns in the datasource for 10+ years, in 100 deployed
> systems, in 1,000,000+ lines of code, and NEVER have I had a problem with
> errors of logic, or run-time, or compilation, or corruption, or anything
> else that could be attributed to matching names. I must have created
> 2,000
> forms (75% datasheets, 25% single or continuous) by starting with the
> Autoform: Columnar/Tabular/Datasheet, which generates control names based
> on
> the columns in the query or table. Sometimes they get renamed, sometimes
> they don't. Either way, never a problem.
>
> You would have to be mighty dense to get confused (or to write code that
> gets confused) between a form text box named STATUS_ID and a column named
> STATUS_ID. And I think you're not dense, so why say it?
>
>
>[/color]

I'm with Lyle on this one. I recently had to overhaul an app that I coded
early on in my Access career and it was very difficult to tell what was what
because I hadn't used any prefixes such as "txt", "cbo" and the like. It's
no great hardship to use this convention and pays big dividends IMO. I wish
I'd started using it earlier.

Keith.

https://www.keithwilby.com

[Lyle Fairfield]

Re: Advanced Queries

DFS wrote:[color=blue]
> Lyle Fairfield wrote:[/color]
[color=blue]
> What it underlies is the fact that MS actually recommends this convention.
> Microsoft vs Lyle Fairfield/Larry Linson? Let me think...[/color]

It's a tough choice. I don't know whether I'd be too hot on the
Linson/Fairfield advice, (they're both old and Alzheimerish) but if MS
recommended something I'd be looking at it pretty skeptically.

BTW I have old code that goes through all my forms and renames controls
using prefixes like "txt" and "cbo" and specifically ensures that bound
controls do not have the same name as the field to which they are
bound.

I'm glad you have had not problems and perhaps I should review my
position on the matter. Since I don't make so many Access Forms
anymore that may be a while.

[DFS]

Re: Advanced Queries

Keith Wilby wrote:[color=blue]
> "DFS" <nospam@dfs_.co m> wrote in message
> news:g7aUf.487$ Pe.435@bignews6 .bellsouth.net. ..[color=green]
>> Lyle Fairfield wrote:[color=darkred]
>>> "DFS" <nospam@dfs_.co m> wrote in
>>> news:LR%Tf.772$ qe.62@bignews1. bellsouth.net:
>>>
>>>> To make it easier, name the screen controls the same as the
>>>> underlying columns.
>>>
>>> This is bad advice and wrong.
>>>
>>> Having two objects with the same name can be confusing to the
>>> developer, his/her colleagues and to the database and its code. The
>>> practice is very bad form and can lead to errors of logic and to
>>> run-time errors as well.
>>>
>>> The fact that Access wizards default to this behaviour only
>>> underlies that it is amateur and inappropriate.[/color]
>>
>> What it underlies is the fact that MS actually recommends this
>> convention. Microsoft vs Lyle Fairfield/Larry Linson? Let me
>> think...
>>
>> I don't recommend it for every case, but I've been using forms with
>> controls
>> named after the columns in the datasource for 10+ years, in 100
>> deployed systems, in 1,000,000+ lines of code, and NEVER have I had
>> a problem with errors of logic, or run-time, or compilation, or
>> corruption, or anything else that could be attributed to matching
>> names. I must have created 2,000
>> forms (75% datasheets, 25% single or continuous) by starting with the
>> Autoform: Columnar/Tabular/Datasheet, which generates control names
>> based on
>> the columns in the query or table. Sometimes they get renamed,
>> sometimes they don't. Either way, never a problem.
>>
>> You would have to be mighty dense to get confused (or to write code
>> that gets confused) between a form text box named STATUS_ID and a
>> column named STATUS_ID. And I think you're not dense, so why say it?
>>
>>
>>[/color]
>
> I'm with Lyle on this one. I recently had to overhaul an app that I
> coded early on in my Access career and it was very difficult to tell
> what was what because I hadn't used any prefixes such as "txt", "cbo"
> and the like.[/color]

Why did you have to overhaul it?

[color=blue]
> It's no great hardship to use this convention and pays
> big dividends IMO. I wish I'd started using it earlier.[/color]

Sometimes the prefixes help. On search screens I always use prefixes txt,
cbo, list, check and opt (with the remaining part of the name matching the
field I'm searching on).

I don't want to be misleading; I don't automatically name all my controls
the same as the columns (in fact, looking through my systems I see nearly
all the text boxes on my datasheets are renamed for user-friendliness:
STATUS_DESC becomes Status), but I have NEVER had a problem when I do use
matching names and write code around them.

And I think every report I create has text boxes matching column names.





[color=blue]
> Keith.
> www.keithwilby.com[/color]

[Rick Brandt]

Re: Advanced Queries

Keith Wilby wrote:[color=blue]
> I'm with Lyle on this one. I recently had to overhaul an app that I
> coded early on in my Access career and it was very difficult to tell
> what was what because I hadn't used any prefixes such as "txt", "cbo"
> and the like. It's no great hardship to use this convention and pays
> big dividends IMO. I wish I'd started using it earlier.
>
> Keith.
> www.keithwilby.com[/color]

I don't fault anyone for the convention they prefer, but I have to chime in on
the other side. I have never had a single issue in over 10 years creating
Access apps caused by having a control named the same as the field it was bound
to.

Does anyone have a reproducible sample situation where a conflict arises? And I
don't count controls having expressions in their ControlSource.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[DFS]

Re: Advanced Queries

Lyle Fairfield wrote:[color=blue]
> DFS wrote:[color=green]
>> Lyle Fairfield wrote:[/color]
>[color=green]
>> What it underlies is the fact that MS actually recommends this
>> convention. Microsoft vs Lyle Fairfield/Larry Linson? Let me
>> think...[/color]
>
> It's a tough choice. I don't know whether I'd be too hot on the
> Linson/Fairfield advice, (they're both old and Alzheimerish) but if MS
> recommended something I'd be looking at it pretty skeptically.[/color]

That's a little cynical (maybe a little exaggerated for your cdma
audience?), though in some respects not a bad position.

But if screen control=column name was a Really Bad Thing as some are
implying, the Access Autoforms wouldn't default to it.

Have you used OpenOffice 2.0 Base? It does the same thing. As I recall,
Borland Paradox for Windows did, too.


[color=blue]
> BTW I have old code that goes through all my forms and renames
> controls using prefixes like "txt" and "cbo" and specifically ensures
> that bound controls do not have the same name as the field to which
> they are bound.[/color]

Why? Any particular problems that you've observed that led you to write
such code.

BTW I have old code that goes through all my forms and renames controls and
specifically ensures that bound controls do have the same name as the field
to which they are bound. (LOL! I'm not kidding)


[color=blue]
> I'm glad you have had not problems and perhaps I should review my
> position on the matter. Since I don't make so many Access Forms
> anymore that may be a while.[/color]

Thank you for not continuing an argument. cdma should be a place of peace
and solitude and swift code...

[Lyle Fairfield]

Re: Advanced Queries

"Rick Brandt" <rickbrandt2@ho tmail.com> wrote in
news:bgbUf.4100 9$_S7.23066@new ssvr14.news.pro digy.com:
[color=blue]
> Does anyone have a reproducible sample situation where a conflict
> arises? And I don't count controls having expressions in their
> ControlSource.[/color]

LOL! I have to stop myself from saying,
"Well, No, but I remember a time when ...."
which totally pisses me off when it's a reply to me.

I know of no such reproducible problem.

--
Lyle Fairfield