Hi all, I have searched and can't find anything exactly related t what I want
to do. I think it is fairly simple. I am pretty new to Access and the DB I
learned from had this, but I must have messed it up.

A concurrent number, starts beginning of year, ends end of year, I have a
Command button on my form for Adding a New record. When someone clicks that,
i would like to have the concurrent number automatically be updated, PLUS
one. Whats the best way to do this?

Thanks!

Most common way of doing this (can cause errors in multi-user environments,
depending upon how you implement the database and when you save the data,
etc.) is to use the DMax function in code:

NextNumber = Nz(DMax("ConcurrentNumberField", "TableName"), 0) + 1

--

Ken Snell
<MS ACCESS MVP>

"Scuda" <(E-Mail Removed)> wrote in message
news:8330C6D6-A883-4F46-B710-(E-Mail Removed)...
> Hi all, I have searched and can't find anything exactly related t what I
> want
> to do. I think it is fairly simple. I am pretty new to Access and the DB I
> learned from had this, but I must have messed it up.
>
> A concurrent number, starts beginning of year, ends end of year, I have a
> Command button on my form for Adding a New record. When someone clicks
> that,
> i would like to have the concurrent number automatically be updated, PLUS
> one. Whats the best way to do this?
>
> Thanks!

Thanks Ken,

And I'm sorry for the ignorance, but where do I enter that code, in a Query?

Thanks again.

"Ken Snell (MVP)" wrote:

> Most common way of doing this (can cause errors in multi-user environments,
> depending upon how you implement the database and when you save the data,
> etc.) is to use the DMax function in code:
>
> NextNumber = Nz(DMax("ConcurrentNumberField", "TableName"), 0) + 1
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
> "Scuda" <(E-Mail Removed)> wrote in message
> news:8330C6D6-A883-4F46-B710-(E-Mail Removed)...
> > Hi all, I have searched and can't find anything exactly related t what I
> > want
> > to do. I think it is fairly simple. I am pretty new to Access and the DB I
> > learned from had this, but I must have messed it up.
> >
> > A concurrent number, starts beginning of year, ends end of year, I have a
> > Command button on my form for Adding a New record. When someone clicks
> > that,
> > i would like to have the concurrent number automatically be updated, PLUS
> > one. Whats the best way to do this?
> >
> > Thanks!
>
>
>

No, not in the query. Usually, you would run this code in the form itself --
usually in the form's Current event after testing if the record is a new
record:

Private Sub Form_Current()
If Me.NewRecord = True Then Me.NameOfControl.Value = _
Nz(DMax("ConcurrentNumberField", "TableName"), 0) + 1
End Sub

--

Ken Snell
<MS ACCESS MVP>



"Scuda" <(E-Mail Removed)> wrote in message
news:F6895A20-82F7-4733-8B87-(E-Mail Removed)...
> Thanks Ken,
>
> And I'm sorry for the ignorance, but where do I enter that code, in a
> Query?
>
> Thanks again.
>
> "Ken Snell (MVP)" wrote:
>
>> Most common way of doing this (can cause errors in multi-user
>> environments,
>> depending upon how you implement the database and when you save the data,
>> etc.) is to use the DMax function in code:
>>
>> NextNumber = Nz(DMax("ConcurrentNumberField", "TableName"), 0) + 1
>>
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>>
>> "Scuda" <(E-Mail Removed)> wrote in message
>> news:8330C6D6-A883-4F46-B710-(E-Mail Removed)...
>> > Hi all, I have searched and can't find anything exactly related t what
>> > I
>> > want
>> > to do. I think it is fairly simple. I am pretty new to Access and the
>> > DB I
>> > learned from had this, but I must have messed it up.
>> >
>> > A concurrent number, starts beginning of year, ends end of year, I have
>> > a
>> > Command button on my form for Adding a New record. When someone clicks
>> > that,
>> > i would like to have the concurrent number automatically be updated,
>> > PLUS
>> > one. Whats the best way to do this?
>> >
>> > Thanks!
>>
>>
>>

Thanks again, and thanks for your patience. I had a little trouble with it,
prob due to my admitted lack of coding knowledge. The name of the field to be
changed is "SENE Case #" and the table name is "SENE Sar Log" Could you
"idiot-proof the below code for me?

Thanks a million,
Scott


"Ken Snell (MVP)" wrote:

