G
Guest
Thanks to you both.
After playing for a while, I still can't figure why it wouldn't work simply.
However, I worked around it with a little more coding. Not as clean....but
works.
--
Thanks for your help,
Chris
After playing for a while, I still can't figure why it wouldn't work simply.
However, I worked around it with a little more coding. Not as clean....but
works.
--
Thanks for your help,
Chris
BruceM said:Ah. I thought you meant something else.
I'm just going to flail around here and see if I hit anything. When you say
the DLookup returns Null in the immediate window when the ClientID is not
valid, do you mean there is nothing? What happens if you wrap Nz around it?
?Nz(DLookup("[ClientID]", "[tbl 1 ClientNoteGeneral]", "[ClientID] =
'XXXXXXX'"),"NG")
I'm losing track of things here. When you say it doesn't work, I expect you
mean you are calling the function from a form. Maybe the function needs to
be a public function so it is available to the form. I don't recall that
you said where the function is located. Also, back to the original posting,
maybe if ClientID is surrounded by square brackets?
Chris said:Thanks. That is what I was referring to in my last post. Any ideas?
--
Thanks for your help,
Chris
BruceM said:I think there's a missing quote mark at the right side of [tbl 1
ClientNoteGeneral]:
?DLookup("[ClientID]", "[tbl 1 ClientNoteGeneral]", "[ClientID] =
'XXXXXXX'")
You have to do things a bit differntly in the immediate window, sorry I
should have mentioned that. First, you have to either use the ? or a
variable and you can't use the Me. keyword. That only works in the
active
form. Do it exactly like this:
?DLookup("[ClientID]", "[tbl 1 ClientNoteGeneral], "[ClientID] =
'XXXXXXX'")
Try it with a valid client id where the XXXXXXX is and try it with an
invalid client id.
I'm gone for the day, but I will check back in in the morning.
--
Dave Hargis, Microsoft Access MVP
:
Immediate window results = compile error. expected: line number or
label
or
statement or end of statement.
Using:
(DLookup("[ClientID]", "[tbl 1 ClientNoteGeneral], "[ClientID] = '" &
Me.[txtClientID] & "'"))
and
=(DLookup("[ClientID]", "[tbl 1 ClientNoteGeneral], "[ClientID] = '" &
Me.[txtClientID] & "'"))
BTW, earlier when I attempted to compile I did get syntax error. But
the
other error came up on [ClientID} whenever cursor left the line of
code.
Thanks for your help,
Chris
:
Is the field ClientID in your table a text field or a numeric field?
Try typing in a DLookup on the table and field in the immediate
window
to
see what results you get.
--
Dave Hargis, Microsoft Access MVP
:
No change, Dave. Same error.
--
Thanks for your help,
Chris
:
I made a little syntax error and didn't notice it, sorry.
If Not IsNull(DLookup("[ClientID]", "[tbl 1 ClientNoteGeneral],
"[ClientID]
='" & Me.[txtClientID] & "'")) Then -- the remainder of the
If
statement.
^ - Missing Quote here
If it still doesn't work, we need to look at some more obscure
things like
reference settings, because I tested it using some of my data/
--
Dave Hargis, Microsoft Access MVP
:
Here's what I used:
If Not IsNull(DLookup("[ClientID]", "[tbl 1
ClientNoteGeneral],
"[ClientID]
='" & Me.[txtClientID] & "')) Then -- the remainder of the
If
statement.
It fails expecting list separator on the second ClientID.
It acts the same if I try to use it as HasData Function.
In both cases I cut and pasted yours.
I do appreciate this help.
Chris
:
It really depends on how you will use it. If it is a one
time
check, then
there is no need to have the HasData1 function. It can be
in
the code you
originally posted:
If Not IsNull(DLookup("[ClientID]", "[tbl 1
ClientNoteGeneral],
"[ClientID]
='" & Me.[txtClientID] & "')) Then
MsgBox "record"
Else
MsgBox "no record"
End If
If you plan to use it in more than one place in your code,
then
keep the
HasData1 function, but chage the code"
Function HasData1(strClient) As Boolean
HasData1 = Not IsNull(DLookup("[ClientID]", "[tbl 1
ClientNoteGeneral],
"[ClientID] ='" & Me.[txtClientID] & "'))
End Function
--
Dave Hargis, Microsoft Access MVP
:
Where do I put that? In the function? SQL? I'm very
much
an amateur and
don't really know much.
--
Thanks for your help,
Chris
:
Very puzzling, Chris. I don't see the problem with the
function. I would
suggest, however, that you could make this a bit more
efficient. Give this a
try:
= Not IsNull(DLookup("[ClientID]", "[tbl 1
ClientNoteGeneral], "[ClientID]
='" & Me.[txtClientID] & "'))
Notice I put the Me. qualifer on txtClientID. You
should
always qualify
your control names.
The above will return True if a record with the ClientID
field matching the
value of the txtClientID control is found and False if
not.
--
Dave Hargis, Microsoft Access MVP
:
Thanks, Dave.
I thought the function was ok...please take a look.
Do
you see anything?
Function HasData1(ClientID) As Boolean
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim SQL As String
Set db = CurrentDb()
SQL = "SELECT [tbl 1 ClientNoteGeneral].ClientID FROM
[tbl 1
ClientNoteGeneral] WHERE [ClientID] ='" &
[txtClientID] &
"';"
Set rst = db.OpenRecordset(SQL)
If rst.EOF And rst.BOF Then
HasData1 = False
Else
HasData1 = True
End If
End Function
--
Thanks for your help,
Chris
:
HasData1 appears to be a function in your
application.
You need to have a
look at it and see what it is returning. Your code
expects it to return a
boolean value of True if the record has data and
False
if not.
The code you posted will work fine if you fix the
function.
--
Dave Hargis, Microsoft Access MVP
:
I am using the following code to determine if a
record is present, then doing
stuff accordingly. Trouble is...I do get the
message
if there is a record
and do not get the message if there is not.
Am I doing something wrong? Is there a better
way?
If HasData1(ClientID) Then
MsgBox "record"
Else ( I even tried NOT HasData1(ClientID))
MsgBox "no record"
End If