Function to count records

[yaronh]

Anyone who can help,

I would like to have a button which by enter it it will create a new record and plugin the number of the record in the ID field. I was trying to create a function which in VBA which will count current number of records and will set the value of the ID filed to counter+1.
I can not get it to work - please help::

 

[ColinEssex]

If you use an autoNumber as the ID field in the table, it will automatically put the next number in the ID field on a form when you start entering the new data.

Col

 

[yaronh]

This I know. But becasue my table is contacted with relationships to another one I cannot assign this property to the field.

 

[Fizzio]

Col is right, Use an AutoNumber field (if it is specifically for an ID only to indicate Unique fields). You have obviously linked 2 tables via a relationship. What you need to look at is

a) why are they joined and does it need to be 1-1, 1-many or many-many
b) how are they joined, by the same primary key, via a foreign key ?

Use the search facility here to look for relationships or search by user Pat Hartman (or buy her book when it comes out!)

 

[The_Doc_Man]

The other possibility is something like this


nextnumber = 1 + Dcount( "[ID]","mytable","put your own selection criteria here, possibly including nothing.)

Or 1 + DMax( "[ID]" etc. etc. etc.)

 

[Hunter666]

The problem you will have with autonumbers is when you create a replica of your database. When you do this the autonumbers no longer run in order.