find max +1 and populate table field

[deb]

I am entering a new record via a form in Access 2003
When clicking the add New Record button I want to automatically calculate
the RiskNo field with largest number in the MainData table, RiskNo field and
add 1.

example
MainData table has ...
RiskNo
4
1
3

When the add new record button is clicked, want it to find the largest
number 4 and then add 1 to it. The RiskNo field for the new record will now
be 5.

Does anyone have the code to make this happen??

Thanks in advance

 

[Jeff Boyce]

Deb

Check mvps.org/access. Check "custom autonumber".

One approach is to use the DMax() function. Or you can create a query that
returns the TOP value of that field.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[SteveM]

In the OnDirty event of your form you could put:

If Me.NewRecord Then
Me.RiskNo = DMax("[RiskNo]","MainData") + 1
End If

This way, as soon as you enter data in any field the RiskNo field will be
populated.

Using domain aggregate functions such as DMax() can be a litttle slow when
you have a large number of records in your table and will get progressively
slower if a lot of records are added frequently. To help with this problem,
you may want to use one of the replacement functions found on the MVP sites
or at mdbmakers.com

Steve

 

[Christina]

I have a question:

This may be a dumb question but...

What is the "Me" portion of your code below? I keep getting a message asking
for what macro this refers to and not sure what to do here. Do I need to
create a separate macro for "me" before creating this code in the OnDirty box
for my form when it's opened?


--
Thank you!
Christina

In the OnDirty event of your form you could put:

If Me.NewRecord Then
Me.RiskNo = DMax("[RiskNo]","MainData") + 1
End If

This way, as soon as you enter data in any field the RiskNo field will be
populated.

Using domain aggregate functions such as DMax() can be a litttle slow when
you have a large number of records in your table and will get progressively
slower if a lot of records are added frequently. To help with this problem,
you may want to use one of the replacement functions found on the MVP sites
or at mdbmakers.com

Steve

I am entering a new record via a form in Access 2003
When clicking the add New Record button I want to automatically calculate
the RiskNo field with largest number in the MainData table, RiskNo field and
add 1.

example
MainData table has ...
RiskNo
4
1
3

When the add new record button is clicked, want it to find the largest
number 4 and then add 1 to it. The RiskNo field for the new record will now
be 5.

Does anyone have the code to make this happen??

Thanks in advance

 

[BruceM]

The "Me" refers to the current database object. If you are working with a
form's code, Me means that what follows is a property of the form, including
fields in the underlying record source (the table or query to which the form
is bound). Me.CurrentRecord means the current record in the recordset (the
one the form is "looking at"); Me.RiskNo is the RiskNo field in the
recordset, or it could be the text box bound to the RiskNo field. I can't
tell from the example.

The suggestion is to use VBA (Visual Basic for Applications) code, which is
not quite the same as a macro. Click iinto an Event on the form's Property
Sheet (I would use the Current event in a single-user database, and the
Before Update event in a multi-user application, in general, but I think the
Dirty event will work). Click the three dots, then click Code Builder, OK.
The VBA editor will open, with the cursor between Private Sub and End Sub.
That's where the code would go.

Actually, I would use the DefaultValue property of the control (text box)
bound to the RiskNo field, which I have called txtRiskNo:

Me.txtRiskNo.DefaultValue = DMax("[RiskNo]","MainData") + 1

DefaultValue applies only to new records, so there is no need for the
NewRecord test. However, using the code as suggested is OK, too.

For another possibility, see:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395

I have a question:

This may be a dumb question but...

What is the "Me" portion of your code below? I keep getting a message
asking
for what macro this refers to and not sure what to do here. Do I need to
create a separate macro for "me" before creating this code in the OnDirty
box
for my form when it's opened?


--
Thank you!
Christina

In the OnDirty event of your form you could put:

If Me.NewRecord Then
Me.RiskNo = DMax("[RiskNo]","MainData") + 1
End If

This way, as soon as you enter data in any field the RiskNo field will be
populated.

Using domain aggregate functions such as DMax() can be a litttle slow
when
you have a large number of records in your table and will get
progressively
slower if a lot of records are added frequently. To help with this
problem,
you may want to use one of the replacement functions found on the MVP
sites
or at mdbmakers.com

Steve

I am entering a new record via a form in Access 2003
When clicking the add New Record button I want to automatically
calculate
the RiskNo field with largest number in the MainData table, RiskNo
field and
add 1.

example
MainData table has ...
RiskNo
4
1
3

When the add new record button is clicked, want it to find the largest
number 4 and then add 1 to it. The RiskNo field for the new record
will now
be 5.

Does anyone have the code to make this happen??

Thanks in advance

 

[Christina]

Bruce,

Thank you for the great explanation for my simple mind here....ok so this is
what I have for my own database and am not sure why I am still getting the
"Name?" error in the [Cust ID as Number] field when I add a new record...

Database Objects referenced:
- Audiovisual Reference Form - form used for data entry
- [Cust ID as Number] - field I would like to increment in value by 1 for
each new record
- New Audiovisual Reference Table - table where the [Cust ID as Number
field] is stored

I set this formula in the Default Value of the [Cust ID as Number]
properties box on the form:

[Audiovisual Reference Form].[CustID as Number]=DMax("[CustID as
Number]","New Audiovisual Reference Table")+1

When I add a new record, I get the "Name?" error in the [CustID as Number]
field....there is something small I am missing I'm sure..

Thank you again,
Christina

The "Me" refers to the current database object. If you are working with a
form's code, Me means that what follows is a property of the form, including
fields in the underlying record source (the table or query to which the form
is bound). Me.CurrentRecord means the current record in the recordset (the
one the form is "looking at"); Me.RiskNo is the RiskNo field in the
recordset, or it could be the text box bound to the RiskNo field. I can't
tell from the example.

