Setting default values in a form

  • Thread starter Thread starter Jeff Abell
  • Start date Start date
J

Jeff Abell

Hi,

Probably a simple question ...

In a table I have a particular field with datatype set
to 'number' (called "Item Number"). It is also the primary
key.

I also have a data entry form which allows new records to
be added to the table.

What I would like to do is make the text box control which
writes to the field "Item Number" automatically default to
the maximum value in the field + 1 i.e. increment to the
next number.

The table already has a lot of data, and the "Item Number"
field was only added recently.

If someone could help it would be greatly appreciated.

Thanks in advance,

Jeff Abell
 
Use an expression similar to this for the Default Value of that control:

=Nz(DMax("Item Number", "TableName"), 0) + 1
 
if you have multiple users, you might not want to set the value of the
primary key field immediately. if one user starts entering data in a new
record, and another user starts entering data in a new record before the
first user saves his record, you could end up with a duplicate default
primary key value. whoever saves their record first will not have a problem,
but the other person will probably get an error due to the duplicate primary
key value.

instead, you could try adding the following code to the form's BeforeUpdate
event, as

Private Sub Form_BeforeUpdate(Cancel As Integer)

With Me
If IsNull(.PrimaryKeyFieldName) Then
.PrimaryKeyFieldName = Nz(DMax("PrimaryKeyFieldName",
"TableName"), 0) + 1
End If
End With

End Sub

*watch for word-wrap on the code above.* you could still conceivably have
duplicate values generated on simultaneous saving, but hopefully it's less
likely.

hth
 
Back
Top