#Name error in Query

[BBC]

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)

 

[BBC via 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

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)

 

[Gina Whipp]

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

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.

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)

 

[Gina Whipp]

Rename the field from Phone to txtPhone. See if it's getting confused
between your filter and the name of the field. However, if it were me I
would use a DLookUp.

--
Gina Whipp

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

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

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

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)

 

[BBC via AccessMonster.com]

Thanks for your advice, I substituted DLookUp and it now works (a little slow
even with all 3 selection criteria as indices). I tried changing table,
field and textbox names to ensure no conflict but the problem persists. I
cannot figure out why it's not working (the query returns the right result in
the wizard, the problem would seem to lay in getting the returned result into
the form's textbox [both are simple text fields]). The query only (normally)
returns one record and one field to the textbox's recordsource, could there
be an issue in that it might be more if an error existed in the data. I
suspect it doesn't know or care and would just take the first item from the
query.
I'd really like this query process to work as I also need to sum some numeric
fields from child tables into textboxes on the main form.
thanks again
I'm about to post another question (on form sizing) if you're up for another
challange.

Rename the field from Phone to txtPhone. See if it's getting confused
between your filter and the name of the field. However, if it were me I
would use a DLookUp.

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

[quoted text clipped - 71 lines]

 

[Gina Whipp]

BBC,

It should not be slow... as a matter of fact, it should be quicker. Does
this database have any data? I'd be willing to take a look.

As for the next question, I would suggest posting a new question... not
because I am not up for the challenge but so everyone can benefit and you
get more prospectives/replies.

--
Gina Whipp

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

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

Thanks for your advice, I substituted DLookUp and it now works (a little
slow
even with all 3 selection criteria as indices). I tried changing table,
field and textbox names to ensure no conflict but the problem persists. I
cannot figure out why it's not working (the query returns the right result
in
the wizard, the problem would seem to lay in getting the returned result
into
the form's textbox [both are simple text fields]). The query only
(normally)
returns one record and one field to the textbox's recordsource, could
there
be an issue in that it might be more if an error existed in the data. I
suspect it doesn't know or care and would just take the first item from
the
query.
I'd really like this query process to work as I also need to sum some
numeric
fields from child tables into textboxes on the main form.
thanks again
I'm about to post another question (on form sizing) if you're up for
another
challange.

Rename the field from Phone to txtPhone. See if it's getting confused
between your filter and the name of the field. However, if it were me I
would use a DLookUp.

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

[quoted text clipped - 71 lines]

Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

 

[BBC via AccessMonster.com]

There are about 8 records for this table in the test database. It takes
about 1 - 1.5 secs to get the 3 fields (3 DLookUps) and maybe I shouldn't be
concerned yet, maybe it will be the same with 800 or more recds but I surely
wouldn't want it to go up anywhere near proportionally. Because I never did
get the query/SQL way to work I don't have anything to compare to, although
I've read that DLookUp is faster.
I'm not going to sweat the speed right yet, maybe later though....

I did post the new question in the "Forms" section. The problem seems to be
very common (large numbers of posts) with the feedback focused on a few items,
but seems to be a mixed bag of suggestions, most of which I've tried.

thanks again

BBC,

It should not be slow... as a matter of fact, it should be quicker. Does
this database have any data? I'd be willing to take a look.

As for the next question, I would suggest posting a new question... not
because I am not up for the challenge but so everyone can benefit and you
get more prospectives/replies.

