Auto-Increment

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hi -

I think this is a simple question but I am stumped. I
have a text field (Job#). I would like that field to
begin with the number 500 and when a new record is
entered I would like that number to be there
automatically and increase by 1 for each new record that
is added. I am a newbie and have tried different things
but I am not sure what expression to use or even where to
place that expression within the properties.

Any help would be great.
Thanks to all.
 
Well, you DON'T want to use the autonumber data type. That's the first
mistake that a newbie makes, so don't. You can use the DMax() function to
find the current maximum value of a field and increment it.

On my website (see sig below) is a small sample database called
"AutonumberProblem.mdb" which illustrates this solution.
 
Hi -

I think this is a simple question but I am stumped. I
have a text field (Job#). I would like that field to
begin with the number 500 and when a new record is
entered I would like that number to be there
automatically and increase by 1 for each new record that
is added. I am a newbie and have tried different things
but I am not sure what expression to use or even where to
place that expression within the properties.
Click to expand...

You'll need to do the data entry using a Form - table datasheets don't
have usable events. I'd make JobNo (don't use # in fieldnames, it's a
date delimiter) a Number... Long Integer field.

Manually enter the first record with 500 in it; create a Form with a
textbox named txtJobNo bound to this field. Open the Form in design
view, view its properties, and find the Before Insert event on the
Events tab. Click the ... icon by this event, invoke the Code Builder,
and edit the two lines of code that get given you automatically to:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtJobNo = DMax("[JobNo]", "[your-table-name]") + 1
End Sub

When you insert a new record using the Form, this expression will find
the largest existing job number, add one, and store that value in the
form control and thence into the table.
 
Back
Top