The suggestion is to use VBA (Visual Basic for Applications) code, which is
not quite the same as a macro. Click iinto an Event on the form's Property
Sheet (I would use the Current event in a single-user database, and the
Before Update event in a multi-user application, in general, but I think the
Dirty event will work). Click the three dots, then click Code Builder, OK.
The VBA editor will open, with the cursor between Private Sub and End Sub.
That's where the code would go.

Actually, I would use the DefaultValue property of the control (text box)
bound to the RiskNo field, which I have called txtRiskNo:

Me.txtRiskNo.DefaultValue = DMax("[RiskNo]","MainData") + 1

DefaultValue applies only to new records, so there is no need for the
NewRecord test. However, using the code as suggested is OK, too.

For another possibility, see:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395

I have a question:

This may be a dumb question but...

What is the "Me" portion of your code below? I keep getting a message
asking
for what macro this refers to and not sure what to do here. Do I need to
create a separate macro for "me" before creating this code in the OnDirty
box
for my form when it's opened?


--
Thank you!
Christina

In the OnDirty event of your form you could put:

If Me.NewRecord Then
Me.RiskNo = DMax("[RiskNo]","MainData") + 1
End If

This way, as soon as you enter data in any field the RiskNo field will be
populated.

Using domain aggregate functions such as DMax() can be a litttle slow
when
you have a large number of records in your table and will get
progressively
slower if a lot of records are added frequently. To help with this
problem,
you may want to use one of the replacement functions found on the MVP
sites
or at mdbmakers.com

Steve

:

I am entering a new record via a form in Access 2003
When clicking the add New Record button I want to automatically
calculate
the RiskNo field with largest number in the MainData table, RiskNo
field and
add 1.

example
MainData table has ...
RiskNo
4
1
3

When the add new record button is clicked, want it to find the largest
number 4 and then add 1 to it. The RiskNo field for the new record
will now
be 5.

Does anyone have the code to make this happen??

Thanks in advance

 

[BruceM]

I've been away until today or I would have replied sooner.

Make sure the text box has a different name from the field (e.g. call it
txtCustID), and use brackets around the table name since it contains spaces.

In general, it is best to use only alphnumeric characters and underscores in
names. For your own benefit the use of short table, control, and field
names will make things easier for you, expecially when you need to type the
names in full. For instance, tblAV_Ref may be all you need for the table.

Bruce,

Thank you for the great explanation for my simple mind here....ok so this
is
what I have for my own database and am not sure why I am still getting the
"Name?" error in the [Cust ID as Number] field when I add a new record...

Database Objects referenced:
- Audiovisual Reference Form - form used for data entry
- [Cust ID as Number] - field I would like to increment in value by 1 for
each new record
- New Audiovisual Reference Table - table where the [Cust ID as Number
field] is stored

I set this formula in the Default Value of the [Cust ID as Number]
properties box on the form:

[Audiovisual Reference Form].[CustID as Number]=DMax("[CustID as
Number]","New Audiovisual Reference Table")+1

When I add a new record, I get the "Name?" error in the [CustID as Number]
field....there is something small I am missing I'm sure..

Thank you again,
Christina

The "Me" refers to the current database object. If you are working with
a
form's code, Me means that what follows is a property of the form,
including
fields in the underlying record source (the table or query to which the
form
is bound). Me.CurrentRecord means the current record in the recordset
(the
one the form is "looking at"); Me.RiskNo is the RiskNo field in the
recordset, or it could be the text box bound to the RiskNo field. I
can't
tell from the example.

The suggestion is to use VBA (Visual Basic for Applications) code, which
is
not quite the same as a macro. Click iinto an Event on the form's
Property
Sheet (I would use the Current event in a single-user database, and the
Before Update event in a multi-user application, in general, but I think
the
Dirty event will work). Click the three dots, then click Code Builder,
OK.
The VBA editor will open, with the cursor between Private Sub and End
Sub.
That's where the code would go.

Actually, I would use the DefaultValue property of the control (text box)
bound to the RiskNo field, which I have called txtRiskNo:

Me.txtRiskNo.DefaultValue = DMax("[RiskNo]","MainData") + 1

DefaultValue applies only to new records, so there is no need for the
NewRecord test. However, using the code as suggested is OK, too.

For another possibility, see:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395

I have a question:

This may be a dumb question but...

What is the "Me" portion of your code below? I keep getting a message
asking
for what macro this refers to and not sure what to do here. Do I need
to
create a separate macro for "me" before creating this code in the
OnDirty
box
for my form when it's opened?


--
Thank you!
Christina


:

In the OnDirty event of your form you could put:

If Me.NewRecord Then
Me.RiskNo = DMax("[RiskNo]","MainData") + 1
End If

This way, as soon as you enter data in any field the RiskNo field will
be
populated.

Using domain aggregate functions such as DMax() can be a litttle slow
when
you have a large number of records in your table and will get
progressively
slower if a lot of records are added frequently. To help with this
problem,
you may want to use one of the replacement functions found on the MVP
sites
or at mdbmakers.com

Steve

:

I am entering a new record via a form in Access 2003
When clicking the add New Record button I want to automatically
calculate
the RiskNo field with largest number in the MainData table, RiskNo
field and
add 1.

example
MainData table has ...
RiskNo
4
1
3

When the add new record button is clicked, want it to find the
largest
number 4 and then add 1 to it. The RiskNo field for the new record
will now
be 5.

Does anyone have the code to make this happen??

Thanks in advance