Advanced Queries

[Woodies_46]

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]

Wo********@hotmail.com wrote:

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

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.RecordSource = 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]

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_.com> wrote in message
news:LR*************@bignews1.bellsouth.net...

Wo********@hotmail.com wrote:

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

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.RecordSource = 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.

[DFS]

Larry Linson wrote:

That can be very confusing.
Why?

I'd suggest naming the Control to the
name of the underlying Field plus a prefix "ctl" for Control.
Access defaults form control names to the column names, so unless he created
the form with no recordsource it already had the names set.

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.
You mean harder to use. You would have to strip off "ctl" each time you
wanted to substitute the control name into the SQL.


Larry Linson
Microsoft Access MVP
"DFS" <nospam@dfs_.com> wrote in message
news:LR*************@bignews1.bellsouth.net...

Wo********@hotmail.com wrote:

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

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.RecordSource = 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]

"DFS" <nospam@dfs_.com> wrote in message
news:R2****************@bignews3.bellsouth.net...

Larry Linson wrote:

That can be very confusing.

Why?

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.

I'd suggest naming the Control to the
name of the underlying Field plus a
prefix "ctl" for Control.

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

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.

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.

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

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]

Larry Linson wrote:

"DFS" <nospam@dfs_.com> wrote in message
news:R2****************@bignews3.bellsouth.net...

Larry Linson wrote:

That can be very confusing.
Why?

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.

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?

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.

>> I'd suggest naming the Control to the
>> name of the underlying Field plus a
>> prefix "ctl" for Control.

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

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.

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

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

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

It's not polite to tell someone what they mean.

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

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

[Woodies_46]

Ok Guys I tried the code that DFS gave me.. and changed
ME.Subform.recordsource to Me.resultssubform.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]

"DFS" <nospam@dfs_.com> wrote in
news:LR*************@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.

--
Lyle Fairfield

[DFS]

Lyle Fairfield wrote:

"DFS" <nospam@dfs_.com> wrote in
news:LR*************@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.

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]

"DFS" <nospam@dfs_.com> wrote in message
news:g7**************@bignews6.bellsouth.net...

Lyle Fairfield wrote:

"DFS" <nospam@dfs_.com> wrote in
news:LR*************@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.

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?

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

[Lyle Fairfield]

DFS wrote:

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

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]

Keith Wilby wrote:

"DFS" <nospam@dfs_.com> wrote in message
news:g7**************@bignews6.bellsouth.net...

Lyle Fairfield wrote:

"DFS" <nospam@dfs_.com> wrote in
news:LR*************@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.
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?

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.

Why did you have to overhaul it?

It's no great hardship to use this convention and pays
big dividends IMO. I wish I'd started using it earlier.
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.

Keith.
www.keithwilby.com

[Rick Brandt]

Keith Wilby wrote:

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

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]

Lyle Fairfield wrote:

DFS wrote:

Lyle Fairfield wrote:

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

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.

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.
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.
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)
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.

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

[Lyle Fairfield]

"Rick Brandt" <ri*********@hotmail.com> wrote in
news:bg*******************@newssvr14.news.prodigy. com:

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

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

[Keith Wilby]

"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:bg*******************@newssvr14.news.prodigy. com...

Keith Wilby wrote:

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

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?

I use prefixes for ease of identification of the object in code. If that
has the added benefit of stopping a conflict then fine, but I have no
examples.

[Keith Wilby]

"DFS" <nospam@dfs_.com> wrote in message
news:r%**************@bignews6.bellsouth.net...

Keith Wilby wrote:

"DFS" <nospam@dfs_.com> wrote in message

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.
Why did you have to overhaul it?

Because of migration from A97 to A2k3.

It's no great hardship to use this convention and pays
big dividends IMO. I wish I'd started using it earlier.

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.

Each to their own, I just find things easier to identify in code using
prefixes.

Regards,
Keith.

[David W. Fenton]

"DFS" <nospam@dfs_.com> wrote in
news:wu****************@bignews3.bellsouth.net:

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?

Yes, that's exactly what causes it. If you have an expression in a
controlsource on a bound form and you use the field name and have a
control with the same name, Access cannot resolve the ambiguity.

