Incrementing Numerical Field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,
 
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
 
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!


PC Datasheet said:
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
(e-mail address removed)
www.pcdatasheet.com



Rosemary said:
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,
Click to expand...
Click to expand...
 
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
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Rosemary said:
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!


PC Datasheet said:
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
(e-mail address removed)
www.pcdatasheet.com



Rosemary said:
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
Click to expand...
time
a
new record is created.

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
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
(e-mail address removed)
www.pcdatasheet.com


Rosemary said:
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!


PC Datasheet said:
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
(e-mail address removed)
www.pcdatasheet.com



Rosemary said:
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
Click to expand...
time
a
new record is created.

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
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?


PC Datasheet said:
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
(e-mail address removed)
www.pcdatasheet.com


Rosemary said:
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!


PC Datasheet said:
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
(e-mail address removed)
www.pcdatasheet.com



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,
Click to expand...
Click to expand...
Click to expand...
 
What does the "Me!" represent in the coding?

Quality Guy said:
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?


PC Datasheet said:
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
(e-mail address removed)
www.pcdatasheet.com


Rosemary said:
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:
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
(e-mail address removed)
www.pcdatasheet.com



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,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top