Thanks for your advice, I substituted DLookUp and it now works (a little
slow

[quoted text clipped - 31 lines]

 

[Gina Whipp]

BBC,

The time to worry is no in early development not down the road where it
could become an issue to fix. Hopefully, in your new psoting you'll get
some feedback that will help determine the issue. My DLookUp's cycle more
records then that and there is no delay, which is why it has me wondering
why yours gives you that 1 to 1.5 second delay.

--
Gina Whipp

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

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

There are about 8 records for this table in the test database. It takes
about 1 - 1.5 secs to get the 3 fields (3 DLookUps) and maybe I shouldn't
be
concerned yet, maybe it will be the same with 800 or more recds but I
surely
wouldn't want it to go up anywhere near proportionally. Because I never
did
get the query/SQL way to work I don't have anything to compare to,
although
I've read that DLookUp is faster.
I'm not going to sweat the speed right yet, maybe later though....

I did post the new question in the "Forms" section. The problem seems to
be
very common (large numbers of posts) with the feedback focused on a few
items,
but seems to be a mixed bag of suggestions, most of which I've tried.

thanks again

BBC,

It should not be slow... as a matter of fact, it should be quicker. Does
this database have any data? I'd be willing to take a look.

As for the next question, I would suggest posting a new question... not
because I am not up for the challenge but so everyone can benefit and you
get more prospectives/replies.

Thanks for your advice, I substituted DLookUp and it now works (a little
slow

[quoted text clipped - 31 lines]

Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

 

[BBC via AccessMonster.com]

Below is the DLookUp I'm using

DLookUp("[contactdetail]","contact","[ClientID]= " & [Forms]![client]!
[clientid] & "And [category]='Phone' AND [Primary] = " & True)

It is indexed on ClientID, Category & Primary
There are 7 recds in the table, 4 related to my 1st (and usual) test client.
This table is also simultaniously being displayed on one of the tabs of a
active tabcontrol on the same main form (only 1 recd would have
"primary=true" though)

Thanks Gina
Brian

BBC,

The time to worry is no in early development not down the road where it
could become an issue to fix. Hopefully, in your new psoting you'll get
some feedback that will help determine the issue. My DLookUp's cycle more
records then that and there is no delay, which is why it has me wondering
why yours gives you that 1 to 1.5 second delay.

There are about 8 records for this table in the test database. It takes
about 1 - 1.5 secs to get the 3 fields (3 DLookUps) and maybe I shouldn't

[quoted text clipped - 30 lines]

 

[Gina Whipp]

Brian,

This can't be the copy/paste... missing an ampersand, apostorphes and some
quotes... Can you please do a copy paste. If you look at the one I am
using you should see what I mean. Your symbols don't match your data type.

"[llStateID] = '" & Me.cboState & "'" & " And [btBrokerID] = " &
Forms![frmLogOn]![cboLoginID] & " And [llListingTypeID] = '" & Me.cboSortBy
& "'"

DLookUp("[contactdetail]","contact","[ClientID]= " & [Forms]![client]!
[clientid] & "And [category]='Phone' AND [Primary] = " & True)

--
Gina Whipp

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

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

Below is the DLookUp I'm using

DLookUp("[contactdetail]","contact","[ClientID]= " & [Forms]![client]!
[clientid] & "And [category]='Phone' AND [Primary] = " & True)

It is indexed on ClientID, Category & Primary
There are 7 recds in the table, 4 related to my 1st (and usual) test
client.
This table is also simultaniously being displayed on one of the tabs of a
active tabcontrol on the same main form (only 1 recd would have
"primary=true" though)

Thanks Gina
Brian

BBC,

The time to worry is no in early development not down the road where it
could become an issue to fix. Hopefully, in your new psoting you'll get
some feedback that will help determine the issue. My DLookUp's cycle more
records then that and there is no delay, which is why it has me wondering
why yours gives you that 1 to 1.5 second delay.

There are about 8 records for this table in the test database. It takes
about 1 - 1.5 secs to get the 3 fields (3 DLookUps) and maybe I
shouldn't

[quoted text clipped - 30 lines]

Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

 

[BBC via AccessMonster.com]

Here it is again, a direct copy/paste from the "controlsource". It gets the
correct results each time when switching parent records, so it does actually
work. As I recall I struggled with the quotes a bit and finally arrived at
this with the aid of either one of my books (or maybe a search on
AccessMonster).


=DLookUp("[contactdetail]","contact","[ClientID]= " & [Forms]![client]!
[clientid] & "And [category]='Phone' AND [Primary] = " & True)

Brian,

This can't be the copy/paste... missing an ampersand, apostorphes and some
quotes... Can you please do a copy paste. If you look at the one I am
using you should see what I mean. Your symbols don't match your data type.

"[llStateID] = '" & Me.cboState & "'" & " And [btBrokerID] = " &
Forms![frmLogOn]![cboLoginID] & " And [llListingTypeID] = '" & Me.cboSortBy
& "'"

DLookUp("[contactdetail]","contact","[ClientID]= " & [Forms]![client]!
[clientid] & "And [category]='Phone' AND [Primary] = " & True)

Below is the DLookUp I'm using

[quoted text clipped - 25 lines]

 

[Gina Whipp]

BBC,

And you say this does not show immediately? I don't see anything wrong. Is
the ClientID a Primary Key in Contact table?

--
Gina Whipp

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

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

Here it is again, a direct copy/paste from the "controlsource". It gets
the
correct results each time when switching parent records, so it does
actually
work. As I recall I struggled with the quotes a bit and finally arrived
at
this with the aid of either one of my books (or maybe a search on
AccessMonster).


=DLookUp("[contactdetail]","contact","[ClientID]= " & [Forms]![client]!
[clientid] & "And [category]='Phone' AND [Primary] = " & True)

Brian,

This can't be the copy/paste... missing an ampersand, apostorphes and some
quotes... Can you please do a copy paste. If you look at the one I am
using you should see what I mean. Your symbols don't match your data
type.

"[llStateID] = '" & Me.cboState & "'" & " And [btBrokerID] = " &
Forms![frmLogOn]![cboLoginID] & " And [llListingTypeID] = '" &
Me.cboSortBy
& "'"

DLookUp("[contactdetail]","contact","[ClientID]= " & [Forms]![client]!
[clientid] & "And [category]='Phone' AND [Primary] = " & True)

Below is the DLookUp I'm using

[quoted text clipped - 25 lines]

Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

 

[John Spencer]

First, you are missing a space in the third argument before the AND
Second, you can include the True inside the clause
Third, the assumption is that clientID is a number field. If it is not
then you are going to need to include text delimiters as shown below.
If it is a number then remove the apostophes (') that surround the
Forms!Client!ClientId reference.

=DLookUp("[contactdetail]","contact","[ClientID]= '" & [Forms]![client]!
[clientid] & "' And [category]='Phone' AND [Primary] = True")

What is the name of the control? It cannot be the same as the name of a
field included on the form/report.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Here it is again, a direct copy/paste from the "controlsource". It gets the
correct results each time when switching parent records, so it does actually
work. As I recall I struggled with the quotes a bit and finally arrived at
this with the aid of either one of my books (or maybe a search on
AccessMonster).


=DLookUp("[contactdetail]","contact","[ClientID]= " & [Forms]![client]!
[clientid] & "And [category]='Phone' AND [Primary] = " & True)

Brian,

This can't be the copy/paste... missing an ampersand, apostorphes and some
quotes... Can you please do a copy paste. If you look at the one I am
using you should see what I mean. Your symbols don't match your data type.

"[llStateID] = '" & Me.cboState & "'" & " And [btBrokerID] = " &
Forms![frmLogOn]![cboLoginID] & " And [llListingTypeID] = '" & Me.cboSortBy
& "'"

DLookUp("[contactdetail]","contact","[ClientID]= " & [Forms]![client]!
[clientid] & "And [category]='Phone' AND [Primary] = " & True)

Below is the DLookUp I'm using

[quoted text clipped - 25 lines]

Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

 

[BBC via AccessMonster.com]

No, the ClientID is the primary key in the Client table (the "one" side),
the Contact table (the many side) has a primary key but it's not used.

BBC,

And you say this does not show immediately? I don't see anything wrong. Is
the ClientID a Primary Key in Contact table?

Here it is again, a direct copy/paste from the "controlsource". It gets
the

[quoted text clipped - 28 lines]

 

[Gina Whipp]

John,

He claims it is working just that it is slow. I was trying to figure out
why his would be slow and mine is instantaneous.

--
Gina Whipp

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

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

First, you are missing a space in the third argument before the AND
Second, you can include the True inside the clause
Third, the assumption is that clientID is a number field. If it is not
then you are going to need to include text delimiters as shown below. If
it is a number then remove the apostophes (') that surround the
Forms!Client!ClientId reference.

=DLookUp("[contactdetail]","contact","[ClientID]= '" & [Forms]![client]!
[clientid] & "' And [category]='Phone' AND [Primary] = True")

What is the name of the control? It cannot be the same as the name of a
field included on the form/report.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Here it is again, a direct copy/paste from the "controlsource". It gets
the
correct results each time when switching parent records, so it does
actually
work. As I recall I struggled with the quotes a bit and finally arrived
at
this with the aid of either one of my books (or maybe a search on
AccessMonster).


=DLookUp("[contactdetail]","contact","[ClientID]= " & [Forms]![client]!
[clientid] & "And [category]='Phone' AND [Primary] = " & True)

Brian,

This can't be the copy/paste... missing an ampersand, apostorphes and
some quotes... Can you please do a copy paste. If you look at the one
I am using you should see what I mean. Your symbols don't match your
data type.

"[llStateID] = '" & Me.cboState & "'" & " And [btBrokerID] = " &
Forms![frmLogOn]![cboLoginID] & " And [llListingTypeID] = '" &
Me.cboSortBy & "'"

DLookUp("[contactdetail]","contact","[ClientID]= " & [Forms]![client]!
[clientid] & "And [category]='Phone' AND [Primary] = " & True)

Below is the DLookUp I'm using

[quoted text clipped - 25 lines]
Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

 

[Gina Whipp]

BBC,

Then it is actually pulling the first of one or more when using the DLookUp
on that table. That might account for the speed.

--
Gina Whipp

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

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

No, the ClientID is the primary key in the Client table (the "one"
side),
the Contact table (the many side) has a primary key but it's not used.

BBC,

And you say this does not show immediately? I don't see anything wrong.
Is
the ClientID a Primary Key in Contact table?

Here it is again, a direct copy/paste from the "controlsource". It gets
the

[quoted text clipped - 28 lines]

Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

 

[BBC via AccessMonster.com]

The ClientID is numeric so is the following correct (separating the
single/double quotes for clarity).

=DLookUp("[contactdetail]","contact",
"[ClientID]= " & [Forms]![client]![clientid] & " And [category]='Phone' AND
[Primary] = & True")

if it weren't numeric then... ?

=DLookUp("[contactdetail]","contact",
"[ClientID]= " '& [Forms]![client]![clientid] &' " And [category]='Phone'
AND [Primary] = & True")

The control name is "Phone" and is an unbound control on the form (for
display purposes only), as this is the "primary" phone number (from the
contact table) and is looked up every time the Client recd is displayed. I
also have one of these where it looks up the primary email address and a Web
URL in the contact table. They do work as originally provided, except a
little slower than I'd have expected. 1+ sec to do three of them on the form
(the contact table is only 7 records at this point).

The reason for the original thread was I wanted to use a query to get this
phone number but I could never get it to work ... a "#name error" resulted in
the unbound control. So the DLookUp was suggested as an alternative.

Other suggestions more than welcome
thanks
Brian

First, you are missing a space in the third argument before the AND
Second, you can include the True inside the clause
Third, the assumption is that clientID is a number field. If it is not
then you are going to need to include text delimiters as shown below.
If it is a number then remove the apostophes (') that surround the
Forms!Client!ClientId reference.

=DLookUp("[contactdetail]","contact","[ClientID]= '" & [Forms]![client]!
[clientid] & "' And [category]='Phone' AND [Primary] = True")

What is the name of the control? It cannot be the same as the name of a
field included on the form/report.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Here it is again, a direct copy/paste from the "controlsource". It gets the
correct results each time when switching parent records, so it does actually

[quoted text clipped - 23 lines]

 

[John Spencer]

This is correct IF the clientId is a number field - not a text field
that contains numbers. Note that I removed the ampersand (&).

=DLookUp("[contactdetail]","contact",
"[ClientID]= " & [Forms]![client]![clientid] & " And [category]='Phone'
AND [Primary] = True")


You might try using some VBA code in the current event of the form to
get the values. This will only work if you are showing ONE record at a
time. That is the form is a SINGLE form and not set to continuous or
datasheet (displaying many records at one time). Not knowing your table
structure you might have VBA that looks something like the following.


Private Sub Form_Current()
Dim strSQL As String
Dim rstAny As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb()
strSQL = "SELECT Contact.Address, ContactDetail" & _
" FROM Contact" & _
" WHERE ClientID=" & [forms]![client]![clientid] & _
" AND Category='Phone' AND Primary=True"

Set rstAny = db.OpenRecordset(strSQL)

If rstAny.RecordCount > 0 Then
Me.Phone = rstAny!ContactDetail
Me.Address = rstAny!Address
End If

End Sub

 

[BBC via AccessMonster.com]

OK, that helps a lot (missed taking out the extra "&")

Was the one for a non-numeric field also correct (I'm sure I'll need that at
some point in time).

Yes, it is a single main form (I use a combobox to select the client record)

thanks again, I'll play some more with this
Brian

This is correct IF the clientId is a number field - not a text field
that contains numbers. Note that I removed the ampersand (&).

=DLookUp("[contactdetail]","contact",
"[ClientID]= " & [Forms]![client]![clientid] & " And [category]='Phone'
AND [Primary] = True")

You might try using some VBA code in the current event of the form to
get the values. This will only work if you are showing ONE record at a
time. That is the form is a SINGLE form and not set to continuous or
datasheet (displaying many records at one time). Not knowing your table
structure you might have VBA that looks something like the following.

Private Sub Form_Current()
Dim strSQL As String
Dim rstAny As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb()
strSQL = "SELECT Contact.Address, ContactDetail" & _
" FROM Contact" & _
" WHERE ClientID=" & [forms]![client]![clientid] & _
" AND Category='Phone' AND Primary=True"

Set rstAny = db.OpenRecordset(strSQL)

If rstAny.RecordCount > 0 Then
Me.Phone = rstAny!ContactDetail
Me.Address = rstAny!Address
End If

End Sub