AutoNumber & Prefix

[Steve Schapel]

Squirrel,

Yes, you are just storing that number in the field. So they will be
repeated every month.

The other stuff about the "BC" and the date reference and the "-" are
all for display purposes only, you won't be storing that in the table.
Well, the "yymm" is already stored in the table, in the DateNotified
field, so you don't want to store that same information in more than one
place. And the "BC" is the same for each record, so it's pointless
storing that. Even if you were to store it, it is a separate data
element, and therefore would need its own distinct field. So all you
are storing is the monthly serialising number.

 

[Guest]

Got it! I was a little unclear about where the format code went. I created a
textbox on my form and added that code. The [ID] field now has the "Nz" code
in the default value field and the text box now displays the correct value. I
had to go back and change the [ID] data in the table since there were
different months in there. I also added a new primary key since I couldn't
add duplicate values to the [ID] field. So any time that I want to carry this
format over to a report or a query I need to use the format that I put in the
textbox, correct?

Squirrel,

You should put the equivalent of this into the Default Value property of
the ID control on the form...

Nz(DMax("[ID]","YourTable","Format([DateNotified],'yymm')=Format(Date(),'yymm')"),0)+1
I don't know what it is named, I am just calling it ID because you
didn't say. You also forgot to tell us the name of the table. So in the
above expression, you need to change the [ID] for the actual name of
your field, and you need to change the "YourTable" for the actual name
of your table.

You shouldn't have anything entered in the Format property of the
control, if that's what you meant.

And yes, the field has to be a Number data type, as defined in the table
design.

--
Steve Schapel, Microsoft Access MVP

Ok I tried it but I'm getting a "#Error" in the field. When I put the code
that he wrote in the "Format" control it changed around the quotation marks
after I typed it in like he said. Is that normal? Also, I put what he said in
the "Default Value" control on the form. Should it go there or in the table's
default value? And when I open the form it shows the code I wrote in the
format control as the text in the actual field. Should it be a number field
or a text field? He called out a number field.

 

[Steve Schapel]

Brilliant, Squirrel!

Yes, you can use that same expression in the Control Source of a textbox
on form or report, whenever you need it. The alternative is making a
calculated field in the query that the form or report is based on, like...
AuthNo: "BC" & Format([DateNotified],"yymm") & "-" & Format([ID],"000")
.... and then make a textbox on the report which is bound to this query
field.

 

[Guest]

Hi Steve,

I have another question for you. Let's say I want to use a similar format
for the default value like you wrote below but with a few changes. What if I
wanted to crate a format like this: "BC" & "2 digit year" & a 2 digit
sequential #"

I don't want it to restart every month like the other one. I just want the
last 2 digits to increment throughout the year and then restart on the next
calendar year. How would I write this code?

Squirrel,

Ok, thanks for the clarification.

You can do what you want like this... Change the AutoNumber field to a
Number data type. For the example, I will assume the name of this field
is ID. On the form, set its Default Value to like this...

Nz(DMax("[ID]","YourTable","Format([DateNotified],'yymm')=Format(Date(),'yymm')"),0)+1

Use like this in your query or form or report, as mentioned before, to
get the format you want...
"BC" & Format([DateNotified],"yymm") & "-" & Format([ID],"000")

--
Steve Schapel, Microsoft Access MVP

The date is coming from a field called "DateNotified". It populates the
record with the current date when a new record is created.

 

[Steve Schapel]

Squirrel,

Similar concept. Set the Default Value like this...

Nz(DMax("[ID]","YourTable","Year([DateNotified])=Year(Date())"),0)+1

Then, for the output display:
"BC" & Format([DateNotified],"yy") & Format([ID],"00")

A 2-digit annually recurring number assumes you will never have more
than 99 in any given year?

 

[Guest]

That's a good point about the 2 digit part. These are generated #'s for
customer returns so I hope they're aren't more than 99 per year but you never
know!
Let me ask you a question. Can we have it go to 3 digits but only show
digits until it hits 100? So instead of showing 001 it would just show 01.

Also, to throw another kink into this process how would I tie this to a
combo box? I have a combo box that my users will select either "internal" or
"external". I only want this # to increment when "external" is selected from
the combobox and I only want this to show on records that have "external"
selected. So if someone was to select "internal" then the control that shows
the prefix # will be blank until a record is either selected that has
"external" selected or when a new record is selected using "external. So
basically I only want it to sequence the number only when it sees "external"
in my combo box. I hope I made sense here.

Squirrel,

Similar concept. Set the Default Value like this...

Nz(DMax("[ID]","YourTable","Year([DateNotified])=Year(Date())"),0)+1

Then, for the output display:
"BC" & Format([DateNotified],"yy") & Format([ID],"00")

A 2-digit annually recurring number assumes you will never have more
than 99 in any given year?

--
Steve Schapel, Microsoft Access MVP

Hi Steve,

I have another question for you. Let's say I want to use a similar format
for the default value like you wrote below but with a few changes. What if I
wanted to crate a format like this: "BC" & "2 digit year" & a 2 digit
sequential #"

I don't want it to restart every month like the other one. I just want the
last 2 digits to increment throughout the year and then restart on the next
calendar year. How would I write this code?

 

[Steve Schapel]

Squirrel,

Format([ID],"#00") will do it.

