Automatic field incrementing

[SeeAll]

Hi,
On a data entry record I have two numeric fields, RegPage and RegNo. When I
move to the next record I would like the RegNo to carry forward and
increment by 1, i.e., 100 to 101, and so on. Also RegPage would carry
forward but without incrementing. However, after eight records are entered
I would like RegPage to increment by 1 and so on every eight records. RegNo
would still carry on incrementing by 1.

Any suggestions, coding or websites on how to achieve this would be much
appreciated.

Regards

 

[scubadiver]

I think your form should be based on a select query. The reason for this is
that you can use "RegNo" as the source for an expression. Divide "RegNo" by 8
and you would get the decimal in the second column. In the third column you
can round up to the nearest integer (you may be able to combine the
expressions for column's 2 and 3)

1 0.125 1
2 0.25 1
3 0.375 1
4 0.5 1
5 0.625 1
6 0.75 1
7 0.875 1
8 1 1
9 1.125 2
10 1.25 2
11 1.375 2
12 1.5 2
13 1.625 2
14 1.75 2
15 1.875 2
16 2 2


For "RegNo" put the following in the form's "before insert" event.

Me!RegNo = DMax("[RegNo]", "[TABLENAME]") + 1

 

[John Spencer]

If you are doing this on an entry form you could use two formulas in the
Before Insert event of the form.

Me.txtRegNo = DMax("RegNo","YourTable") +1
Me.txtRegPage = (Me.txtRegNo -1)\8 +1

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[SeeAll]

Hi,

Thank you scubadiver and John for responding. I now have the RegNo
incrementing, however, I cannot get RegPage to work. I think this might be
due to me not explaining exactly what I am inputting. The data to be
entered into RegNo and RegPage as page numbers and entry numbers which are
already known:

RegPage 173
RegNo 1377 (followed by rest of data to be entered to this RegNo)
RegNo 1378 (followed by rest of data to be entered to this RegNo)
RegNo 1379
RegNo 1380
RegNo 1381
RegNo 1382
RegNo 1383
RegNo 1384

RegPage 174
RegNo 1385
RegNo 1386
And so on.


RegNo incrementing using DMax works fine if I keep adding data sequentially,
but, how would I enter data, lets say, RegPage 172 the RegNo would be one
more than the last entry made which would be incorrect it should be 1369 -
1376?

Again, any thoughts, code etc. would be appreciated.

Regards

 

[John Spencer]

If you have Reg No working then you should be able to calculate regpage
using

Me.txtRegPage = ((DMax("RegNo","YourTable") +1)-1)\8 +1

And in theory, you should not even need to store reg page since it is
dependent on reg no.

1377 -1 = 1376
1376\8 = 172
172 + 1 = 173

1384 -1 = 1383
1383 \ 8 = 172
172 +1 = 173

1385-1 = 1384
1384 \ 8 = 173
173 +1 = 174

Are you using the \ operator (which is integer division) and not / operator
which is division and will not necessarily return whole numbers?

If you are still having problems can you post what you have that is working.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[SeeAll]

Hi John,

Thanks I now have both fields being updated as they should. The reason why
it wasn't working before, I had RegPage as the first field to be entered
followed by RegNo, when I entered 1 in RegPage, RegNo was filled with the
correct number. So I've disabled RegPage and RegNo and I start entering
data in Forename both RegPage and RegNo are now correctly filled.

Any thoughts on RegNo incrementing using DMax works fine if I keep adding
data sequentially, but, how would I enter data, lets say, RegPage 172 the
RegNo would be one more than the last entry made which would be incorrect it
should be 1369 - 1376?

Regards