Numeral Auto Increment in Fields

[Angel]

Hi

I have a problem with the primary key field in my database

I need to have a specific quote identifier - it cannot be changed from the companie's policy number

I need to have a field that looks like the following

S04/0001 Rev.

The 0001 needs to increment with each new record, but the revision number will only increment each time you duplicate a previous record. The year indicator obviously has to change with each new year too

Is this at all possible? I cannot seem to get Access to understand the way i want it to identify each record! I am clearly missing something obvious

Any help or ideas would be greatly appreciated!

 

[Allen Browne]

This field appears to contain 4 different things:
- S (whatever that represents);
- 04 = last 2 digits of year;
- 0001 = a counter;
- Rev.0 = a revision number.

Placing 4 things in one field violates one of the most basic rules of
normalization: keep the data atomic. Use 4 different fields: you can still
combine them into one for display purposes.

Suggested fields:
Something Text (1 char)
TheYear Number. (Integer) Requires 4 digits.
TheCount Number. (Integer)
Rev Number (integer, unless fractions).

To print the code on a report, use a text box with ControlSource of:
=[Something] & Right([TheYear], 2) & "/" & Format([TheCount], "0000") & "
Rev. " & [Rev]

Now, to your question. To get the next available counter number when you
create a new record in a form, use the BeforeUpdate event procedure of the
form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
If Me.NewRecord Then
Me.TheYear = Year(Date)
Me.Rev = 0
strWhere = "(Something = """ & Me.Something & _
""") AND (TheYear = " & Me.TheYear & ")"
Me.TheCount = Nz(DMax("TheCount", "MyTable", strWhere), 0) + 1
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I have a problem with the primary key field in my database.

I need to have a specific quote identifier - it cannot be changed from the companie's policy number.

I need to have a field that looks like the following:

S04/0001 Rev. 0

The 0001 needs to increment with each new record, but the revision number

will only increment each time you duplicate a previous record. The year
indicator obviously has to change with each new year too!

Is this at all possible? I cannot seem to get Access to understand the

way i want it to identify each record! I am clearly missing something
obvious!

 

[Steve Schapel]

Angel,

I would say the key here is that the "company policy" is only relevant
to the display of the quote identifier when used on forms or reports.
How it is stored at a table level, which no-one will see, is another
matter. You are trying to store three distinct pieces of information
in the one field. I would suggest you keep the core quote number, as
a number, in one field, the revision number, as a number, in another
field. The year is probably already there somehwere ina date field.
Then, whenever you need to output the quote number, generate it within
the query that the form or report is based on, or in the control
source of an unbound textbox on the form or report, something like...
="S" & Right$(Year([DateField]),2) & "/" & Format([CoreNumber],"0000"
& " Rev. " & [RevNumber]

- Steve Schapel, Microsoft Access MVP

 

[Angel]

Hi Steve

Sorry i took so long to get back to you, i was off sick! Thanks so much for the help

One more question - How do i get this to appear in the right way, and increment properly within the tables and the user input form? They need to have a unique number whenever they create a new quote (whether that be a whole new quote number or just a new revision! I can see now how i could combine these numbers in the reports / queries, but i still don;t know how to increment them

The trick here is getting the bloody things to LOOK right on screen when the data is being input

Any ideas

Thanks - Angel (AKA: Depserate to get this problem out of my hair)

 

[Angel]

Hi Allen

Thanks for getting back to me. All your assistance is great

Just one more question? How do i get the revision number to increment properly. I want it to start on zerowith each new quote, but each time you open an old quote, and say press a button "Revise current quote", i want it to copy all the current records to the same quote number, but with the next revision number

Any idea
Shot for the help, Angel

 

[Allen Browne]

Use DMax() to get the maximum Rev number also.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Thanks for getting back to me. All your assistance is great!

Just one more question? How do i get the revision number to increment

properly. I want it to start on zerowith each new quote, but each time you
open an old quote, and say press a button "Revise current quote", i want it
to copy all the current records to the same quote number, but with the next
revision number.