If you're using the control name somewhere else, the ambiguity is
also there. While Access is likely to resolve it correctly (if all
you want is the control value and its bound to a field of the same
name, you're going to get the right result regardless of whether it
uses the field or the control), you're still depending on Access to
guess correctly, and while that guess may be correct in the original
context for which you've designed, needs may change over time and it
can potentially cause problems.

Because of that, I disambiguate the name of any control called in
code the first time I call it in code. That means that from that
point on, the control and the field have different names.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

Wo********@hotmail.com wrote in
news:11*********************@e56g2000cwe.googlegro ups.com:

Ok Guys I tried the code that DFS gave me.. and changed
ME.Subform.recordsource to Me.resultssubform.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.

Try switching to Me!resultssubform.SourceObject and see if that
makes a difference (I don't know that it will).

I've never encountered that error message myself -- I didn't know it
even it existed (Error 2124, I see).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[DFS]

David W. Fenton wrote:

"DFS" <nospam@dfs_.com> wrote in
news:wu****************@bignews3.bellsouth.net:

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?
Yes, that's exactly what causes it.

Whether the control names match the column names or not, #Name? shows up if
there's no datasource set for the Form, but the controls have
ControlSources.
If you have an expression in a
controlsource on a bound form and you use the field name and have a
control with the same name, Access cannot resolve the ambiguity.
Not sure if I can duplicate this claim.

On 2 bound datasheet tests I did, I had no problems.

Example 1:
text control (and underlying column) text values: JOB_RESULT
add a text box with the ControlSource: =Left([JOB_RESULT],3)

Example 2:
text control (and underlying column) date values: JOB_START_DT
add a text box with the ControlSource: =([JOB_START_DT] + 7)

When I run the datasheet, both examples work perfectly.

Both examples also work fine if the form is Single or Continuous.
If you're using the control name somewhere else, the ambiguity is
also there. While Access is likely to resolve it correctly (if all
you want is the control value and its bound to a field of the same
name, you're going to get the right result regardless of whether it
uses the field or the control), you're still depending on Access to
guess correctly, and while that guess may be correct in the original
context for which you've designed, needs may change over time and it
can potentially cause problems.
I disagree. If I haven't seen a "potential problem" by now, after using
Access 2.0, 95, 97, 2000 and 2003, I never will.

Because of that, I disambiguate the name of any control called in
code the first time I call it in code. That means that from that
point on, the control and the field have different names.

[Woodies_46]

Ok so if I rename my controls in the form, how much of the code would I
have to change, By the looks of things the sqlstr varible seems to be
pulling the right information but when it goes to place the SQL
statment in the SourceObject property box thats where the error
occurs.. its so annoying..

Also I was wondering if it is possible to make a blank Query and then
write that SQL statment to it... and then have the subforms
SourceObject property box pointing to that Query.
I know its properly a stupid way of doing things but it look like a way
of getting around this dumb error.

Thanks Guys

[DFS]

Wo********@hotmail.com wrote:

Ok so if I rename my controls in the form, how much of the code would
I have to change,
There's no need to rename your controls. Leave them matching the underlying
column names.

By the looks of things the sqlstr varible seems to
be pulling the right information but when it goes to place the SQL
statment in the SourceObject property box thats where the error
occurs.. its so annoying..
I don't think you need to change the SourceObject. The original code I sent
changes the subform recordsource, and should have worked.
Also I was wondering if it is possible to make a blank Query and then
write that SQL statment to it...
You won't be able to create a blank query, but create a simple one

SELECT * FROM TABLE;

Save it with name "FormQuery"

Then refresh it with code like this:

dim qItem as QueryDef
Set qItem = db.QueryDefs("FormQuery")
qItem.SQL = strSQL
qItem.Close
db.QueryDefs.Refresh

and then have the subforms
SourceObject property box pointing to that Query.
To change a form's SourceObject to a query, using code:

Forms.FormName.SourceObject = "Query.QueryName"

Or you can do it in the form design view, in the Properties dialog. You
have to preface the query name with the word Query.
I know its properly a stupid way of doing things but it look like a
way of getting around this dumb error.

Thanks Guys

[Woodies_46]

Ok man.. u've been a been a big help its just this one thing thats
stuffing up, please bear with me i'm only new at this VB coding i'm so
used to working with pascal.

The reason that i changed recordsource to SourceObject is becuase the
Subform/SubQuery thingy dosn't have recordsource as a property and
just gives me an error when i try and use it.. Would u like for me to
emial u the forms and some of the code i've got..

Thanks

[DFS]

Wo********@hotmail.com wrote:

Ok man.. u've been a been a big help its just this one thing thats
stuffing up, please bear with me i'm only new at this VB coding i'm so
used to working with pascal.

The reason that i changed recordsource to SourceObject is becuase the
Subform/SubQuery thingy dosn't have recordsource as a property and
just gives me an error when i try and use it.. Would u like for me to
emial u the forms and some of the code i've got..
Woodies,

Your main form (which sounds like a search form) should probably be unbound,
and your subform should not be linked to the main form (LinkChildFields and
LinkMasterFields should be blank). You won't see the Recordsource property
for the subform when viewing it on the main form in design view, but the
code I originally gave you will set the subform recordsource.

Thanks

[Woodies_46]

So what ur saying is i should be able to copy and paste ur code stright
in and it should work, or do i have to rename subform with the name of
the Subform.

[Woodies_46]

Ok I just ranamed subform with the name of the subfrom its self and now
it gives me an error thats says "Object does not support property or
method".

Sorry Man

Thanks
Nathan

[DFS]

Wo********@hotmail.com wrote:

Ok I just ranamed subform with the name of the subfrom its self and
now it gives me an error thats says "Object does not support property
or method".
Try Me.subformname.form.recordsource = strSQL

Sorry Man

Thanks
Nathan

[Wayne Gillespie]

On 22 Mar 2006 14:42:25 -0800, Wo********@hotmail.com wrote:

Ok so if I rename my controls in the form, how much of the code would I
have to change, By the looks of things the sqlstr varible seems to be
pulling the right information but when it goes to place the SQL
statment in the SourceObject property box thats where the error
occurs.. its so annoying..

Also I was wondering if it is possible to make a blank Query and then
write that SQL statment to it... and then have the subforms
SourceObject property box pointing to that Query.
I know its properly a stupid way of doing things but it look like a way
of getting around this dumb error.

Thanks Guys

The SourceObject property is used to store the NAME of the subform to be loaded
into the subform control. It should NOT be set to a query or SQL statement.

You should be setting the RecordSource of the subform to the query/sql.

Me.MySubform.SourceObject = "NameOfSubform"

Me.MySubform.Form.RecordSource = "MyQueryName"
or
Me.MySubform.Form.RecordSource = SQLstr
Wayne Gillespie
Gosford NSW Australia

[Woodies_46]

I think we are getting some where but it just gives me an error thats
displays "the expression u have entered refers to a object that is
locked or does not exsits". ????what the..

I really like you're guys help..thnak u

[Wayne Gillespie]

On 22 Mar 2006 15:40:26 -0800, Wo********@hotmail.com wrote:

I think we are getting some where but it just gives me an error thats
displays "the expression u have entered refers to a object that is
locked or does not exsits". ????what the..

I really like you're guys help..thnak u

Did you change "MySubform" to the actual name of your subform control?
Wayne Gillespie
Gosford NSW Australia

[Woodies_46]

Yeah i did could u explan what that ME. thing is

[Larry Linson]

"Me!ControlSourceName" used in code cannot determine whether you are
referring to the name of the Control, if you have named it the same, or the
underlying Field in the Record Source that you have used as Control Source.
Which of the two would you want Access to use? Which of the two does it
actually use?

Frankly, if you have used that for years and years without bad result, I
have no problem with your continuing to do so. I do have a problem with your
repeatedly advising others to do so, (and giving you the benefit of the
doubt:) without realizing you may be leading them to a bad result.

The fact that the Microsoft-supplied Wizard does so is not germane, and most
certainly does not imply that it can never be a problem.

Anytime, my friend, that Lyle and I agree, you should take heed, because we
differ on a great many matters of opinion, so our areas of agreement are
more often than not, matters of fact.

Larry Linson
Microsoft Access MVP

"DFS" <nospam@dfs_.com> wrote in message
news:7e**************@bignews6.bellsouth.net...

Lyle Fairfield wrote:

DFS wrote:

Lyle Fairfield wrote:

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

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.

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.

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.

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)

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.

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

