Custom Counter Code

[DS]

I dont want to use autonumber, I want a custom code. This is what I
have so far.

If [SType]=1 Then
Nz(DMax("SalesID","Sales","E" & [SalesID])+1
ElseIf [SType]=2 Then
Nz(DMax("SalesID","Sales","D" & [SalesID])+1
ElseIf [SType]=3 Then
Nz(DMax("SalesID","Sales","T" & [SalesID])+1
ElseIf [SType]=4 Then
Nz(DMax("SalesID","Sales","P" & [SalesID])+1
ElseIf [SType]=5 Then
Nz(DMax("SalesID","Sales","Q" & [SalesID])+1
End If

Also, what are the downsides to doing this. This will be over a Network
with multiple users accessing the Database at once.
What happens when a number is deleted? I still want a record that it
was deleted. I don't want the number used over again. Any help is
appreciated.
Thanks
DS

 

[Douglas J Steele]

WIth multiple users, it's possible that two users may grab the same "next
number" before the data's committed. You'll need to have code to handle this
in your application (the second user to try to insert will get a "Duplicate
Entry" error. Grab a new key for them and try again)

What happens when a number's deleted varies. If a number in the middle is
deleted, nothing will happen: the deleted number will not get reused. If the
last number assigned is deleted, that number will be reused if you
physically deleted the record. If all you did was mark it as deleted, so
that there's still a record in the table, it won't be reused.

 

[DS]

WIth multiple users, it's possible that two users may grab the same "next
number" before the data's committed. You'll need to have code to handle this
in your application (the second user to try to insert will get a "Duplicate
Entry" error. Grab a new key for them and try again)

What happens when a number's deleted varies. If a number in the middle is
deleted, nothing will happen: the deleted number will not get reused. If the
last number assigned is deleted, that number will be reused if you
physically deleted the record. If all you did was mark it as deleted, so
that there's still a record in the table, it won't be reused.

Thanks Doug,
Heres where I am now. The previous code is not needed. SalesID is a
Number and SType is a Letter. I have this on the before update of the
form but it's not working. Also any samples of the code out there that
you mentioned. Thank you for your help.
DS

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.SalesID) = True Then
Me.SalesID = Nz(DMax("SalesID", "Sales", "SalesID = SType &
SalesID")) + 1
End If
End Sub

 

[DS]

Thanks Doug,
Heres where I am now. The previous code is not needed. SalesID is a
Number and SType is a Letter. I have this on the before update of the
form but it's not working. Also any samples of the code out there that
you mentioned. Thank you for your help.
DS

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.SalesID) = True Then
Me.SalesID = Nz(DMax("SalesID", "Sales", "SalesID = SType &
SalesID")) + 1
End If
End Sub

This works on the Before Insert Event, but I still need to marry the
Stype field and the SalesID field together.

Me!SalesID = Nz(DMax("[SalesID]", "Sales")) + 1

I tried this but it doesn't work.

Me!SalesID = Nz(DMax("[SalesID]", "Sales", "SalesID = Stype & SalesID")) + 1

Thanks
DS

 

[DS]

Thanks Doug,
Heres where I am now. The previous code is not needed. SalesID is a
Number and SType is a Letter. I have this on the before update of the
form but it's not working. Also any samples of the code out there
that you mentioned. Thank you for your help.
DS

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.SalesID) = True Then
Me.SalesID = Nz(DMax("SalesID", "Sales", "SalesID = SType &
SalesID")) + 1
End If
End Sub

This works on the Before Insert Event, but I still need to marry the
Stype field and the SalesID field together.

Me!SalesID = Nz(DMax("[SalesID]", "Sales")) + 1

I tried this but it doesn't work.

Me!SalesID = Nz(DMax("[SalesID]", "Sales", "SalesID = Stype & SalesID"))
+ 1

Thanks
DS

The DMax is working but I can't set the format of the field...

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!SalesID = Nz(DMax("[SalesID]", "Sales"), 0) + 1
SalesID = Format([SType], "SalesID")
End Sub

I need the final number to look like this D1 or it maybe T1, Q1, E1, P1
D, T, E, P, or Q being the SType Field.



Any ideas why this isn't working?
Thanks
DS

 

[DS]

Douglas J Steele wrote:

WIth multiple users, it's possible that two users may grab the same
"next
number" before the data's committed. You'll need to have code to
handle this
in your application (the second user to try to insert will get
"Duplicate
Entry" error. Grab a new key for them and try again)

What happens when a number's deleted varies. If a number in the
middle is
deleted, nothing will happen: the deleted number will not get
reused. If the
last number assigned is deleted, that number will be reused if you
physically deleted the record. If all you did was mark it as
deleted, so
that there's still a record in the table, it won't be reused.

Thanks Doug,
Heres where I am now. The previous code is not needed. SalesID is a
Number and SType is a Letter. I have this on the before update of
the form but it's not working. Also any samples of the code out
there that you mentioned. Thank you for your help.
DS

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.SalesID) = True Then
Me.SalesID = Nz(DMax("SalesID", "Sales", "SalesID = SType &
SalesID")) + 1
End If
End Sub

This works on the Before Insert Event, but I still need to marry the
Stype field and the SalesID field together.

Me!SalesID = Nz(DMax("[SalesID]", "Sales")) + 1

I tried this but it doesn't work.

Me!SalesID = Nz(DMax("[SalesID]", "Sales", "SalesID = Stype &
SalesID")) + 1

Thanks
DS

The DMax is working but I can't set the format of the field...

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!SalesID = Nz(DMax("[SalesID]", "Sales"), 0) + 1
SalesID = Format([SType], "SalesID")
End Sub

I need the final number to look like this D1 or it maybe T1, Q1, E1, P1
D, T, E, P, or Q being the SType Field.



Any ideas why this isn't working?
Thanks
DS

I have this but I'm getting a "Type MisMatch Error"

Me!SalesID = SType & Nz(DMax("[SalesID]", "Sales"), 0) + 1

"SType" is a text field as is SalesID

It works if I replace SType with a letter or a number but not a field name.
Thanks
DS

 

[Douglas J. Steele]

DS wrote:

Douglas J Steele wrote:

WIth multiple users, it's possible that two users may grab the same
"next
number" before the data's committed. You'll need to have code to
handle this
in your application (the second user to try to insert will get
"Duplicate
Entry" error. Grab a new key for them and try again)

What happens when a number's deleted varies. If a number in the middle
is
deleted, nothing will happen: the deleted number will not get reused.
If the
last number assigned is deleted, that number will be reused if you
physically deleted the record. If all you did was mark it as deleted,
so
that there's still a record in the table, it won't be reused.

Thanks Doug,
Heres where I am now. The previous code is not needed. SalesID is a
Number and SType is a Letter. I have this on the before update of the
form but it's not working. Also any samples of the code out there that
you mentioned. Thank you for your help.
DS

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.SalesID) = True Then
Me.SalesID = Nz(DMax("SalesID", "Sales", "SalesID = SType &
SalesID")) + 1
End If
End Sub



This works on the Before Insert Event, but I still need to marry the
Stype field and the SalesID field together.

Me!SalesID = Nz(DMax("[SalesID]", "Sales")) + 1

I tried this but it doesn't work.

Me!SalesID = Nz(DMax("[SalesID]", "Sales", "SalesID = Stype & SalesID"))
+ 1

Thanks
DS

The DMax is working but I can't set the format of the field...

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!SalesID = Nz(DMax("[SalesID]", "Sales"), 0) + 1
SalesID = Format([SType], "SalesID")
End Sub

I need the final number to look like this D1 or it maybe T1, Q1, E1, P1
D, T, E, P, or Q being the SType Field.



Any ideas why this isn't working?
Thanks
DS

I have this but I'm getting a "Type MisMatch Error"

Me!SalesID = SType & Nz(DMax("[SalesID]", "Sales"), 0) + 1

"SType" is a text field as is SalesID

It works if I replace SType with a letter or a number but not a field
name.

Sorry I haven't been around...

Since SalesID is text, you can't add one to it. You could strip off the
first letter and convert to a number before adding:

Me!SalesID = SType & CLng(Mid(Nz(DMax("[SalesID]", "Sales"), "A0"), 2)) + 1

Are you trying to get T1, T2, T3, Q1, Q2, Q3, ..., or is T1, Q2, E3, E4, Q5,
P6 good enough (because all you're going to get with your code is the
latter)

Assuming you're trying to get T1, T2, T3..., what you want is

Me!SalesID = SType & CLng(Mid(Nz(DMax("[SalesID]", "Sales", "Left([SalesId],
1) = '" & SType & "'"), "A0"), 2)) + 1

(note that's ' " & SType & " ' ")

However, you're going to run into an issue. Since your SalesId is text,
you're going to find that it sorts T1, T10, T2, T3, T4, T5, T6, T7, T8, T9.
In other words, T9 is always going to be your highest value.

You're either going to have to pad with zeroes, or (more correct) store
strictly the number and concatenate SType and the number for display
purposes (make the primary key the combination of SType and the number,
rather than strictly the one field).

If you go the pad with zeroes route, how many zeros you put is up to you.
Will T999 be as large as you need? If so, use

Me!SalesID = SType & Format(CLng(Mid(Nz(DMax("[SalesID]", "Sales",
"Left([SalesId], 1) = '" & SType & "'"), "A0"), 2)) + 1, "000")

Will you need T9999? Replace the "000" at the end with "0000"

 

[DS]

DS wrote:

DS wrote:


DS wrote:


Douglas J Steele wrote:


WIth multiple users, it's possible that two users may grab the same
"next
number" before the data's committed. You'll need to have code to
handle this
in your application (the second user to try to insert will get
"Duplicate
Entry" error. Grab a new key for them and try again)

What happens when a number's deleted varies. If a number in the middle
is
deleted, nothing will happen: the deleted number will not get reused.
If the
last number assigned is deleted, that number will be reused if you
physically deleted the record. If all you did was mark it as deleted,
so
that there's still a record in the table, it won't be reused.


Thanks Doug,
Heres where I am now. The previous code is not needed. SalesID is a
Number and SType is a Letter. I have this on the before update of the
form but it's not working. Also any samples of the code out there that
you mentioned. Thank you for your help.
DS

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.SalesID) = True Then
Me.SalesID = Nz(DMax("SalesID", "Sales", "SalesID = SType &
SalesID")) + 1
End If
End Sub



This works on the Before Insert Event, but I still need to marry the
Stype field and the SalesID field together.

Me!SalesID = Nz(DMax("[SalesID]", "Sales")) + 1

I tried this but it doesn't work.

Me!SalesID = Nz(DMax("[SalesID]", "Sales", "SalesID = Stype & SalesID"))
+ 1

Thanks
DS

The DMax is working but I can't set the format of the field...

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!SalesID = Nz(DMax("[SalesID]", "Sales"), 0) + 1
SalesID = Format([SType], "SalesID")
End Sub

I need the final number to look like this D1 or it maybe T1, Q1, E1, P1
D, T, E, P, or Q being the SType Field.



Any ideas why this isn't working?
Thanks
DS

I have this but I'm getting a "Type MisMatch Error"

Me!SalesID = SType & Nz(DMax("[SalesID]", "Sales"), 0) + 1

"SType" is a text field as is SalesID

It works if I replace SType with a letter or a number but not a field
name.

Sorry I haven't been around...

Since SalesID is text, you can't add one to it. You could strip off the
first letter and convert to a number before adding:

Me!SalesID = SType & CLng(Mid(Nz(DMax("[SalesID]", "Sales"), "A0"), 2)) + 1

Are you trying to get T1, T2, T3, Q1, Q2, Q3, ..., or is T1, Q2, E3, E4, Q5,
P6 good enough (because all you're going to get with your code is the
latter)

Assuming you're trying to get T1, T2, T3..., what you want is

Me!SalesID = SType & CLng(Mid(Nz(DMax("[SalesID]", "Sales", "Left([SalesId],
1) = '" & SType & "'"), "A0"), 2)) + 1

(note that's ' " & SType & " ' ")

However, you're going to run into an issue. Since your SalesId is text,
you're going to find that it sorts T1, T10, T2, T3, T4, T5, T6, T7, T8, T9.
In other words, T9 is always going to be your highest value.

You're either going to have to pad with zeroes, or (more correct) store
strictly the number and concatenate SType and the number for display
purposes (make the primary key the combination of SType and the number,
rather than strictly the one field).

If you go the pad with zeroes route, how many zeros you put is up to you.
Will T999 be as large as you need? If so, use

Me!SalesID = SType & Format(CLng(Mid(Nz(DMax("[SalesID]", "Sales",
"Left([SalesId], 1) = '" & SType & "'"), "A0"), 2)) + 1, "000")

Will you need T9999? Replace the "000" at the end with "0000"

Thanks for being the Calavary!!! I'm at my wits end on this one....
What I need is it to be The Latter...

""""Are you trying to get T1, T2, T3, Q1, Q2, Q3, ..., or is T1, Q2, E3,
E4, Q5, P6 good enough (because all you're going to get with your code
is the latter""

So I have the SalesID set as a Number field and the SType set as a Text
field. I want to add the SType to the SalesID.

Can we keep it as the Sales ID as a Number field with the one Letter in
front so it sorts by number....indefintely?
Thanks
Doug

 

[DS]

DS wrote:


DS wrote:


DS wrote:


Douglas J Steele wrote:


WIth multiple users, it's possible that two users may grab the
same "next
number" before the data's committed. You'll need to have code to
handle this
in your application (the second user to try to insert will get
"Duplicate
Entry" error. Grab a new key for them and try again)

What happens when a number's deleted varies. If a number in the
middle is
deleted, nothing will happen: the deleted number will not get
reused. If the
last number assigned is deleted, that number will be reused if you
physically deleted the record. If all you did was mark it as
deleted, so
that there's still a record in the table, it won't be reused.


Thanks Doug,
Heres where I am now. The previous code is not needed. SalesID
is a Number and SType is a Letter. I have this on the before
update of the form but it's not working. Also any samples of the
code out there that you mentioned. Thank you for your help.
DS

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.SalesID) = True Then
Me.SalesID = Nz(DMax("SalesID", "Sales", "SalesID = SType &
SalesID")) + 1
End If
End Sub




This works on the Before Insert Event, but I still need to marry
the Stype field and the SalesID field together.

Me!SalesID = Nz(DMax("[SalesID]", "Sales")) + 1

I tried this but it doesn't work.

Me!SalesID = Nz(DMax("[SalesID]", "Sales", "SalesID = Stype &
SalesID")) + 1

Thanks
DS


The DMax is working but I can't set the format of the field...

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!SalesID = Nz(DMax("[SalesID]", "Sales"), 0) + 1
SalesID = Format([SType], "SalesID")
End Sub

I need the final number to look like this D1 or it maybe T1, Q1, E1, P1
D, T, E, P, or Q being the SType Field.



Any ideas why this isn't working?
Thanks
DS


I have this but I'm getting a "Type MisMatch Error"

Me!SalesID = SType & Nz(DMax("[SalesID]", "Sales"), 0) + 1

"SType" is a text field as is SalesID

It works if I replace SType with a letter or a number but not a field
name.

Sorry I haven't been around...

Since SalesID is text, you can't add one to it. You could strip off
the first letter and convert to a number before adding:

Me!SalesID = SType & CLng(Mid(Nz(DMax("[SalesID]", "Sales"), "A0"),
2)) + 1

Are you trying to get T1, T2, T3, Q1, Q2, Q3, ..., or is T1, Q2, E3,
E4, Q5, P6 good enough (because all you're going to get with your code
is the latter)

Assuming you're trying to get T1, T2, T3..., what you want is

Me!SalesID = SType & CLng(Mid(Nz(DMax("[SalesID]", "Sales",
"Left([SalesId], 1) = '" & SType & "'"), "A0"), 2)) + 1

(note that's ' " & SType & " ' ")

However, you're going to run into an issue. Since your SalesId is
text, you're going to find that it sorts T1, T10, T2, T3, T4, T5, T6,
T7, T8, T9. In other words, T9 is always going to be your highest value.

You're either going to have to pad with zeroes, or (more correct)
store strictly the number and concatenate SType and the number for
display purposes (make the primary key the combination of SType and
the number, rather than strictly the one field).

If you go the pad with zeroes route, how many zeros you put is up to
you. Will T999 be as large as you need? If so, use

Me!SalesID = SType & Format(CLng(Mid(Nz(DMax("[SalesID]", "Sales",
"Left([SalesId], 1) = '" & SType & "'"), "A0"), 2)) + 1, "000")

Will you need T9999? Replace the "000" at the end with "0000"

Thanks for being the Calavary!!! I'm at my wits end on this one....
What I need is it to be The Latter...

""""Are you trying to get T1, T2, T3, Q1, Q2, Q3, ..., or is T1, Q2, E3,
E4, Q5, P6 good enough (because all you're going to get with your code
is the latter""

So I have the SalesID set as a Number field and the SType set as a Text
field. I want to add the SType to the SalesID.

Can we keep it as the Sales ID as a Number field with the one Letter in
front so it sorts by number....indefintely?
Thanks
Doug

OK I started with this wrong and it said wrong type.
Is this assumning that the SalesID field is Text? Its a Number field.
Is that wrong?
Thanks
DS

Me!SalesID = SType & CLng(Mid(Nz(DMax("[SalesID]", "Sales",
"Left([SalesId],
1) = '" & SType & "'"), "A0"), 2)) + 1

 

[Douglas J Steele]

Sorry I haven't been around...

Since SalesID is text, you can't add one to it. You could strip off
the first letter and convert to a number before adding:

Me!SalesID = SType & CLng(Mid(Nz(DMax("[SalesID]", "Sales"), "A0"),
2)) + 1

Are you trying to get T1, T2, T3, Q1, Q2, Q3, ..., or is T1, Q2, E3,
E4, Q5, P6 good enough (because all you're going to get with your code
is the latter)

Assuming you're trying to get T1, T2, T3..., what you want is

Me!SalesID = SType & CLng(Mid(Nz(DMax("[SalesID]", "Sales",
"Left([SalesId], 1) = '" & SType & "'"), "A0"), 2)) + 1

(note that's ' " & SType & " ' ")

However, you're going to run into an issue. Since your SalesId is
text, you're going to find that it sorts T1, T10, T2, T3, T4, T5, T6,
T7, T8, T9. In other words, T9 is always going to be your highest value.

You're either going to have to pad with zeroes, or (more correct)
store strictly the number and concatenate SType and the number for
display purposes (make the primary key the combination of SType and
the number, rather than strictly the one field).

If you go the pad with zeroes route, how many zeros you put is up to
you. Will T999 be as large as you need? If so, use

Me!SalesID = SType & Format(CLng(Mid(Nz(DMax("[SalesID]", "Sales",
"Left([SalesId], 1) = '" & SType & "'"), "A0"), 2)) + 1, "000")

Will you need T9999? Replace the "000" at the end with "0000"

Thanks for being the Calavary!!! I'm at my wits end on this one....
What I need is it to be The Latter...

""""Are you trying to get T1, T2, T3, Q1, Q2, Q3, ..., or is T1, Q2, E3,
E4, Q5, P6 good enough (because all you're going to get with your code
is the latter""

So I have the SalesID set as a Number field and the SType set as a Text
field. I want to add the SType to the SalesID.

Can we keep it as the Sales ID as a Number field with the one Letter in
front so it sorts by number....indefintely?

OK I started with this wrong and it said wrong type.
Is this assumning that the SalesID field is Text? Its a Number field.

If it's a numeric field, you can't put text in it. A numeric field can only
be 1, 2, 3, not T1, T2, T3


As I said, it's probably better to have SalesID (as a number), and SType
(text) as separate fields in the table, with the primary key being both of
those fields.

To determine what the next ID should be, you'd use

Nz(DMax("[SalesID]", "Sales", "[SType] ='" & SType & "'"), "0") + 1

If you need to have T1, T2, etc on forms and reports, create a query with a
computed field that concatenates the two, and use the query wherever you
would otherwise have used the table.

In the query grid, you'd add a field SId: [SType] & [SalesId]