MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Assigning New ID# in Code

• Follow

```What code can I use to assign a number as an ID# to a new record?  I
basically would add 1 to the previous ID.
--
Gator
```
 0

```Why not just use an Autonumber field?

If you can't for some reason, you can get a new number using the DMax
function:

lngNewId = Nz(DMax("[ID]","TableName"),0) + 1
--
Dave Hargis, Microsoft Access MVP

"Gator" wrote:

> What code can I use to assign a number as an ID# to a new record?  I
> basically would add 1 to the previous ID.
> --
> Gator
```
 0

```I could probably use auto number...I just figured I would assign code, maybe
for sake of learning.  Also, what does "Nz" mean?
--
Gator

"Klatuu" wrote:

> Why not just use an Autonumber field?
>
> If you can't for some reason, you can get a new number using the DMax
> function:
>
>   lngNewId = Nz(DMax("[ID]","TableName"),0) + 1
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "Gator" wrote:
>
> > What code can I use to assign a number as an ID# to a new record?  I
> > basically would add 1 to the previous ID.
> > --
> > Gator
```
 0

```The Nz function is used to convert Null values to some other value.
It does not chage the value of non Null values.
The reason for putting it in this code is that when you create the first
record, the DMax would return Null, becase there are no records.  If you were
to add 1 to Null, you would get Null.  So to avoid that possibility, it
converts the Null to 0 and 0 + 1 = 1.  That would be the first record.
--
Dave Hargis, Microsoft Access MVP

"Gator" wrote:

> I could probably use auto number...I just figured I would assign code, maybe
> for sake of learning.  Also, what does "Nz" mean?
> --
> Gator
>
>
> "Klatuu" wrote:
>
> > Why not just use an Autonumber field?
> >
> > If you can't for some reason, you can get a new number using the DMax
> > function:
> >
> >   lngNewId = Nz(DMax("[ID]","TableName"),0) + 1
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "Gator" wrote:
> >
> > > What code can I use to assign a number as an ID# to a new record?  I
> > > basically would add 1 to the previous ID.
> > > --
> > > Gator
```
 0

```What do I use to just simply add 1 to an existing record?
--
Gator

"Klatuu" wrote:

> The Nz function is used to convert Null values to some other value.
> It does not chage the value of non Null values.
> The reason for putting it in this code is that when you create the first
> record, the DMax would return Null, becase there are no records.  If you were
> to add 1 to Null, you would get Null.  So to avoid that possibility, it
> converts the Null to 0 and 0 + 1 = 1.  That would be the first record.
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "Gator" wrote:
>
> > I could probably use auto number...I just figured I would assign code, maybe
> > for sake of learning.  Also, what does "Nz" mean?
> > --
> > Gator
> >
> >
> > "Klatuu" wrote:
> >
> > > Why not just use an Autonumber field?
> > >
> > > If you can't for some reason, you can get a new number using the DMax
> > > function:
> > >
> > >   lngNewId = Nz(DMax("[ID]","TableName"),0) + 1
> > > --
> > > Dave Hargis, Microsoft Access MVP
> > >
> > >
> > > "Gator" wrote:
> > >
> > > > What code can I use to assign a number as an ID# to a new record?  I
> > > > basically would add 1 to the previous ID.
> > > > --
> > > > Gator
```
 0

```I don't understand your question.  Do you mean add 1 to the id already in an
existing record?
--
Dave Hargis, Microsoft Access MVP

"Gator" wrote:

> What do I use to just simply add 1 to an existing record?
> --
> Gator
>
>
> "Klatuu" wrote:
>
> > The Nz function is used to convert Null values to some other value.
> > It does not chage the value of non Null values.
> > The reason for putting it in this code is that when you create the first
> > record, the DMax would return Null, becase there are no records.  If you were
> > to add 1 to Null, you would get Null.  So to avoid that possibility, it
> > converts the Null to 0 and 0 + 1 = 1.  That would be the first record.
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "Gator" wrote:
> >
> > > I could probably use auto number...I just figured I would assign code, maybe
> > > for sake of learning.  Also, what does "Nz" mean?
> > > --
> > > Gator
> > >
> > >
> > > "Klatuu" wrote:
> > >
> > > > Why not just use an Autonumber field?
> > > >
> > > > If you can't for some reason, you can get a new number using the DMax
> > > > function:
> > > >
> > > >   lngNewId = Nz(DMax("[ID]","TableName"),0) + 1
> > > > --
> > > > Dave Hargis, Microsoft Access MVP
> > > >
> > > >
> > > > "Gator" wrote:
> > > >
> > > > > What code can I use to assign a number as an ID# to a new record?  I
> > > > > basically would add 1 to the previous ID.
> > > > > --
> > > > > Gator
```
 0

