Auto count items.

[ILIRI]

I am creating a Ms Access 2007 database where I will input data in the
future from a tabular form (form1) and print the report (report1). This form
calculates fright and other cost for the purchased material.
Currently I am struggling to insert a code to form1 (I presume same code
could be applied to report1 for the same purpose). this code would count
items, something similar to auto number function in tables, however Auto
number changes the order if an Item is deleted.

Item; Description; Purchase cost; Fright; Insurance; Total.
1 a1 3000 150 0
3150
2 a2 4000 200 0
4200
3 a3 2000 100 0
2100.



__________ Information from ESET NOD32 Antivirus, version of virus signature database 4783 (20100118) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[Arvin Meyer [MVP]]

Autonumbers do not change if an item is deleted. The would be useless as
indices and/or keys if they did.

That said, you will need to bind the item column to an order number in the
main form, and write some code in the subform like:

Private Sub Form_Current()
On Error Resume Next
Dim x As Integer

x = DMax("Item", "TableName", "ID =" & Forms!MainFormName!ID)

If IsNull(x) Then
Me!Item.DefaultValue = 1
Else
Me!Item.DefaultValue = x + 1
End If

End Sub