use dmax to increase last number by 1

[SLP]

Hi. I am using the following code to automatically increase the last number
in a field by 1. The field is fldCenterID from the table tblCostCenters. It
works fine until I try to create a record after the ID of 10. Everything
there after is a 10. Any ideas on what is wrong? Thanks.

Dim strMax As String
strMax = DMax("fldCenterID", "tblCostCenters")
Me!HiddenCtl = Right(strMax, Len(strMax) - InStr(1, strMax, "-")) + 1

 

[John Spencer]

You probably have string values in fldCenterID. So when you use DMAX it is
returning "9" which as a string is greater than "10".

You need store your strings with leading zeroes, change the field type to a
number (long), or change the routine. Since you also seem to have a dash
and other characters in the fldCenterID this is going to be more complex.

Post some examples of your fldCenterID's.

The best solution would probably be to break the fldCenterId into its
component parts and store them in separate fields in the table.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[SLP]

Thanks for the response. The CenterIDs are 1, 2, 3, 4, etc. I can strip the
dash since it really isn't needed. I thought this would work for make it a
Long Number but getting a compile error with the code:

Me!HiddenCtl = Nz(DMax(CLng([fldCenterID])","tblCostCenters"),0)+1

Any ideas would be appreciated.

 

[SLP]

Hi, It works now for some reason. Thanks for your suggestion.

Thanks for the response. The CenterIDs are 1, 2, 3, 4, etc. I can strip the
dash since it really isn't needed. I thought this would work for make it a
Long Number but getting a compile error with the code:

Me!HiddenCtl = Nz(DMax(CLng([fldCenterID])","tblCostCenters"),0)+1

Any ideas would be appreciated.

You probably have string values in fldCenterID. So when you use DMAX it is
returning "9" which as a string is greater than "10".

You need store your strings with leading zeroes, change the field type to a
number (long), or change the routine. Since you also seem to have a dash
and other characters in the fldCenterID this is going to be more complex.

Post some examples of your fldCenterID's.

The best solution would probably be to break the fldCenterId into its
component parts and store them in separate fields in the table.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John Spencer]

I think you meant

Me!HiddenCtl = Nz(DMax("CLng([fldCenterID])","tblCostCenters"),0)+1

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi, It works now for some reason. Thanks for your suggestion.

Thanks for the response. The CenterIDs are 1, 2, 3, 4, etc. I can strip the
dash since it really isn't needed. I thought this would work for make it a
Long Number but getting a compile error with the code:

Me!HiddenCtl = Nz(DMax(CLng([fldCenterID])","tblCostCenters"),0)+1

Any ideas would be appreciated.

You probably have string values in fldCenterID. So when you use DMAX it is
returning "9" which as a string is greater than "10".

You need store your strings with leading zeroes, change the field type to a
number (long), or change the routine. Since you also seem to have a dash
and other characters in the fldCenterID this is going to be more complex.

Post some examples of your fldCenterID's.

The best solution would probably be to break the fldCenterId into its
component parts and store them in separate fields in the table.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi. I am using the following code to automatically increase the last
number
in a field by 1. The field is fldCenterID from the table tblCostCenters.
It
works fine until I try to create a record after the ID of 10. Everything
there after is a 10. Any ideas on what is wrong? Thanks.

Dim strMax As String
strMax = DMax("fldCenterID", "tblCostCenters")
Me!HiddenCtl = Right(strMax, Len(strMax) - InStr(1, strMax, "-")) + 1

 

[SLP]

Yes. When I wrote it the second time I remembered the quote before CLng.
Thanks again.

I think you meant

Me!HiddenCtl = Nz(DMax("CLng([fldCenterID])","tblCostCenters"),0)+1

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi, It works now for some reason. Thanks for your suggestion.

Thanks for the response. The CenterIDs are 1, 2, 3, 4, etc. I can strip the
dash since it really isn't needed. I thought this would work for make it a
Long Number but getting a compile error with the code:

Me!HiddenCtl = Nz(DMax(CLng([fldCenterID])","tblCostCenters"),0)+1

Any ideas would be appreciated.

:

You probably have string values in fldCenterID. So when you use DMAX it is
returning "9" which as a string is greater than "10".

You need store your strings with leading zeroes, change the field type to a
number (long), or change the routine. Since you also seem to have a dash
and other characters in the fldCenterID this is going to be more complex.

Post some examples of your fldCenterID's.

The best solution would probably be to break the fldCenterId into its
component parts and store them in separate fields in the table.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi. I am using the following code to automatically increase the last
number
in a field by 1. The field is fldCenterID from the table tblCostCenters.
It
works fine until I try to create a record after the ID of 10. Everything
there after is a 10. Any ideas on what is wrong? Thanks.

Dim strMax As String
strMax = DMax("fldCenterID", "tblCostCenters")
Me!HiddenCtl = Right(strMax, Len(strMax) - InStr(1, strMax, "-")) + 1