```I am using a List Box Control on an Form to automatically add new records.
I'm only entering data in a couple of the fields and the other fields are
automatically filled based on the data in the list that I select (which I am
not displaying on the form).  The ID is a field that I am not planning to
display and enter data.  On the ADOrs.AddNew command, I want the ID to
automatically be added, which will naturally be just an increment from the
previous record.  How do I code that increment?  Thankx
--
Gator

"Klatuu" wrote:

> I don't understand your question.  Do you mean add 1 to the id already in an
> existing record?
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "Gator" wrote:
>
> > What do I use to just simply add 1 to an existing record?
> > --
> > Gator
> >
> >
> > "Klatuu" wrote:
> >
> > > The Nz function is used to convert Null values to some other value.
> > > It does not chage the value of non Null values.
> > > The reason for putting it in this code is that when you create the first
> > > record, the DMax would return Null, becase there are no records.  If you were
> > > to add 1 to Null, you would get Null.  So to avoid that possibility, it
> > > converts the Null to 0 and 0 + 1 = 1.  That would be the first record.
> > > --
> > > Dave Hargis, Microsoft Access MVP
> > >
> > >
> > > "Gator" wrote:
> > >
> > > > I could probably use auto number...I just figured I would assign code, maybe
> > > > for sake of learning.  Also, what does "Nz" mean?
> > > > --
> > > > Gator
> > > >
> > > >
> > > > "Klatuu" wrote:
> > > >
> > > > > Why not just use an Autonumber field?
> > > > >
> > > > > If you can't for some reason, you can get a new number using the DMax
> > > > > function:
> > > > >
> > > > >   lngNewId = Nz(DMax("[ID]","TableName"),0) + 1
> > > > > --
> > > > > Dave Hargis, Microsoft Access MVP
> > > > >
> > > > >
> > > > > "Gator" wrote:
> > > > >
> > > > > > What code can I use to assign a number as an ID# to a new record?  I
> > > > > > basically would add 1 to the previous ID.
> > > > > > --
> > > > > > Gator
```
 0

```Make it the default value of the control:
=Nz(DMax("[ID]","TableName"),0) + 1

--
Dave Hargis, Microsoft Access MVP

"Gator" wrote:

> I am using a List Box Control on an Form to automatically add new records.
> I'm only entering data in a couple of the fields and the other fields are
> automatically filled based on the data in the list that I select (which I am
> not displaying on the form).  The ID is a field that I am not planning to
> display and enter data.  On the ADOrs.AddNew command, I want the ID to
> automatically be added, which will naturally be just an increment from the
> previous record.  How do I code that increment?  Thankx
> --
> Gator
>
>
> "Klatuu" wrote:
>
> > I don't understand your question.  Do you mean add 1 to the id already in an
> > existing record?
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "Gator" wrote:
> >
> > > What do I use to just simply add 1 to an existing record?
> > > --
> > > Gator
> > >
> > >
> > > "Klatuu" wrote:
> > >
> > > > The Nz function is used to convert Null values to some other value.
> > > > It does not chage the value of non Null values.
> > > > The reason for putting it in this code is that when you create the first
> > > > record, the DMax would return Null, becase there are no records.  If you were
> > > > to add 1 to Null, you would get Null.  So to avoid that possibility, it
> > > > converts the Null to 0 and 0 + 1 = 1.  That would be the first record.
> > > > --
> > > > Dave Hargis, Microsoft Access MVP
> > > >
> > > >
> > > > "Gator" wrote:
> > > >
> > > > > I could probably use auto number...I just figured I would assign code, maybe
> > > > > for sake of learning.  Also, what does "Nz" mean?
> > > > > --
> > > > > Gator
> > > > >
> > > > >
> > > > > "Klatuu" wrote:
> > > > >
> > > > > > Why not just use an Autonumber field?
> > > > > >
> > > > > > If you can't for some reason, you can get a new number using the DMax
> > > > > > function:
> > > > > >
> > > > > >   lngNewId = Nz(DMax("[ID]","TableName"),0) + 1
> > > > > > --
> > > > > > Dave Hargis, Microsoft Access MVP
> > > > > >
> > > > > >
> > > > > > "Gator" wrote:
> > > > > >
> > > > > > > What code can I use to assign a number as an ID# to a new record?  I
> > > > > > > basically would add 1 to the previous ID.
> > > > > > > --
> > > > > > > Gator
```
 0

