Incrementing Numerical Field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rosemary - 15 Jan 2005 02:05 GMT

Hi,

I have a field on my form that is called "Job Number". I would like it to
increment automatically each time a new record is created.

Ideally, I'd like it to start with the number 1 at the start of each new
day. But if that requires a lot of programming, then I would just be happy
with having the field increment automatically to the next number each time a
new record is created.

Thanks,

Reply to this Message

PC Datasheet - 15 Jan 2005 03:33 GMT

Your form needs to be designed so you enter the job date first. Then put the
following code in the AfterUpdate event of job date:
If DCount("[JobNumber]","JobTable","[JobDate] = #" & Me!JobDate & "#") = 0
Then
Me!JobNumber = 1
Else
Me!JobNumber = DMax("[JobNumber]","JobTable","[JobDate] = #" &
Me!JobDate & "#") + 1
End If

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdatasheet.com
www.pcdatasheet.com

> Hi,
>
[quoted text clipped - 7 lines]
>
> Thanks,

Reply to this Message

Rosemary - 15 Jan 2005 12:29 GMT

Hi,

Thanks - just one glitch:

In the JobDate (which I call ClockedIn) field, I have another event
procedure in the DblClick event that provides an automatic date -
Me!ClockedIn.Value = Now()

After I put your code into the AfterUpdate event procedure, I found that it
worked if I typed in the date manually. But if I double-click to get the
date automatically, then the AfterUpdate event didn't fire.

I tried moving the code from AfterUpdate to OnChange, but that didn't help.
Can I adjust the code so it will fire whether I input the date manually, or
use the double-click event procedure?

Thanks!

> Your form needs to be designed so you enter the job date first. Then put the
> following code in the AfterUpdate event of job date:
[quoted text clipped - 25 lines]
> >
> > Thanks,

Reply to this Message

Arvin Meyer - 15 Jan 2005 13:03 GMT

you can force an event to fire from another event, simply by naming it. add
its name right after your double-click code, but before any exit or error
handling code, like (aircode):

Private Sub Command20_DblClick(Cancel As Integer)
On Error GoTo Error_Handler

'...blah blah

txtWhatever_AfterUpdate

Exit_Here:
Exit Sub
Error_Handler:
MsgBox "blah blah"
End Sub

Signature

Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

> Hi,
>
[quoted text clipped - 43 lines]
> > >
> > > Thanks,

Reply to this Message

PC Datasheet - 15 Jan 2005 15:47 GMT

Leave my code in the AfterUpdate event and add my code after your line of
code in the Dbl Click event. BTW, if you only need the Date (and not Time)
in the ClockedIn Field, you can avoid potential problems by using Date()
rather than Now(). Date() just gives you the current date where Now() gives
you the current date and the current time.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdatasheet.com
www.pcdatasheet.com

> Hi,
>
[quoted text clipped - 43 lines]
> > >
> > > Thanks,

Reply to this Message

Quality Guy - 13 Jun 2007 18:57 GMT

I found this info through searching

I have a need to increment a field in a record every time a new record is
created.

I tried the code as listed below in my Access 2003 but it produces compile
errors when I enter it in the "Code" section for the form field "DataDate"
(After Update)

Table = "Data"
Fields = DataID (AutoNum), DataDate (Date), DataNumber (Number),
DataInput1 (Text), DataInput2 (Text)

Am I doing something wrong?

> Leave my code in the AfterUpdate event and add my code after your line of
> code in the Dbl Click event. BTW, if you only need the Date (and not Time)
[quoted text clipped - 63 lines]
> > > >
> > > > Thanks,

Reply to this Message

Quality Guy - 13 Jun 2007 19:29 GMT

What does the "Me!" represent in the coding?

> I found this info through searching
>
[quoted text clipped - 78 lines]
> > > > >
> > > > > Thanks,

Reply to this Message