[Woodies_46]

Ok i think i've confussed all of us.. what i have is a form with a tab
control window in it then i have three tabs one that has the search
critera with tick boxs and stuff, the next one has a photo and the last
one I have drawn a subQuery...this subquery at the monent is blank and
has no sourceobject or anything. So is it still possible to do what i
would like to do but with a subquery as the look nicer when showing a
heap of results.

Sorry for all the confussion

[Rick Brandt]

"Larry Linson" <bo*****@localhost.not> wrote in message
news:3PmUf.10189$TK2.7835@trnddc07...

"Me!ControlSourceName" used in code cannot determine whether you are referring
to the name of the Control, if you have named it the same, or the underlying
Field in the Record Source that you have used as Control Source. Which of the
two would you want Access to use? Which of the two does it actually use?[snip]

Can you name one example of when it makes any difference? If I'm setting or
referring to the value then the result is the same. If I am setting or
referring to some other property then only one of them is likely to have that
property. To me this is no different than overloaded function signatures used
in many other environments. Access knows I mean the control when I refer to the
Enabled property because the field has no such property for example.

Again, I have no issue with people who prefer to use different names, but it a
preference IMO, nothing more.

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

[Woodies_46]

Ok i've got it sort of working i have a subfrom that is set to
datasheet and when I run the code it take the sqlstr and puts it in the
recordsource property box which is good... but i would like it to add
the coloums to the datasheet once they have been selected.

