Numbering fields

[posthelp]

I want to create two numbering fields with a relation between the two numbering fields.

a. The first numbering field named StampsInBook has three parts: 06-0001/01

- The first part is 06 refers to the collection composers and change never.
- The third part 00 is a count number, starting with 01 en change with +1 for each stamp. The maximum value is 50. When the stamp 50 is done three things happens - The number of the second part 0001 change +1 and becomes 06-0002
- The number of the third part begin again with 01. So with get 06-0002/01
- The second number field named Book change (see further)

b. The second number field named Book has two parts: 2006/01

- The first part 2006 refers to the year and have to change every year
- The second part depends from the action in third part of the first numbering field StampsInBoeek. When the stamp 50 is done i, the second part of the number field Book change +1 and becomes 2006/02
- When the year change not only the year change but the second part begins again with 01. Let us suppose that the last number in the year 2006 is 78 (2006/78) when the year change we become 2007/01

For the numbering field I tried with: (green is my translation in Englisch for specific words)

In my table called Numbers I have:

The field ZegelsInboek is a text field with notation: =”06-” & format(tussen,”0000”) & “/” & format(einde,”00”) and inputmask 00/0000/00
StampsInBook is .... ="06-" & format(between,"0000") & "/" & format(end,"00")
The field Boek is a text field with notation: =Laatsteboek and imputmask 0000/00
Book is .... =Lastbook

Dim rs as recordset

Dim tussen, laatste,einde (Dim between, last, end)

Set rs=dbengine(0)(0).openrecordset(“Select max(ZegelsInBoek) as grootste,max(boek) as grootsteboek from table”)

Set rs=dbegine(0)(0). openrecordset("Select max(StampsInBook) as greatest,max(Book) as greatestbook from table")

Laatste=rs!grootste

Last=rs!greatest

Laatsteboek=rs!grootsteboek

Lastbook=rs!greatestbook

If val(mid(laatste,9))=30 then

If val(mid(last,9))=50 then

Tussen=val(mid(laatste,6,4))+1

Between=val(mid(last,6,4))+1

Einde=1

End=1

Laatsteboek=left(Laatsteboek,7) & format(val(mid(laatsteboek,8)+1))

Lastbook=left(Lastbook,7) & format(val(mid(lastbook,8)+1))

Else

Tussen=val(mid(laatste,6,4))

Between=val(mid(last,6,4))

Einde= val(mid(laatste,9))+1

End=val(mid(last,9))+1

Endif


I placed this code "after input" of a stamp ad I hoped that the numberfields will start automaticly but it was a dream.

With kind regards


Wilfried De Reu

 

[TheAceMan1]

How are ya posthelp . . .

Your talking [blue]Totals[/blue] Fields which represent single values!

Why do you think a single total needs to be represented is each record? . . . [purple]as access goes this is simply a calculated field . . .[/purple]

Be sure to grab your favorite beverage and have a look at the following . . . it should prove to be eye opening:

Fundamentals of Relational Database Design

Normalizing Tables

Table Relationships

See Ya! . . . . . .