Autonumber with a datasheet view

[Guest]

I have a main form (enter_RMA) with a subform (Ord_Line) linked by the
RMA_ID. The main form has customer info, order date, ship date, order
status, etc. The subform has specific order data. For example on the
subform - the user chooses a part_id, the qty (Quantity), and some other data
is automatically populated based on the part_id. If the order contains more
than 1 part the user goes to the next line and repeats the process till all
the parts are ordered. There is a field called Line_No which has to be
entered. Right now the user has to manually enter a new line number. Is
there a way to automatically have the Line_No increment by 1 each time a new
part is added to the order?

 

[Dave Emmert]

You could create a field (as a number) to for this number. For the below
code, I choose Line_Num. Then create a function to set this number:

Public Function OneUp(OrderNum as Long) As Long
dim db as database, rs as dao.recordset
set db = currentdb
set rs = db.openrecordset(Select * From Ord_Line Where RMA_ID = " &
OrderNum & ";")
if rs.recordcount > 0 then
rs.movelast
OneUp = rs!Line_Num + 1
Else
OneUp = 1
end if
rs.close
db.close
End Function

I would then set the default value to that field as OneUp([RMA_ID])

The problem is that if the user deletes a record from the table, this number
will remain the same and not adjust to the new order. To fix this issue,
you would have to write another subroutine to reorder the numbers.

Dave Emmert