Cheers

[Lyle Fairfield]

I don't want to continue an argument but upon further thought I want to
point out that there may be a difference to one's coding when the
control and its source field are named as the same thing.

When they are the same then
Me.TheName (or Me!TheName) refers to the form's control, say a textbox,
named TheName. That is Access/VBA examines the form first to find an
object called TheName.

But when they are named differently say TheName for the field, and
txtTheName for the Control then
Me.TheName refers to the Field only and not to the control,
and, of course, Me.txtTheName refers to the Control.

Are there times when we want to to have a simple reference to the field
instead of the control? I'm not sure.

In any case, for my own personal practice, I will continue to give the
control (and all objects) a distinct name. And I will continue to
recommend this practice. Many will disregard that advice. To them I
say, regardless of whether it's dangerous, whether there are problems,
whether you have done it for 50 years with no trouble, whether MS
recommends it, it is not good form.

As an aside, I suggest that those who are influenced by what MS
recommends take a good look at the Northwind and Solution databases. If
you are impressed with what you see there then by all means, keep on
being influenced.

[David W. Fenton]

"DFS" <nospam@dfs_.com> wrote in
news:gI**************@bignews8.bellsouth.net:

David W. Fenton wrote:

"DFS" <nospam@dfs_.com> wrote in
news:wu****************@bignews3.bellsouth.net:

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?

Yes, that's exactly what causes it.

Whether the control names match the column names or not, #Name?
shows up if there's no datasource set for the Form, but the
controls have ControlSources.

Same problem: Access can't resolve the controlsources, because they
aren't there. In the case of duplication within the namespace, it's
just because of an overabundance of possible resolutions, with two
different objects possibly referred to. That's why one should
disambiguate the two things with different names.

If Access did not merge the Controls and Fields collection into the
single default collection of forms, this would not be a problem.
Since it does, it's something that you can have a problem with, and
something that can be easily avoided.

If you have an expression in a
controlsource on a bound form and you use the field name and have
a control with the same name, Access cannot resolve the
ambiguity.

Not sure if I can duplicate this claim.

On 2 bound datasheet tests I did, I had no problems.

Example 1:
text control (and underlying column) text values: JOB_RESULT
add a text box with the ControlSource: =Left([JOB_RESULT],3)

Example 2:
text control (and underlying column) date values: JOB_START_DT
add a text box with the ControlSource: =([JOB_START_DT] + 7)

When I run the datasheet, both examples work perfectly.

Both examples also work fine if the form is Single or Continuous.

Well, you're right -- I've obviously described the problem wrong,
after all. I always rename controls that are referred to in
calculations or code, so I can't find an occurrence of the problem
to explain what it is that actually causes it, but I've certainly
encountered it plenty of times in the days before I started
disambiguating controlnames from fieldnames.

