Check for record

		Home \| Contact Us \| FAQ \| Search & Site Map \| Link to Us
		Sign In \| Join \| Other 45 Sites in Network

Discussion Groups Forms Forms Programming Queries Modules / DAO / VBA Reports / Printing Macros Database Design Security Conversion Importing / Linking SQL Server / ADP Multiuser / Networking Replication Setup / Configuration Developer Toolkits ActiveX Controls New Users General 1 General 2

Access Directory Tools Tutorials User Groups

Related Topics

SQL Server Other DB Products MS Office More Topics ...

MS Access Forum / Forms Programming / May 2007

Check for record

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris - 01 May 2007 20:38 GMT

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

Signature

Thanks for your help,
Chris

Reply to this Message

Klatuu - 01 May 2007 21:19 GMT

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.

Signature

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
[quoted text clipped - 7 lines]
> MsgBox "no record"
> End If

Reply to this Message

Chris - 01 May 2007 21:34 GMT

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

Signature

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
[quoted text clipped - 13 lines]
> > MsgBox "no record"
> > End If

Reply to this Message

Klatuu - 01 May 2007 21:48 GMT

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.

Signature

Dave Hargis, Microsoft Access MVP

> Thanks, Dave.
>
[quoted text clipped - 32 lines]
> > > MsgBox "no record"
> > > End If

Reply to this Message

Chris - 01 May 2007 22:03 GMT

Where do I put that? In the function? SQL? I'm very much an amateur and
don't really know much.

Signature

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
[quoted text clipped - 44 lines]
> > > > MsgBox "no record"
> > > > End If

Reply to this Message

Klatuu - 01 May 2007 22:09 GMT

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

Signature

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.
[quoted text clipped - 47 lines]
> > > > > MsgBox "no record"
> > > > > End If

Reply to this Message

Chris - 01 May 2007 22:27 GMT

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
[quoted text clipped - 68 lines]
> > > > > > MsgBox "no record"
> > > > > > End If

Reply to this Message

Klatuu - 01 May 2007 22:33 GMT

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/

Signature

Dave Hargis, Microsoft Access MVP

> Here's what I used:
>
[quoted text clipped - 83 lines]
> > > > > > > MsgBox "no record"
> > > > > > > End If

Reply to this Message

Chris - 01 May 2007 22:43 GMT

No change, Dave. Same error.

Signature

Thanks for your help,
Chris

> I made a little syntax error and didn't notice it, sorry.
>
[quoted text clipped - 91 lines]
> > > > > > > > MsgBox "no record"
> > > > > > > > End If

Reply to this Message

Klatuu - 01 May 2007 22:46 GMT

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.

Signature

Dave Hargis, Microsoft Access MVP

> No change, Dave. Same error.
>
[quoted text clipped - 93 lines]
> > > > > > > > > MsgBox "no record"
> > > > > > > > > End If

Reply to this Message

Chris - 01 May 2007 22:49 GMT

It is a text field.

Signature

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
[quoted text clipped - 97 lines]
> > > > > > > > > > MsgBox "no record"
> > > > > > > > > > End If

Reply to this Message

Klatuu - 01 May 2007 22:53 GMT

Okay, then the syntax appears to be correct. There is some other problem.
Did you try entering it in the immediate window?
This should not be that difficult because it is a very technique that is
used all the time.

Signature

Dave Hargis, Microsoft Access MVP

> It is a text field.
>
[quoted text clipped - 99 lines]
> > > > > > > > > > > MsgBox "no record"
> > > > > > > > > > > End If

Reply to this Message

Chris - 01 May 2007 23:01 GMT

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
[quoted text clipped - 97 lines]
> > > > > > > > > > MsgBox "no record"
> > > > > > > > > > End If

Reply to this Message

Klatuu - 01 May 2007 23:07 GMT

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.

Signature

Dave Hargis, Microsoft Access MVP

> Immediate window results = compile error. expected: line number or label or
> statement or end of statement.
[quoted text clipped - 116 lines]
> > > > > > > > > > > MsgBox "no record"
> > > > > > > > > > > End If

Reply to this Message

Chris - 01 May 2007 23:19 GMT

