creating next number on linked data

[Daniel M]

I have a linked excel file that lists data with one column being an
incremental number. I have a form that inserts new data into the file and i
need to increment this number and have it prefilled in in the form but only
writes it when i update the form. Any ideas?

Thanks.

 

[Arvin Meyer [MVP]]

The only way to do that is to unbind the form from the table (Excel file in
this case). First make a copy to be sure this is what you want.

In the property sheet for each control on the form, delete the control
source. Also delete the record source of the form itself. After filling out
the form, use an Append Query to move the data from the form to the table.

The above is a few hours work and should be unnecessary in a relational
database. Incrementing numbers never need to be sequential. A spoiled record
is no more of a catastrophe than a damaged paper order.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Daniel Doyle]

You could set the default for the control on your form to the name max of
the current number plus one.
E.g. If your linked spreadsheet is called excelsheet and the field you want
to increment is called idfield, the default for the control on your form
that is bound to num should be:

=DMax("idfield","excelsheet ")+1

Hope this helps.

Dan.

 

[Daniel M]

Dan, ah! this is exactly what i was looking for. Thanks!