If you're using the control name somewhere else, the ambiguity is
also there. While Access is likely to resolve it correctly (if
all you want is the control value and its bound to a field of the
same name, you're going to get the right result regardless of
whether it uses the field or the control), you're still depending
on Access to guess correctly, and while that guess may be correct
in the original context for which you've designed, needs may
change over time and it can potentially cause problems.

I disagree. If I haven't seen a "potential problem" by now, after
using Access 2.0, 95, 97, 2000 and 2003, I never will.

It's up to you, but I think it's bad practice to not distinguish
between two things that are distinctly different in their meanings
and properties.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

Wo********@hotmail.com wrote in
news:11********************@v46g2000cwv.googlegrou ps.com:

Ok man.. u've been a been a big help its just this one thing thats
stuffing up, please bear with me i'm only new at this VB coding
i'm so used to working with pascal.

The reason that i changed recordsource to SourceObject is becuase
the Subform/SubQuery thingy dosn't have recordsource as a
property and just gives me an error when i try and use it.. Would
u like for me to emial u the forms and some of the code i've got..

The subform on the form is a control that holds another form. That
control is distinct from the form that is its source object.

A control called SUBFORM could have as its SourceObject the form
subMyForm.

The subform control has a small set of properties.

One of those returns a reference to the actual form embedded in that
subform, the one named in the SourceObject property. That is
referred to as:

Me!SUBFORM.Form

And what you get there is the same set of properties that all forms
have.

Thus:

Me!SUBFORM

has no Recordsource because it is not a form.

But Me!SUBFORM.Form

*does* have a Recordsource, because it refers to a form object.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Wayne Gillespie]

On 22 Mar 2006 16:53:34 -0800, Wo********@hotmail.com wrote:

Yeah i did could u explan what that ME. thing is

Me is a shorthand way to refer to the form (or report) where the code resides.

eg if you have a form called "frmMyForm" with a control called "txtMyControl",
you can refer to the control simply as Me.txtMyControl
This is the equivalent of writing -
Forms!frmMyForm!txtMyControl

You can refer to the properties of the control using -
Me.txtMyControl.Enabled=True
Me.txtMyControl.Visible=False etc.

You can refer to properties of the form using -
Me.RecordSource = "qryMyQuery"
Me.AllowEdits=False etc
Wayne Gillespie
Gosford NSW Australia

[DFS]

Larry Linson wrote:

"Me!ControlSourceName" used in code cannot determine whether you are
referring to the name of the Control, if you have named it the same,
or the underlying Field in the Record Source that you have used as
Control Source.
Au contraire. VBA can and does determine that Me!ControlSourceName refers
to the control, and provides you access to the control properties.

Which of the two would you want Access to use? Which
of the two does it actually use?
I want it to use the value in the control, and it does use the value in the
control, which is the value in the table.
Frankly, if you have used that for years and years without bad
result, I have no problem with your continuing to do so. I do have a
problem with your repeatedly advising others to do so, (and giving
you the benefit of the doubt:) without realizing you may be leading
them to a bad result.
OK. What bad result have you ever encountered?
The fact that the Microsoft-supplied Wizard does so is not germane,
I think it is germane. Very much so. They wouldn't do so if it was as
potentially fraught with errors as some here like to claim.

Does MS anywhere discourage the use of matching names?
and most certainly does not imply that it can never be a problem.
Never? This is like a defense attorney saying "Isn't it possible that a pig
could one day fly?"

Maybe it can. Never has for me.

Anytime, my friend, that Lyle and I agree, you should take heed,
because we differ on a great many matters of opinion, so our areas of
agreement are more often than not, matters of fact.
LOL! Hubris goeth before a fall...

Larry Linson
Microsoft Access MVP

"DFS" <nospam@dfs_.com> wrote in message
news:7e**************@bignews6.bellsouth.net...

Lyle Fairfield wrote:

DFS wrote:
Lyle Fairfield wrote:

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

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.

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.

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.

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)

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.

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

[Larry Linson]

"DFS" <nospam@dfs_.com> wrote

LOL! Hubris goeth before a fall...

I also agree with Lyle on the matter of opinion with which he closed his
participation in this discussion, and I am following his example. Read his
post of 3/22/2006 8:39PM CST, which I endorse.

Larry Linson
Microsoft Access MVP

[Larry Linson]

"Lyle Fairfield" <ly***********@aim.com> wrote

