Consecutive Numbering

[kaylee]

Hi there!

I am working on a new database and have run into an issue. In my old database, I had a table called Punch Cards and I used it more like a spreadsheet... entering each new punch card one after another. In my new database, I have a main form called Client Profile Form and there are multiple subforms (one of which is for Punch Cards). My problem is this.... I want to be able to have the next consecutive punch card number enter when I start entering data in the punch card subform. Is there a way to do this without removing all the data and changing it to auto number? I would really prefer to keep my punch card number format as it is.



Any thoughts or suggestions are greatly appreciated!

[pbaldy]

The DMax() is often used in this situation:

http://www.baldyweb.com/CustomAutonumber.htm

[DougsGraphics]

Like pbaldy said, use DMax()
Set the default value field for the input control as =DMax("Punchcard_Number_Field", "Punch_Card_Table",) + 1 as the default value for the control. You can set the control to "locked" and be confident it can't be changed. Optionally, you could set an event trigger to populate the field and do it all through VBA.

[kaylee]

I am still having trouble with this. I am only familiar with the basics of Access and this is far beyond my capabilities. I am going to send a couple screen shots.

[kaylee]

[DougsGraphics]

So, here's the challenge with what you are trying to do. I will presume for the moment that the "-16" represents the year? If so, I would recommend you strip it off using an update query since you have a date field and you can always combine the two to create the format you are displaying. It would make life easier. Because I find that Domain Aggregate functions are not supported as default values, the only way you will be able to do this is to set an event trigger in your form and use VBA to generate and assign a default value. The following code can be used to generate your value (again, presuming the last two digits refer to the year):

Left(DMax("[Punch Card #]","[Punch Cards]"),3)+1 & "-" & Right(Year(Now()),2)

That will have to be executed off some triggering event such as a command button set to issue a new card that would auto populate that field. Hope that helps some...

[kaylee]

I can't get this code to work. I'm getting an unknown function error message.

[DougsGraphics]

Kaylee,
Here is one way you can tackle it -- I will try to give you enough detail to get something you can build on.
First, add a command button to the detail section of your form and caption it something like "Add New". (Important: your form will have to be displayed as a continuous form for this to work, it won't work in spreadsheet view.)
Next, in the event tab of the property sheet for the command button, make the "On Click" event set to [Event Procedure] by selecting the [...] button and picking "Code Builder".
Your VBA editor will come up with the "Private Sub ButtonName_Click()" and "End Sub" lines.
In between those lines, insert the DMax function code between those lines like this:

Private Sub YourButton_Click()
Me.Punch_Card__.Value = Left(DMax("[Punch Card #]", "[Table1]"), 3) + 1 & "-" & Right(Year(Now()), 2)
End Sub

(Note that access won't recognize your "Punch Card #" field unless you substitute underscores for spaces and the # symbol.)
Save the code and return to your form. Put your form back in form view and when the button is clicked it should populate the "Punch Card #" field with the next value.

(Sorry for the frustration)

[kaylee]

I changed the Punch Card # so the year (-16) is in another column. I tried the code you previously suggested (see photo) but it is still giving me an error code.

[kaylee]

So, here's the challenge with what you are trying to do. I will presume for the moment that the "-16" represents the year? If so, I would recommend you strip it off using an update query since you have a date field and you can always combine the two to create the format you are displaying. It would make life easier. Because I find that Domain Aggregate functions are not supported as default values, the only way you will be able to do this is to set an event trigger in your form and use VBA to generate and assign a default value. The following code can be used to generate your value (again, presuming the last two digits refer to the year):

Left(DMax("[Punch Card #]","[Punch Cards]"),3)+1 & "-" & Right(Year(Now()),2)

That will have to be executed off some triggering event such as a command button set to issue a new card that would auto populate that field. Hope that helps some...

I am having some success with this. Although when I tab to a new record, it is entering the same number again :S

[ssanfu]

FYI: A better naming convention would really help you.

Object names should be only letters and/or numbers.
Never begin a name with a number.
NO spaces, punctuation or special characters (except the underscore)!
Do not use reserved words as object names. (see: http://allenbrowne.com/AppIssueBadWord.html)

Bad field name: 'Punch Card #' ...... has spaces and the hash mark ('#' is a date delimiter)