counter function

[Peter]

Hi everyone,

I had some trouble determining if my question belonged in the forms.coding
newsgroup or in the modules.coding newsgroup. I decided to post it in both.

I have a table called T01_ClientFollowUpNo with a couple of fields, but
below are the fields that matter for my question:

ClientNo FollowUpNo
32501 001 (the 00 part is just formatting)
32501 002
39700 001
55520 001
55520 002
55520 003

ClientNo = long integer
FollowUpNo = long integer. First it was just an integer, but while putting
this problem into words I figured that might be the problem, so I changed
the data type. No gain, though. So here it is:

I want to have a function that is to be used in a form, so that when I add a
new FollowUpNo to a ClientNo (using a the AfterUpdate event for the combobox
control used to select the ClientNo) the next FollowUpNo is automatically
set as the largest FollowUpNo for that ClientNo sof ar found in the actual
table, but then plus one of course.

This is what I have so far:

Function NextFollowUpNo(lngClientNo as Long) as Long
Dim lngNumber as Long
lngNumber = Nz(DMax("[FollowUpNo]", "T01_ClientFollowUpNo", "[ClientNo] = '"
& lngClientNo & "'"), 1)
NextFollowUpNo = lngNumber
End Function

When I test this function in the immediate window using 32501 as the
required parameter for lngClientNo , I get an error message saying: Data
type mismatch in criteria expression.

Anyone know what is wrong here?

Greetings,
Peter

[Jeff Boyce]

responded to in your other post...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Peter" <scot...@xs4all.nl> wrote in message
news:4c0ff02c$0$23601$e4fe...@dreader25.news.xs4all.nl...

[Peter]

This is the answer I got back from Jeff Boyce (thanks again) in the other
newsgroup I mentioned in my earlier message.

"Peter" <scot...@xs4all.nl> schreef in bericht
news:4c0ff02c$0$23601$e4fe...@dreader25.news.xs4all.nl...

[Bob Quintal]

"Peter" <scot...@xs4all.nl> wrote in
news:4c0ff02c$0$23601$e4fe...@dreader25.news.xs4all.nl:

Because your ClientNo field is type long, you need to compare it to a
long integer, but you put apostrophes around lngClientNo, so it's
being converted to a string datatype. try this:

lngNumber = Nz(DMax("[FollowUpNo]", "T01_ClientFollowUpNo",

"[ClientNo] = " & lngClientNo ), 1)

You also need to fix
NextFollowUpNo = lngNumber + 1