I don't want to continue an argument but
upon further thought I want to point out
that there may be a difference to one's
coding when the control and its source field
are named as the same thing.

When they are the same then Me.TheName
(or Me!TheName) refers to the form's control,
say a textbox, named TheName. That is
Access/VBA examines the form first to find an
object called TheName.

But when they are named differently say
TheName for the field, and txtTheName for
the Control then Me.TheName refers to the Field
only and not to the control, and, of course,
Me.txtTheName refers to the Control.

Are there times when we want to to have a
simple reference to the field instead of the
control? I'm not sure.

In any case, for my own personal practice, I
will continue to give the control (and all objects)
a distinct name. And I will continue to recom-
mend this practice. Many will disregard that
advice. To them I say, regardless of whether it's
dangerous, whether there are problems, whether
you have done it for 50 years with no trouble,
whether MS recommends it, it is not good form.

As an aside, I suggest that those who are
influenced by what MS recommends take a
good look at the Northwind and Solution databases.
If you are impressed with what you see there
then by all means, keep on being influenced.

I quoted this in its entirety because I thought it worth being said
(written) again. I agree with Lyle's position, endorse it and plan to follow
suit, and to his cogent explanation, I say "Amen".

Larry Linson
Microsoft Access MVP

[DFS]

Larry Linson wrote:

"Lyle Fairfield" <ly***********@aim.com> wrote

> I don't want to continue an argument but
> upon further thought I want to point out
> that there may be a difference to one's
> coding when the control and its source field
> are named as the same thing.
>
> When they are the same then Me.TheName
> (or Me!TheName) refers to the form's control,
> say a textbox, named TheName. That is
> Access/VBA examines the form first to find an
> object called TheName.
>
> But when they are named differently say
> TheName for the field, and txtTheName for
> the Control then Me.TheName refers to the Field
> only and not to the control, and, of course,
> Me.txtTheName refers to the Control.
>
> Are there times when we want to to have a
> simple reference to the field instead of the
> control? I'm not sure.
What would be the point? One of the main reasons for using a bound form in
the first place is to have a reference to the field (and the data therein),
via the control.
> In any case, for my own personal practice, I
> will continue to give the control (and all objects)
> a distinct name. And I will continue to recom-
> mend this practice. Many will disregard that
> advice. To them I say, regardless of whether it's
> dangerous, whether there are problems, whether
> you have done it for 50 years with no trouble,
> whether MS recommends it, it is not good form.
Why is it "not good form"? Surely you have some reason to make this claim.
> As an aside, I suggest that those who are
> influenced by what MS recommends take a
> good look at the Northwind and Solution databases.
> If you are impressed with what you see there
> then by all means, keep on being influenced.

No need to be hypocritical, yet sneering at the same time. I'm quite sure
MS' influence on your development efforts has been considerable through the
years, and across their technologies.

I quoted this in its entirety because I thought it worth being said
(written) again. I agree with Lyle's position, endorse it and plan to
follow suit, and to his cogent explanation, I say "Amen".
I don't see you or Lyle offering any support whatsoever for t/his position,
other than to say "it is not good form" to use matching names. Is there a
valid technical reason? I've never encountered one, and no one on cdma
seems able to provide one. Seems to me it's purely a matter of personal
taste.

Larry Linson
Microsoft Access MVP

DFS
Microsoft Access developer

[RoyVidar]

DFS wrote in message <kM***************@bignews1.bellsouth.net> :

The fact that the Microsoft-supplied Wizard does so is not germane,

I think it is germane. Very much so. They wouldn't do so if it was as
potentially fraught with errors as some here like to claim.

Does MS anywhere discourage the use of matching names?

Perhaps not directly discourage, but as I see it, they do recognize it
as one of several possible reasons for the #Name error.

Here's a quote from a Microsoft KB on the this error. Pay attention to
the second reason/cause:

"Some other causes for the #Name? error value include:
• A field name on the form or report that does not match the name of
the field in the underlying table.
• A control name that is the same as one of the fields in the
underlying table.
• An expression that is designed to calculate a sum for a control may
include a Sum() function. (You can use the Sum() function to calculate
sums only for fields, not for controls.)

http://support.microsoft.com/default...b;en-us;209132

--
Roy-Vidar