Incrementing numbers based on another control

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, first poster. My question is this:

I am working on a cemetery database, I have only one table and one form at
this point. On my form, I want to auto-increment the number in the Grave
field based on the number in the Row field.

For example, There are 50 graves in the first row, instead of having to
manually keep up with what grave I'm on I'd like the form to autoinsert the
next number as long as the row didn't change. When the row goes to 2, I'd
like the grave number to start over again at 1.
I am new to VBA, but used to working with Access. Any help or advice to
where I can find some learning material online for VBA would be a great help
also, as I would like to become proficient in the VBA language. Thanks!
 
Hi,


The technique would be to find the max row in the table, then, the max
Grave.

DMax("Row", "Graves")

return the maximum row value in use,


DMax("Grave", "Graves", "Row=" & DMax("Row", "Graves") )

return the max grave number already in use. Adding 1 will work most of the
time, except when it is already 50. The way you wish to handle this
situation is not clear. The "lazy" way would be to do nothing, letting you
change the 51 to 1, and also increasing the Row number, in such a case, but
you cannot write over a computed expression.


= 1+ DMax("Grave", "Graves", "Row=" & DMax("Row", "Graves") )


could not be write over, if you use it for source of a control. The
"easiest" way is to use VBA, but are you ready to use VBA?

If so, in the onCurrent event procedure handler, add some code like:


If Me.NewRecord then
' only for new record, do nothing for old records
Me.Row=DMax("row", "graves")
Me.Grave = 1+ DMax( "grave", "graves", "row=" & Me.Row)
If Me.Grave > 50 then
Debug.Assert Me.Grave=51 'what else can it logically be
' but since it is not enforced, "assert" it !
Me.Row=Me.Row+1
Me.Grave=1
End If ' Grave > 50
End if ' Me.NewRecord




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top