New record in a restricted form

[Guest]

Hi,
I have a table which contains a key, name and other info. I have a form
that shows only certain records from the table based on a restriction. I
want to add a new record from this restricted form, but when I enter data in
the new record the key value for the new record is not updated. It stays at
0. If I try to add 2 new records I get an error for duplicate key (because
they both have a 0 value) and get locked up.
Is there a way to get the key value to increment to the next value when in a
restricted form?
Thanks,

 

[John W. Vinson]

Hi,
I have a table which contains a key, name and other info. I have a form
that shows only certain records from the table based on a restriction. I
want to add a new record from this restricted form, but when I enter data in
the new record the key value for the new record is not updated. It stays at
0. If I try to add 2 new records I get an error for duplicate key (because
they both have a 0 value) and get locked up.
Is there a way to get the key value to increment to the next value when in a
restricted form?
Thanks,

What do you mean by "restricted"? Based on a Query? If so, what's the Query?

Except for Autonumber fields, Access won't (by itself) increment a field for
you. If it's a Number, you should certainly open the Table and remove Access'
dimwitted "default default" value of 0 from the field properties. You can put
VBA code in the form's BeforeInsert event to increment the ID:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "[tablename]")) + 1
End Sub

This will look up the maximum value of the field ID in the table "tablename";
NZ will return a zero if there are no records in the table; adding 1 will
increment it, and the result will be put into the textbox named txtID (which
should be bound to the ID field).

John W. Vinson [MVP]