> No, not in the query. Usually, you would run this code in the form itself --
> usually in the form's Current event after testing if the record is a new
> record:
>
> Private Sub Form_Current()
> If Me.NewRecord = True Then Me.NameOfControl.Value = _
> Nz(DMax("ConcurrentNumberField", "TableName"), 0) + 1
> End Sub
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
>
>
> "Scuda" <(E-Mail Removed)> wrote in message
> news:F6895A20-82F7-4733-8B87-(E-Mail Removed)...
> > Thanks Ken,
> >
> > And I'm sorry for the ignorance, but where do I enter that code, in a
> > Query?
> >
> > Thanks again.
> >
> > "Ken Snell (MVP)" wrote:
> >
> >> Most common way of doing this (can cause errors in multi-user
> >> environments,
> >> depending upon how you implement the database and when you save the data,
> >> etc.) is to use the DMax function in code:
> >>
> >> NextNumber = Nz(DMax("ConcurrentNumberField", "TableName"), 0) + 1
> >>
> >> --
> >>
> >> Ken Snell
> >> <MS ACCESS MVP>
> >>
> >> "Scuda" <(E-Mail Removed)> wrote in message
> >> news:8330C6D6-A883-4F46-B710-(E-Mail Removed)...
> >> > Hi all, I have searched and can't find anything exactly related t what
> >> > I
> >> > want
> >> > to do. I think it is fairly simple. I am pretty new to Access and the
> >> > DB I
> >> > learned from had this, but I must have messed it up.
> >> >
> >> > A concurrent number, starts beginning of year, ends end of year, I have
> >> > a
> >> > Command button on my form for Adding a New record. When someone clicks
> >> > that,
> >> > i would like to have the concurrent number automatically be updated,
> >> > PLUS
> >> > one. Whats the best way to do this?
> >> >
> >> > Thanks!
> >>
> >>
> >>
>
>
>

Post the code that you have tried to use.

--

Ken Snell
<MS ACCESS MVP>

"Scuda" <(E-Mail Removed)> wrote in message
news6DCD988-E03E-49D1-8C4E-(E-Mail Removed)...
> Thanks again, and thanks for your patience. I had a little trouble with
> it,
> prob due to my admitted lack of coding knowledge. The name of the field to
> be
> changed is "SENE Case #" and the table name is "SENE Sar Log" Could you
> "idiot-proof the below code for me?
>
> Thanks a million,
> Scott
>
>
> "Ken Snell (MVP)" wrote:
>
>> No, not in the query. Usually, you would run this code in the form
>> itself --
>> usually in the form's Current event after testing if the record is a new
>> record:
>>
>> Private Sub Form_Current()
>> If Me.NewRecord = True Then Me.NameOfControl.Value = _
>> Nz(DMax("ConcurrentNumberField", "TableName"), 0) + 1
>> End Sub
>>
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>>
>>
>>
>> "Scuda" <(E-Mail Removed)> wrote in message
>> news:F6895A20-82F7-4733-8B87-(E-Mail Removed)...
>> > Thanks Ken,
>> >
>> > And I'm sorry for the ignorance, but where do I enter that code, in a
>> > Query?
>> >
>> > Thanks again.
>> >
>> > "Ken Snell (MVP)" wrote:
>> >
>> >> Most common way of doing this (can cause errors in multi-user
>> >> environments,
>> >> depending upon how you implement the database and when you save the
>> >> data,
>> >> etc.) is to use the DMax function in code:
>> >>
>> >> NextNumber = Nz(DMax("ConcurrentNumberField", "TableName"), 0) + 1
>> >>
>> >> --
>> >>
>> >> Ken Snell
>> >> <MS ACCESS MVP>
>> >>
>> >> "Scuda" <(E-Mail Removed)> wrote in message
>> >> news:8330C6D6-A883-4F46-B710-(E-Mail Removed)...
>> >> > Hi all, I have searched and can't find anything exactly related t
>> >> > what
>> >> > I
>> >> > want
>> >> > to do. I think it is fairly simple. I am pretty new to Access and
>> >> > the
>> >> > DB I
>> >> > learned from had this, but I must have messed it up.
>> >> >
>> >> > A concurrent number, starts beginning of year, ends end of year, I
>> >> > have
>> >> > a
>> >> > Command button on my form for Adding a New record. When someone
>> >> > clicks
>> >> > that,
>> >> > i would like to have the concurrent number automatically be updated,
>> >> > PLUS
>> >> > one. Whats the best way to do this?
>> >> >
>> >> > Thanks!
>> >>
>> >>
>> >>
>>
>>
>>

Thanks Ken, I am using:


Private Sub Form_Current()
If Me.NewRecord = True Then Me.SENE_CASE__.Value = _
Nz(DMax("SENE_CASE__", "SENE Sar Log"), 0) + 1
End Sub

Where my field I want to add a number to is: "SENE CASE #" ( text box) and
the table name is SENE Sar Log.

Thanks again

Do you have a control named "SENE CASE #" on your form? or is that field in
the form's RecordSource query? Assuming that it is a control, and that that
is why you're using SENE_CASE__ as it's "equivalent" in the code, let's try
this:

