counter function

[Peter]

Hi everyone,

I had some trouble determining if my question belongs 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]

Peter

I think you'll probably want to have a "+1" in that expression, to add one
to the max number already in use.

If your [ClientNo] is a long integer, why are you surrounding it with
apostrophes (" ' ")? You use that when you are passing in a text value.

Good luck!

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]

I must say, I never ever got an answer back so incredibly fast. And it works
too.

Thank you Jeff !

Peter

I think you'll probably want to have a "+1" in that expression, to add one
to the max number already in use.

If your [ClientNo] is a long integer, why are you surrounding it with
apostrophes (" ' ")? You use that when you are passing in a text value.

Good luck!

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.

Hi everyone,

I had some trouble determining if my question belongs 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