#Name error in Query

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.

Gina Whipp wrote:
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)

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

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

"John Spencer" wrote in message
...
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
'================================================= ===


BBC via AccessMonster.com wrote:
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)

Gina Whipp wrote:
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,

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

"BBC via AccessMonster.com" u49322@uwe wrote in message
news:919ef49c1f199@uwe...
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.

Gina Whipp wrote:
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)

--
Message posted via http://www.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

John Spencer wrote:
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]
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

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

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

John Spencer wrote:
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

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