#Name error in Query

Have read at least 100 posts re #name errors and still can't figure out
what's wrong with mine.
I have a master form (and table, both called "Client") that has a (unique)
ClientID displayed (bound to the tables ClientID).
I have a child table that contains all (non-gound mail) contact "categories",
(ex: email, web site URL, phone, etc.), each of these having a "type" (ex for
phone: home, mobile, business, etc.). For each "category" the client can
select one as "primary".
On the main form I want to display the "primary" phone number in a textbox
and am trying to use the following query to get it:
(where "address" is the actual contact data, phone #, email,..and
is the only visible column)
(I've tried without the ![address] on the end, this was added when
I used the expression
builder so tried in one of my tests )
(a unbound field on the form called "phone" has it's control
source set to this query)
(hopefully have all the (..)'s right, couldn't copy/paste it here)

=(SELECT Contact.Address FROM Contact
WHERE (((Contact.ClientID)=[forms]![client]![clientid])
AND ((Contact.Category)="Phone") AND ((Contact.Primary)=True))!
[address]

This was originally built in Query Wizard and as long as the form is active
it actually works directly in the Wizard when I "run" it (it doesn't have the
![address] on it though). I copied/pasted the SQL to the control source (&
added the requested (..)'s until it stopped complaining). When the form runs
I get the #Name error in the "phone" textbox.
Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

As a note, I do have a another table called "address" that that appears as a
subform on this main form inside a tab control, could there be any conflicts
generated there?

BBC wrote:
Have read at least 100 posts re #name errors and still can't figure out
what's wrong with mine.
I have a master form (and table, both called "Client") that has a (unique)
ClientID displayed (bound to the tables ClientID).
I have a child table that contains all (non-gound mail) contact "categories",
(ex: email, web site URL, phone, etc.), each of these having a "type" (ex for
phone: home, mobile, business, etc.). For each "category" the client can
select one as "primary".
On the main form I want to display the "primary" phone number in a textbox
and am trying to use the following query to get it:
(where "address" is the actual contact data, phone #, email,..and
is the only visible column)
(I've tried without the ![address] on the end, this was added when
I used the expression
builder so tried in one of my tests )
(a unbound field on the form called "phone" has it's control
source set to this query)
(hopefully have all the (..)'s right, couldn't copy/paste it here)

=(SELECT Contact.Address FROM Contact
WHERE (((Contact.ClientID)=[forms]![client]![clientid])
AND ((Contact.Category)="Phone") AND ((Contact.Primary)=True))!
[address]

This was originally built in Query Wizard and as long as the form is active
it actually works directly in the Wizard when I "run" it (it doesn't have the
![address] on it though). I copied/pasted the SQL to the control source (&
added the requested (..)'s until it stopped complaining). When the form runs
I get the #Name error in the "phone" textbox.
Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

What does ![address] represent?

Bob

BBC wrote:
Have read at least 100 posts re #name errors and still can't figure out
what's wrong with mine.
I have a master form (and table, both called "Client") that has a (unique)
ClientID displayed (bound to the tables ClientID).
I have a child table that contains all (non-gound mail) contact "categories",
(ex: email, web site URL, phone, etc.), each of these having a "type" (ex for
phone: home, mobile, business, etc.). For each "category" the client can
select one as "primary".
On the main form I want to display the "primary" phone number in a textbox
and am trying to use the following query to get it:
(where "address" is the actual contact data, phone #, email,..and
is the only visible column)
(I've tried without the ![address] on the end, this was added when
I used the expression
builder so tried in one of my tests )
(a unbound field on the form called "phone" has it's control
source set to this query)
(hopefully have all the (..)'s right, couldn't copy/paste it here)

=(SELECT Contact.Address FROM Contact
WHERE (((Contact.ClientID)=[forms]![client]![clientid])
AND ((Contact.Category)="Phone") AND ((Contact.Primary)=True))!
[address]

This was originally built in Query Wizard and as long as the form is active
it actually works directly in the Wizard when I "run" it (it doesn't have the
![address] on it though). I copied/pasted the SQL to the control source (&
added the requested (..)'s until it stopped complaining). When the form runs
I get the #Name error in the "phone" textbox.
Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200902/1

BBC,

What is the name of the field on the form. If it's ClientID try changing to
txtClientID.
--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"BBC" u49322@uwe wrote in message news:912cefff906fc@uwe...
Have read at least 100 posts re #name errors and still can't figure out
what's wrong with mine.
I have a master form (and table, both called "Client") that has a (unique)
ClientID displayed (bound to the tables ClientID).
I have a child table that contains all (non-gound mail) contact
"categories",
(ex: email, web site URL, phone, etc.), each of these having a "type" (ex
for
phone: home, mobile, business, etc.). For each "category" the client can
select one as "primary".
On the main form I want to display the "primary" phone number in a textbox
and am trying to use the following query to get it:
(where "address" is the actual contact data, phone #,
email,..and
is the only visible column)
(I've tried without the ![address] on the end, this was added
when
I used the expression
builder so tried in one of my tests )
(a unbound field on the form called "phone" has it's control
source set to this query)
(hopefully have all the (..)'s right, couldn't copy/paste it
here)

=(SELECT Contact.Address FROM Contact
WHERE (((Contact.ClientID)=[forms]![client]![clientid])
AND ((Contact.Category)="Phone") AND ((Contact.Primary)=True))!
[address]

This was originally built in Query Wizard and as long as the form is
active
it actually works directly in the Wizard when I "run" it (it doesn't have
the
![address] on it though). I copied/pasted the SQL to the control source
(&
added the requested (..)'s until it stopped complaining). When the form
runs
I get the #Name error in the "phone" textbox.
Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

In one of my many trials I used the expression builder (EB) to insert the
query into the field's control source (selected the query in the EB, the EB
asked me to select a field from the Query (address or ClientID) and then EB
placed the "![address]" at the end of the Select statement .. so I just gave
it a try in one of my tests. (in that particular test I had made both the
"address" and the ClientID" visible in the Query, The original tests simply
used the query without that but they didn't work either.
I've tried puting the query to the control source 3 ways now, naming the the
control source=query as saved in the Query Wizard, copying/pasting the SQL
code as generated from the Query Wizard, and then taking the Query Wizard
code via the EB (which resulted in the ![address] being added to the end of
the SQL statement).
thanks

raskew wrote:
What does ![address] represent?

Bob

Have read at least 100 posts re #name errors and still can't figure out
what's wrong with mine.
[quoted text clipped - 27 lines]
Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200902/1

BBC,

Yu know I got so focused on fixing the your statement I didn't pay
attention. If all you are trying to do is pull up the Address when those
conditions are met why not use a DlookUp statement?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"BBC via AccessMonster.com" u49322@uwe wrote in message
news:9130db2168e75@uwe...
In one of my many trials I used the expression builder (EB) to insert the
query into the field's control source (selected the query in the EB, the
EB
asked me to select a field from the Query (address or ClientID) and then
EB
placed the "![address]" at the end of the Select statement .. so I just
gave
it a try in one of my tests. (in that particular test I had made both the
"address" and the ClientID" visible in the Query, The original tests
simply
used the query without that but they didn't work either.
I've tried puting the query to the control source 3 ways now, naming the
the
control source=query as saved in the Query Wizard, copying/pasting the SQL
code as generated from the Query Wizard, and then taking the Query Wizard
code via the EB (which resulted in the ![address] being added to the end
of
the SQL statement).
thanks

raskew wrote:
What does ![address] represent?

Bob

Have read at least 100 posts re #name errors and still can't figure out
what's wrong with mine.
[quoted text clipped - 27 lines]
Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200902/1

Forgot the DLookUp...

=DLookUp("Address", "Contact", "ClientID= " & [forms]![client]![clientid] &
And "Category = '" & Phone &"'" & " And Primary & " =True

Hope I got that right.... watch out for word wrap...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"BBC via AccessMonster.com" u49322@uwe wrote in message
news:9130db2168e75@uwe...
In one of my many trials I used the expression builder (EB) to insert the
query into the field's control source (selected the query in the EB, the
EB
asked me to select a field from the Query (address or ClientID) and then
EB
placed the "![address]" at the end of the Select statement .. so I just
gave
it a try in one of my tests. (in that particular test I had made both the
"address" and the ClientID" visible in the Query, The original tests
simply
used the query without that but they didn't work either.
I've tried puting the query to the control source 3 ways now, naming the
the
control source=query as saved in the Query Wizard, copying/pasting the SQL
code as generated from the Query Wizard, and then taking the Query Wizard
code via the EB (which resulted in the ![address] being added to the end
of
the SQL statement).
thanks

raskew wrote:
What does ![address] represent?

Bob

Have read at least 100 posts re #name errors and still can't figure out
what's wrong with mine.
[quoted text clipped - 27 lines]
Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200902/1

I thought DLookUp was for tables that are NOT open,I was using the Query
instead of a DLookUp, this "contact" table is also on the tab control
(another tab) along with the (mail-)address table.

Gina Whipp wrote:
Forgot the DLookUp...

=DLookUp("Address", "Contact", "ClientID= " & [forms]![client]![clientid] &
And "Category = '" & Phone &"'" & " And Primary & " =True

Hope I got that right.... watch out for word wrap...

In one of my many trials I used the expression builder (EB) to insert the
query into the field's control source (selected the query in the EB, the
[quoted text clipped - 25 lines]
Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

--
Message posted via http://www.accessmonster.com

the "client" table's primary key is ClientID and it is bound to the Client
form (as ClientID). The "contact" table also has a "ClientID" column that is
related to the "Client.ClientID". The matching process seems to work (when I
run the query from the QWizard it finds the right row and display the right
contact address (remember non of this is related to "address" table, although
it is open on another tab)
When I named these tables & fields I didn't expect it would be this confusing
(I foolishly expected it all to work)

To recap.
I have Tables (with fields indicate by - ):
Client (displayed on the main form)
-ClientID (bound, numeric primary key)
-Phone (unbound txtbox)
Contact (related to "clients" tabel, a datagrid; located on
a tab control, bottom main form)
-ClientID (related to "clients.ClientID")
-Address (txt field containing the phone number)
Address (the table for mail/home address, a datagrid; located on
a tab control with "contact" and others)
-Address1,2, -City, -etc (this is not part of the interation
at this point but it does have a name in common
with a field in "contacts" so I've indicated its presence)

So I'm using the following query to place the primary phone number from the
contacts table onto the form in the unbound control called "phone" (ie it's
control source=(Select ....))

=(SELECT Contact.Address FROM Contact
WHERE (((Contact.ClientID)=[forms]![client]![clientid])
AND ((Contact.Category)="Phone")
AND ((Contact.Primary)=True))

I get the #Name error showing in the form's field txtbox "phone" when I run
the form.
When I built this query in Query Wizard and ran it from there (ie selected
datagrid view) it actually works and shows me the primary phone number in a
one-row,one column datagrid. As long as I had already tried to run the form
and have itsitting in the background. I've gotton rid of the ![addreess] as
it's not part of the issue and has become a red-herring.

I really apprecite the help, I have spent a whole week trying to solve this.
It has become a major point of frustration for me, something so simple (or
not). I'm developing some free software for a chartible foundation and I'm
just about at the point giving up (as I need this capability for a number of
items on the form .. maybe I'll just have them re-type this stuff everywhere
it's needed .. NOT)
thanks so much
Brian

Gina Whipp wrote:
BBC,

What is the name of the field on the form. If it's ClientID try changing to
txtClientID.
Have read at least 100 posts re #name errors and still can't figure out
what's wrong with mine.
[quoted text clipped - 36 lines]
Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

--
Message posted via http://www.accessmonster.com

DLookUp does not have that restriction and you can perform a DLookUp on a
query as well.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"BBC via AccessMonster.com" u49322@uwe wrote in message
news:9136b553a8e39@uwe...
I thought DLookUp was for tables that are NOT open,I was using the Query
instead of a DLookUp, this "contact" table is also on the tab control
(another tab) along with the (mail-)address table.

Gina Whipp wrote:
Forgot the DLookUp...

=DLookUp("Address", "Contact", "ClientID= " & [forms]![client]![clientid]
&
And "Category = '" & Phone &"'" & " And Primary & " =True

Hope I got that right.... watch out for word wrap...

In one of my many trials I used the expression builder (EB) to insert
the
query into the field's control source (selected the query in the EB, the
[quoted text clipped - 25 lines]
Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

--
Message posted via http://www.accessmonster.com