Auto Call #

[ssworthi]

Is there a way to have a form create an auto number with a prefix of "CN-####". There is a data base one department uses and it has Case #, Call #, Incident # and I have been requested to create on their form auto numbers. I have some experience in auto #'s but not much and thought best to ask the experts. Thanks so much for any help!

 

[GinaWhipp]

Since you care about the numbers AND you can't control the AutoNumber field going to suggest you use DMax() in the Before_Insert of your form...

Me.YourField = DMax("YourIDField","YourTable") + 1

...and just store the numbers. You can display the lettering you want on the form and/or reports.

 

[John Big Booty]

Perhaps the attached sample will give you some pointers. It uses the DMax() function plus one to increment a counter. The prefix to indicate which office the number relates to is only added for the users benefit when the number is being displayed in a form or report.

 

[ssworthi]

I want to make sure that I understand where I am to do this. In the form properties there is a "Before_Insert" listed in the property fields. That I have a text box for (example Case # )on the form that in the Before Insert I use "Me.Case # = DMax("Case #,"Incident Tbl") + 1"

 

[ssworthi]

I wrote:
Me.[Case #] = DMax([Case #], [Incident Reports Table]) + 1

It gave me a "compile error". This is so new to me.

 

[GinaWhipp]

Yes, but Bracket because of spaces and wildcard characters...

Me![Case #] = DMax("[Case #]","[Incident Tbl]") + 1

 

[GinaWhipp]

What is the name of you field on the form?

 

[ssworthi]

Awesome; it worked. The number came out "61-235" Anyway to format it with month and year? That is so great! I just love this forum! Thank you so much!

 

[GinaWhipp]

Where and how do you want the month and year to show?

 

[ssworthi]

In the case # would be great. I don't know how it's calculating the 61-235; where would I look? Thanks so much for your help.

 

[GinaWhipp]

Okay, well you didn't say how you wanted it to look so here's try number one...

Me.[Case #] = DMax("[Case #]","[Incident Tbl]") + 1 & Year(Date()) & "." & Month(Date())

 

[ssworthi]

I wrote:
Me.[Case #] = DMax("[Case #]","[Incident Reports Table]") + 1 & Year(Date()) & "." & Month(Date())

It replied"Run-Time error '94': Invalid use of null

 

[GinaWhipp]

Nothing there is NULL, what did you do to execute it?

 

[ssworthi]

I just started a new record and that's what happened. This is what I wrote:
Me.[Case #] = DMax("[Case #]","[Incident Reports Table]") + 1 & Year(Date()) & "." & Month(Date())

Does this look correct or do I have a type-O?

 

[GinaWhipp]

Where exactly are you putting this line because it looks perfect...

 

[ssworthi]

I open the form in design view, went to form properties, and in the "before index" line I put in that code. Thanks so much for your help with this; I'm so challenged by code.

 

[ssworthi]

I just tried it again and it looks like some of the () are being dropped in the string.
Me.[Case #] = DMax("[Case #]", "[Incident Reports Table]") + 1 & Year(Date) & "." & Month(Date)
I actually went in and used the debugger and tried to add the () but it saved it with just the (Date).

 

[GinaWhipp]

It dropping the parentheses should happen in the Editor so no worries there. Oh dear, I see part of the problem, you can't run DMax against that field because we are adding Month and Year.

There is no Before_Index so did you mean Before_Update and that would be Before_Update of the form. However, with storing Month Year, is this a text field?

 

[ssworthi]

I put the event "before insert". Should it be before update? It works when I use the before insert without the date; it gives me a number that I have no idea where it comes from but it works.

 

[GinaWhipp]

It will work there and you can leave it there... it is the Month/Date that is going to cause the issue. Once I thought about it there is no way for it to get maximum value adding that.

Now I can either write something OR you can store Month/Tear in another column. Which do you prefer?