Assigning a specific # to a new record

[gguy]

I am trying to add a new record in a switchboard. Each record needs to have a certificate ID, which is based on the year and record number. For example, 20020001 would be the first certificate number.
When I choose to enter a new certificate, I want the certificate number to add one to the previous cert number and appear automatically on the new certificate form.

 

[Fizzio]

A couple of ways you can do this using the dmax function or a combination of Date() and an autonumber field.

Using Dmax

In the form where you are inputting the records,

On the form's On Current

Dim intYearDiff as Integer
intYearDiff = Format(Date(),"yyyy" - left(DMax("[CertificateID]","Name of Table"),4) 'creates the difference between the years so that each year, index begins at yyyy0001

If Me.NewRecord then
Select Case intYearDiff

Case 0
Me.[CertificateID] = DMax("[CertificateID]","Name of Table") + 1

Case 1
Me.[CertificateID] = Format(Date(),"yyyy") & "0001"

End select
End If

HTH

 

[gguy]

This is exactly what I was looking for, Thanks for your help! GGuy

 

[gguy]

Closing always saves current record

The auto numbering works great but when I get out of the form the new record is automatically saved. In most cases I want it to save the record but right now I just want to look at the record and make sure the next number in the sequence is appearing in the Cert ID field. I want to close the form without that record appearing in the table.
I thought I might accomplish this using a command button to save and another to close without saving. In the command button to close without saving I tried a docmd.close and a close macro in the on click. Neither was successful each time a record was created with the correct Cert ID and all other fields zero filled.
I need to jump into this form and jump back out without a new record being created each time.
Thanks in advance for any help. GGuy

 

[Fizzio]

How you can do this is a couple of ways. If you move the code to before_insert, it will only create the certificateID when you start typing a new record (but it will not show the new CertificateID until then)
Or alternatively, have an Exit Button. On the OnClick event of the button, have the first command as
Me.Undo 'Undo changes
DoCmd.Close 'Close the form

The first method is the better one but it will not show the new certificate ID.

HTH

 

[gguy]

Hey, thanks, I know the first choice is better and I understand why but I think, at least for now, I will use the second choice. I've added it and it works well. Thanks again, GGuy