Increment a field by one (1) when adding a record

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

I have a table which contains a field which keeps track of number of items
for that particular group of sub items. It is not a serial field, because
it needs to always start at 1 and increment to n for each master detail
relationship. How would I look at the previous record number and then
increment it by 1. The number should first start a 1



Thanks in advance.
 
I have a table which contains a field which keeps track of number of items
for that particular group of sub items. It is not a serial field, because
it needs to always start at 1 and increment to n for each master detail
relationship. How would I look at the previous record number and then
increment it by 1. The number should first start a 1
Click to expand...

Here's some code I use on my Invoice form to sequentially number
lineitems. In the Form's (subform's) BeforeInsert event put code like

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!Line = Nz(DMax("Line", "HoursLine", "InvoiceID=" & _
Forms!Invoice!InvoiceID)) + 1
End Sub

using, of course, your own table and fieldnames.
 
Bruce said:
I have a table which contains a field which keeps track of number of
items for that particular group of sub items. It is not a serial
field, because it needs to always start at 1 and increment to n for
each master detail relationship. How would I look at the previous
record number and then increment it by 1. The number should first
start a 1
Click to expand...

While John Vinson has given you an example of how to do this, I can't
help wondering if you need to have this "item count" table at all. It
sounds like the sort of data that can be extracted by a totals query at
any time, and if it's done that way it's always guaranteed to be
accurate. Of course, I don't know your application or what functional
need is behind your question, so I'm just throwing this comment out for
consideration.
 
Back
Top