3. On your Orders form (I hope you are using a form to enter and edit your
data),
make the data source for InvoiceNo textbox
=nz(dmax("InvoiceNo","tblOrders"))+1
if UpRider means the ControlSource property of the textbox control, when he
refers to the "data source", i'm afraid that won't work. a calculated
control (which is a control with the ControlSource property set to an
expression) is, by definition, unbound. whatever value is displayed in it
will *not* save to a field in the table; you would have to write the value
to the table programmatically.
suggest you skip steps 2, 3, and 4 from the previous post. instead, add the
InvoiceNo field to your Orders table, as previously suggested. if you
haven't already done so, create a form bound to the table; you'll do the
programming and the data entry in the form - not directly in the table.
next, add the following code to the form's BeforeUpdate event procedure, as
If Me.NewRecord Then
Me!InvoiceNo = Nz(DMax("InvoiceNo", _
"tblOrders"), 1999)+1
End If
if you don't know how to create a VBA procedure in a form, see
http://home.att.net/~california.db/instructions.html, and click on the
"Create a VBA event procedure" link, for illustrated step-by-step
instructions. the code above assumes that the name of the table is
tblOrders, and the name of the field is InvoiceNo. if those values are
different in your database, you must adjust the code accordingly.
when the user adds or edits a record, the code fires; it checks to see if
it's a new record, and if so, then a new invoice number is generated, just
before the record is saved to the table. note that, generally speaking, in a
multi-user (or potential multi-user) environment, i set incrementing code to
the BeforeUpdate event to lessen (while not eliminating) the possibility of
duplicate numbers being generated.
hth