I found that we missed a quote at the end of the source. Once in place, the
immediate window returned the ID for when a record is present; null when none
was.

I corrected our earlier codes and still no joy. When a record exists for a
client I get the message; nothing when it does not exist for a client.

I'm leaving too...hope you can see something in the morning.

Signature

Thanks for your help,
Chris

> 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
[quoted text clipped - 127 lines]
> > > > > > > > > > > > MsgBox "no record"
> > > > > > > > > > > > End If

Reply to this Message

Klatuu - 02 May 2007 14:17 GMT

Because it works in the immediate window, we know that it does work and that
it is not a reference problem. There has to be some other conflict we are
not seeing.

Signature

Dave Hargis, Microsoft Access MVP

> I found that we missed a quote at the end of the source. Once in place, the
> immediate window returned the ID for when a record is present; null when none
[quoted text clipped - 136 lines]
> > > > > > > > > > > > > MsgBox "no record"
> > > > > > > > > > > > > End If

Reply to this Message

Chris - 02 May 2007 14:19 GMT

I can't see it.

Signature

Thanks for your help,
Chris

> Because it works in the immediate window, we know that it does work and that
> it is not a reference problem. There has to be some other conflict we are
[quoted text clipped - 140 lines]
> > > > > > > > > > > > > > MsgBox "no record"
> > > > > > > > > > > > > > End If

Reply to this Message

BruceM - 02 May 2007 13:39 GMT

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
[quoted text clipped - 155 lines]
>> > > > > > > > > > > MsgBox "no record"
>> > > > > > > > > > > End If

Reply to this Message

Chris - 02 May 2007 14:20 GMT

Thanks. That is what I was referring to in my last post. Any ideas?

Signature

Thanks for your help,
Chris

> I think there's a missing quote mark at the right side of [tbl 1
> ClientNoteGeneral]:
[quoted text clipped - 160 lines]
> >> > > > > > > > > > > MsgBox "no record"
> >> > > > > > > > > > > End If

Reply to this Message

BruceM - 02 May 2007 15:13 GMT

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?

> Thanks. That is what I was referring to in my last post. Any ideas?
>
[quoted text clipped - 183 lines]
>> >> > > > > > > > > > > MsgBox "no record"
>> >> > > > > > > > > > > End If

Reply to this Message

Chris - 02 May 2007 15:46 GMT

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.

Signature

Thanks for your help,
Chris

> Ah. I thought you meant something else.
>
[quoted text clipped - 197 lines]
> >> >> > > > > > > > > > > MsgBox "no record"
> >> >> > > > > > > > > > > End If

Reply to this Message

BruceM - 02 May 2007 16:41 GMT

How about posting the code that worked so that all may benefit?

> Thanks to you both.
>
[quoted text clipped - 229 lines]
>> >> >> > > > > > > > > > > MsgBox "no record"
>> >> >> > > > > > > > > > > End If

Reply to this Message

Chris - 02 May 2007 20:00 GMT

I would if there was something to benefit others. What I was trying to
resolve never got resolved. My approach to the bigger picture (of which the
issue was a very small part) was altered and does not lend itself to others'
benefit.

Again, if there was benefit I would do so. Nothing to report regarding my
issue.

Signature

Thanks for your help,
Chris

> How about posting the code that worked so that all may benefit?
>
[quoted text clipped - 218 lines]
> >> >> >> > > > > > > > > > > I am using the following code to determine if a
> >> >> >> > > > > > > > > > > record is present, then doing

Reply to this Message

BruceM - 02 May 2007 21:03 GMT

Oh. I thought you had come up with something. Well, good luck then.

>I would if there was something to benefit others. What I was trying to
> resolve never got resolved. My approach to the bigger picture (of which
[quoted text clipped - 258 lines]
>> >> >> >> > > > > > > > > > > if a
>> >> >> >> > > > > > > > > > > record is present, then doing

Reply to this Message

Sign In

Join

My Latest Posts

My Monitored Threads

My Photo Gallery

My Homepage

Start New Thread

Enable EMail Alerts

Rate this Thread

©2008 Advenet LLC Privacy Policy - Terms of Use

This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.