automatically adding 1 to a recordset

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

Guest

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
 
Thanks Ken,

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

Thanks again.
 
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
 
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
 
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.aspx?scid=kb;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
 
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) said:
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.aspx?scid=kb;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 said:
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
Click to expand...
Click to expand...
 
Work is keeping me busy tonite... I will reply, but it will be tomorrow at
earliest ... sorry for delay.
 
The process you used for entering the code steps might mean that your form
does not know that you've established an event procedure for the form's
Current event, assuming that you entered the code in the form's module. It's
possible that you put the code in some other module, where it will be
ignored and not used.

Open the form in design view, open the Properties window, select the Event
tab, and click in the box next to On Current. Select "[Event Procedure]"
from the dropdown list. Then click on the three-dot button at far right.
This will open tthe VBE and, if the code you entered is still in the form's
module, you'll be taken right to it. Otherwise, now you can enter these
lines between the "Private Sub Form_Current()" and "End Sub" lines in the
VBE:

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

--

Ken Snell
<MS ACCESS MVP>


Scuda said:
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) said:
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.aspx?scid=kb;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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top