```I'm geting an error
my table is main2007 and the field is ID

adors!ID = Nz(DMax([ID], main2007), 0) + 1
--
Gator

"Klatuu" wrote:

> Make it the default value of the control:
> =Nz(DMax("[ID]","TableName"),0) + 1
>
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "Gator" wrote:
>
> > I am using a List Box Control on an Form to automatically add new records.
> > I'm only entering data in a couple of the fields and the other fields are
> > automatically filled based on the data in the list that I select (which I am
> > not displaying on the form).  The ID is a field that I am not planning to
> > display and enter data.  On the ADOrs.AddNew command, I want the ID to
> > automatically be added, which will naturally be just an increment from the
> > previous record.  How do I code that increment?  Thankx
> > --
> > Gator
> >
> >
> > "Klatuu" wrote:
> >
> > > I don't understand your question.  Do you mean add 1 to the id already in an
> > > existing record?
> > > --
> > > Dave Hargis, Microsoft Access MVP
> > >
> > >
> > > "Gator" wrote:
> > >
> > > > What do I use to just simply add 1 to an existing record?
> > > > --
> > > > Gator
> > > >
> > > >
> > > > "Klatuu" wrote:
> > > >
> > > > > The Nz function is used to convert Null values to some other value.
> > > > > It does not chage the value of non Null values.
> > > > > The reason for putting it in this code is that when you create the first
> > > > > record, the DMax would return Null, becase there are no records.  If you were
> > > > > to add 1 to Null, you would get Null.  So to avoid that possibility, it
> > > > > converts the Null to 0 and 0 + 1 = 1.  That would be the first record.
> > > > > --
> > > > > Dave Hargis, Microsoft Access MVP
> > > > >
> > > > >
> > > > > "Gator" wrote:
> > > > >
> > > > > > I could probably use auto number...I just figured I would assign code, maybe
> > > > > > for sake of learning.  Also, what does "Nz" mean?
> > > > > > --
> > > > > > Gator
> > > > > >
> > > > > >
> > > > > > "Klatuu" wrote:
> > > > > >
> > > > > > > Why not just use an Autonumber field?
> > > > > > >
> > > > > > > If you can't for some reason, you can get a new number using the DMax
> > > > > > > function:
> > > > > > >
> > > > > > >   lngNewId = Nz(DMax("[ID]","TableName"),0) + 1
> > > > > > > --
> > > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > >
> > > > > > >
> > > > > > > "Gator" wrote:
> > > > > > >
> > > > > > > > What code can I use to assign a number as an ID# to a new record?  I
> > > > > > > > basically would add 1 to the previous ID.
> > > > > > > > --
> > > > > > > > Gator
```
 0

```nevermind, it works.  I removed the brackets on ID.
--
Gator

"Klatuu" wrote:

> Make it the default value of the control:
> =Nz(DMax("[ID]","TableName"),0) + 1
>
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "Gator" wrote:
>
> > I am using a List Box Control on an Form to automatically add new records.
> > I'm only entering data in a couple of the fields and the other fields are
> > automatically filled based on the data in the list that I select (which I am
> > not displaying on the form).  The ID is a field that I am not planning to
> > display and enter data.  On the ADOrs.AddNew command, I want the ID to
> > automatically be added, which will naturally be just an increment from the
> > previous record.  How do I code that increment?  Thankx
> > --
> > Gator
> >
> >
> > "Klatuu" wrote:
> >
> > > I don't understand your question.  Do you mean add 1 to the id already in an
> > > existing record?
> > > --
> > > Dave Hargis, Microsoft Access MVP
> > >
> > >
> > > "Gator" wrote:
> > >
> > > > What do I use to just simply add 1 to an existing record?
> > > > --
> > > > Gator
> > > >
> > > >
> > > > "Klatuu" wrote:
> > > >
> > > > > The Nz function is used to convert Null values to some other value.
> > > > > It does not chage the value of non Null values.
> > > > > The reason for putting it in this code is that when you create the first
> > > > > record, the DMax would return Null, becase there are no records.  If you were
> > > > > to add 1 to Null, you would get Null.  So to avoid that possibility, it
> > > > > converts the Null to 0 and 0 + 1 = 1.  That would be the first record.
> > > > > --
> > > > > Dave Hargis, Microsoft Access MVP
> > > > >
> > > > >
> > > > > "Gator" wrote:
> > > > >
> > > > > > I could probably use auto number...I just figured I would assign code, maybe
> > > > > > for sake of learning.  Also, what does "Nz" mean?
> > > > > > --
> > > > > > Gator
> > > > > >
> > > > > >
> > > > > > "Klatuu" wrote:
> > > > > >
> > > > > > > Why not just use an Autonumber field?
> > > > > > >
> > > > > > > If you can't for some reason, you can get a new number using the DMax
> > > > > > > function:
> > > > > > >
> > > > > > >   lngNewId = Nz(DMax("[ID]","TableName"),0) + 1
> > > > > > > --
> > > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > >
> > > > > > >
> > > > > > > "Gator" wrote:
> > > > > > >
> > > > > > > > What code can I use to assign a number as an ID# to a new record?  I
> > > > > > > > basically would add 1 to the previous ID.
> > > > > > > > --
> > > > > > > > Gator
```
 0

9 Replies
607 Views

Similiar Articles:

7/10/2012 12:22:53 PM