The easiest way to do this, given that you have already set up the
Default Value approach, would be to allow the Default Value to assign
the incremental number for a new record, and then to remove it again if
the "internal" option is selected. You would do that on the After
Update event of the combobox, using code such as...
If Me.YourCombobox = "internal" Then
Me.ID = Null
End If
Would that be satisfactory?

 

[Guest]

Hi Steve,

That will work but when I try to put the # sign into that line of code the #
sign disappears when I tab out of that line. It then goes back to
Format([ID],"00"). Will it still increment over 99?

Also, is there a way to have this control only visible when "External" is
selected from my combobox? I added this to your code but it doesn't seem to
be working

If Me.MyComboBox = "Internal" Then
Me.ID = Null
Me.DMR.Visible = False
Else
Me.DMR.Visible = True
End If

When I try and put this code in the "OnCurrent" event I get an error message
saying it can't find the control specified. Am I doing it wrong for the
"OnCurrent" event?

Squirrel,

Format([ID],"#00") will do it.

The easiest way to do this, given that you have already set up the
Default Value approach, would be to allow the Default Value to assign
the incremental number for a new record, and then to remove it again if
the "internal" option is selected. You would do that on the After
Update event of the combobox, using code such as...
If Me.YourCombobox = "internal" Then
Me.ID = Null
End If
Would that be satisfactory?

--
Steve Schapel, Microsoft Access MVP

That's a good point about the 2 digit part. These are generated #'s for
customer returns so I hope they're aren't more than 99 per year but you never
know!
Let me ask you a question. Can we have it go to 3 digits but only show
digits until it hits 100? So instead of showing 001 it would just show 01.

Also, to throw another kink into this process how would I tie this to a
combo box? I have a combo box that my users will select either "internal" or
"external". I only want this # to increment when "external" is selected from
the combobox and I only want this to show on records that have "external"
selected. So if someone was to select "internal" then the control that shows
the prefix # will be blank until a record is either selected that has
"external" selected or when a new record is selected using "external. So
basically I only want it to sequence the number only when it sees "external"
in my combo box. I hope I made sense here.

 

[Guest]

Hi Steve,

Ok so here's what I've done so far. I've got everything working ok except
for the "IF" statement. I put this statement in the "AfterUpdate" event of my
combobox:

If Me.NCMRType = "Internal" Then
Me.ID = Null
End If

And this statement in the "CurrentEvent" of the form:

If Me.NCMRType = "Internal" Then
Me.DMR.Visible = False
Else
Me.DMR.Visible = True
End If

It all works fine but if I was to delete the record I get a run-time error
'2424'.
If I remove the statement from the "CurrentEvent" of the form then the
delete function works without any errors. Am I setting up the code
incorrectly for the "CurrentEvent" procedure?

Squirrel,

Format([ID],"#00") will do it.

The easiest way to do this, given that you have already set up the
Default Value approach, would be to allow the Default Value to assign
the incremental number for a new record, and then to remove it again if
the "internal" option is selected. You would do that on the After
Update event of the combobox, using code such as...
If Me.YourCombobox = "internal" Then
Me.ID = Null
End If
Would that be satisfactory?

--
Steve Schapel, Microsoft Access MVP

That's a good point about the 2 digit part. These are generated #'s for
customer returns so I hope they're aren't more than 99 per year but you never
know!
Let me ask you a question. Can we have it go to 3 digits but only show
digits until it hits 100? So instead of showing 001 it would just show 01.

Also, to throw another kink into this process how would I tie this to a
combo box? I have a combo box that my users will select either "internal" or
"external". I only want this # to increment when "external" is selected from
the combobox and I only want this to show on records that have "external"
selected. So if someone was to select "internal" then the control that shows
the prefix # will be blank until a record is either selected that has
"external" selected or when a new record is selected using "external. So
basically I only want it to sequence the number only when it sees "external"
in my combo box. I hope I made sense here.

 

[Guest]

Hi Steve,
I'm back again. I've been using this new auto prefix and it seems to work ok
except when I go back and delete the last record it then stops working. It
won't increment the +1 at the end of the code you wrote below. Is there
something we can add to this to have it restart if a record is deleted?

Squirrel,

You should put the equivalent of this into the Default Value property of
the ID control on the form...

Nz(DMax("[ID]","YourTable","Format([DateNotified],'yymm')=Format(Date(),'yymm')"),0)+1
I don't know what it is named, I am just calling it ID because you
didn't say. You also forgot to tell us the name of the table. So in the
above expression, you need to change the [ID] for the actual name of
your field, and you need to change the "YourTable" for the actual name
of your table.

You shouldn't have anything entered in the Format property of the
control, if that's what you meant.

And yes, the field has to be a Number data type, as defined in the table
design.

--
Steve Schapel, Microsoft Access MVP

Ok I tried it but I'm getting a "#Error" in the field. When I put the code
that he wrote in the "Format" control it changed around the quotation marks
after I typed it in like he said. Is that normal? Also, I put what he said in
the "Default Value" control on the form. Should it go there or in the table's
default value? And when I open the form it shows the code I wrote in the
format control as the text in the actual field. Should it be a number field
or a text field? He called out a number field.

 

[Steve Schapel]

Squirrel,

I can't quite understand what's happening here. What method are you
using to delete a record? And do you mean you are deleting the very
last, most recently entered record, or do you mean a previous one? Ok,
so what's actually happening? After the record is deleted, you go to a
blank new record on the form, and the Default Value is... what?