Private Sub Form_Current()
If Me.NewRecord = True And _
Len(Me.[SENE CASE #].Value & "") = 0 Then _
Me.[SENE CASE #].Value = _
Nz(DMax("SENE CASE #", "SENE Sar Log"), 0) + 1
End Sub

I have slightly changed the code to test if the "SENE CASE #" control
already has a value (using the Len function), and to not change its value if
it already has one.

The actual field name is SENE CASE #, so that is the name you must use in
the DMax function. The SENE_CASE__ is VBA's "equivalent" for the field name
because you cannot have a field or object or control name in VBA with spaces
or with # characters unless you enclose the name in [ ] characters. The
"alternative" code example that uses this VBA "equivalent" would be this:

Private Sub Form_Current()
If Me.NewRecord = True And _
Len(Me.SENE_CASE__.Value & "") = 0 Then _
Me.SENE_CASE__.Value = _
Nz(DMax("SENE CASE #", "SENE Sar Log"), 0) + 1
End Sub

In fact, may I recommend that you not use spaces or # character (or any of a
large number of special characters) in field or control or table names? See
these articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default...b;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

--

Ken Snell
<MS ACCESS MVP>


"Scuda" <(E-Mail Removed)> wrote in message
news:B0BFA20A-E4C1-4D5E-A28D-(E-Mail Removed)...
> Thanks Ken, I am using:
>
>
> Private Sub Form_Current()
> If Me.NewRecord = True Then Me.SENE_CASE__.Value = _
> Nz(DMax("SENE_CASE__", "SENE Sar Log"), 0) + 1
> End Sub
>
> Where my field I want to add a number to is: "SENE CASE #" ( text box) and
> the table name is SENE Sar Log.
>
> Thanks again

Hi Ken, yes it is a control source, in my text box.

I tried to enter both code snippets you had kindly given me and I am having
no sucess. I entered them by going to Tools, Macro, VBE. Hope that is
correct procedure.

One of the errors (highlighted in yellow) was actually the "Private Sub
Form_Current()"

I was looking through an old DB here that had that function working at one
time (not now unfortuantely) and the box was an Unbound box, does that help
at all?

Thanks again, I appreciate your patience with me.


Scott


"Ken Snell (MVP)" wrote:

> Do you have a control named "SENE CASE #" on your form? or is that field in
> the form's RecordSource query? Assuming that it is a control, and that that
> is why you're using SENE_CASE__ as it's "equivalent" in the code, let's try
> this:
>
> Private Sub Form_Current()
> If Me.NewRecord = True And _
> Len(Me.[SENE CASE #].Value & "") = 0 Then _
> Me.[SENE CASE #].Value = _
> Nz(DMax("SENE CASE #", "SENE Sar Log"), 0) + 1
> End Sub
>
> I have slightly changed the code to test if the "SENE CASE #" control
> already has a value (using the Len function), and to not change its value if
> it already has one.
>
> The actual field name is SENE CASE #, so that is the name you must use in
> the DMax function. The SENE_CASE__ is VBA's "equivalent" for the field name
> because you cannot have a field or object or control name in VBA with spaces
> or with # characters unless you enclose the name in [ ] characters. The
> "alternative" code example that uses this VBA "equivalent" would be this:
>
> Private Sub Form_Current()
> If Me.NewRecord = True And _
> Len(Me.SENE_CASE__.Value & "") = 0 Then _
> Me.SENE_CASE__.Value = _
> Nz(DMax("SENE CASE #", "SENE Sar Log"), 0) + 1
> End Sub
>
> In fact, may I recommend that you not use spaces or # character (or any of a
> large number of special characters) in field or control or table names? See
> these articles for more information:
>
> List of reserved words in Access 2002 and Access 2003
> http://support.microsoft.com/default...b;en-us;286335
>
> List of Microsoft Jet 4.0 reserved words
> http://support.microsoft.com/?id=321266
>
> Special characters that you must avoid when you work with Access databases
> http://support.microsoft.com/?id=826763
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
>
> "Scuda" <(E-Mail Removed)> wrote in message
> news:B0BFA20A-E4C1-4D5E-A28D-(E-Mail Removed)...
> > Thanks Ken, I am using:
> >
> >
> > Private Sub Form_Current()
> > If Me.NewRecord = True Then Me.SENE_CASE__.Value = _
> > Nz(DMax("SENE_CASE__", "SENE Sar Log"), 0) + 1
> > End Sub
> >
> > Where my field I want to add a number to is: "SENE CASE #" ( text box) and
> > the table name is SENE Sar Log.
> >
> > Thanks again
>
>
>

Work is keeping me busy tonite... I will reply, but it will be tomorrow at
earliest ... sorry for delay.

--

Ken Snell
<MS ACCESS MVP>

"Scuda" <(E-Mail Removed)> wrote in message
news:2ABED199-B554-470E-AB1E-(E-Mail Removed)...
> Hi Ken, yes it is a control source, in my text box.
>