Autonum Field is Null

  • Thread starter Thread starter Reinhard
  • Start date Start date
R

Reinhard

I have the following code in an Access 2003 form that creates an
auto-incrementing requisition number. It works when operating on an Access
2003 database. I am in the process of moving this database to SQL server and
using MS Access 2003 as the frontend for now, but the code requisition number
is null when the code is executed in this environment. Is there something
special I need to do when the table is linked to a backend SQL database?

Private Sub PurchasingAgent_AfterUpdate()

'Generate Requisition Number
Me![ReqNoInfo] = Right(DatePart("yyyy", Date), 2) &
[Forms]![frmRequisition]![Acronym] & "-" & [ReqNumber]

End Sub
 
You may have to 'turn on' the Identity property of that field in SQL-Server
("Identity == Autonumber")

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
The field has the Identity property. Could the problem be in the timing when
the field is assigned a value meaning that the field is still Null until the
record is inserted?
 
Aha! Now I understand.

Yes, in Access, the Autonumber on a new record is created when the new
record is first "dirtied" (i.e., anything added to any field).

In SQL-Server, the Identity on a new record is created ONLY when the new
record is saved (i.e., after everything is added).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Reinhard said:
The field has the Identity property. Could the problem be in the timing
when
the field is assigned a value meaning that the field is still Null until
the
record is inserted?

Reinhard said:
I have the following code in an Access 2003 form that creates an
auto-incrementing requisition number. It works when operating on an
Access
2003 database. I am in the process of moving this database to SQL server
and
using MS Access 2003 as the frontend for now, but the code requisition
number
is null when the code is executed in this environment. Is there something
special I need to do when the table is linked to a backend SQL database?

Private Sub PurchasingAgent_AfterUpdate()

'Generate Requisition Number
Me![ReqNoInfo] = Right(DatePart("yyyy", Date), 2) &
[Forms]![frmRequisition]![Acronym] & "-" & [ReqNumber]

End Sub
Click to expand...
Click to expand...
 
Ok, that is what I thought. Is there a way for me to pick up the Identify
field then to have it available in the form? The code I provided gets
executed in an After Update situation.

Jeff Boyce said:
Aha! Now I understand.

Yes, in Access, the Autonumber on a new record is created when the new
record is first "dirtied" (i.e., anything added to any field).

In SQL-Server, the Identity on a new record is created ONLY when the new
record is saved (i.e., after everything is added).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Reinhard said:
The field has the Identity property. Could the problem be in the timing
when
the field is assigned a value meaning that the field is still Null until
the
record is inserted?

Reinhard said:
I have the following code in an Access 2003 form that creates an
auto-incrementing requisition number. It works when operating on an
Access
2003 database. I am in the process of moving this database to SQL server
and
using MS Access 2003 as the frontend for now, but the code requisition
number
is null when the code is executed in this environment. Is there something
special I need to do when the table is linked to a backend SQL database?

Private Sub PurchasingAgent_AfterUpdate()

'Generate Requisition Number
Me![ReqNoInfo] = Right(DatePart("yyyy", Date), 2) &
[Forms]![frmRequisition]![Acronym] & "-" & [ReqNumber]

End Sub
Click to expand...
Click to expand...
Click to expand...
 
One way (a bit of a kludge) is to query the table for the most recent
record, and return the ID.

Regards

Jeff Boyce
Microsoft Office/Access MVP



Reinhard said:
Ok, that is what I thought. Is there a way for me to pick up the Identify
field then to have it available in the form? The code I provided gets
executed in an After Update situation.

Jeff Boyce said:
Aha! Now I understand.

Yes, in Access, the Autonumber on a new record is created when the new
record is first "dirtied" (i.e., anything added to any field).

In SQL-Server, the Identity on a new record is created ONLY when the new
record is saved (i.e., after everything is added).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Reinhard said:
The field has the Identity property. Could the problem be in the timing
when
the field is assigned a value meaning that the field is still Null
until
the
record is inserted?

:

I have the following code in an Access 2003 form that creates an
auto-incrementing requisition number. It works when operating on an
Access
2003 database. I am in the process of moving this database to SQL
server
and
using MS Access 2003 as the frontend for now, but the code requisition
number
is null when the code is executed in this environment. Is there
something
special I need to do when the table is linked to a backend SQL
database?

Private Sub PurchasingAgent_AfterUpdate()

'Generate Requisition Number
Me![ReqNoInfo] = Right(DatePart("yyyy", Date), 2) &
[Forms]![frmRequisition]![Acronym] & "-" & [ReqNumber]

End Sub
Click to expand...
Click to expand...
Click to expand...
 
I guess I would have to do that to get the ID, increment it for the current
record, and then save it as a new record when the rest of the data in the
form was filled in. Would SQL let me specify the record id if that field is
an Identify field?

Jeff Boyce said:
One way (a bit of a kludge) is to query the table for the most recent
record, and return the ID.

Regards

Jeff Boyce
Microsoft Office/Access MVP



Reinhard said:
Ok, that is what I thought. Is there a way for me to pick up the Identify
field then to have it available in the form? The code I provided gets
executed in an After Update situation.

Jeff Boyce said:
Aha! Now I understand.

Yes, in Access, the Autonumber on a new record is created when the new
record is first "dirtied" (i.e., anything added to any field).

In SQL-Server, the Identity on a new record is created ONLY when the new
record is saved (i.e., after everything is added).

Regards

Jeff Boyce
Microsoft Office/Access MVP

The field has the Identity property. Could the problem be in the timing
when
the field is assigned a value meaning that the field is still Null
until
the
record is inserted?

:

I have the following code in an Access 2003 form that creates an
auto-incrementing requisition number. It works when operating on an
Access
2003 database. I am in the process of moving this database to SQL
server
and
using MS Access 2003 as the frontend for now, but the code requisition
number
is null when the code is executed in this environment. Is there
something
special I need to do when the table is linked to a backend SQL
database?

Private Sub PurchasingAgent_AfterUpdate()

'Generate Requisition Number
Me![ReqNoInfo] = Right(DatePart("yyyy", Date), 2) &
[Forms]![frmRequisition]![Acronym] & "-" & [ReqNumber]

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Not sure what you mean by that...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Reinhard said:
I guess I would have to do that to get the ID, increment it for the current
record, and then save it as a new record when the rest of the data in the
form was filled in. Would SQL let me specify the record id if that field
is
an Identify field?

Jeff Boyce said:
One way (a bit of a kludge) is to query the table for the most recent
record, and return the ID.

Regards

Jeff Boyce
Microsoft Office/Access MVP



Reinhard said:
Ok, that is what I thought. Is there a way for me to pick up the
Identify
field then to have it available in the form? The code I provided gets
executed in an After Update situation.

:

Aha! Now I understand.

Yes, in Access, the Autonumber on a new record is created when the new
record is first "dirtied" (i.e., anything added to any field).

In SQL-Server, the Identity on a new record is created ONLY when the
new
record is saved (i.e., after everything is added).

Regards

Jeff Boyce
Microsoft Office/Access MVP

The field has the Identity property. Could the problem be in the
timing
when
the field is assigned a value meaning that the field is still Null
until
the
record is inserted?

:

I have the following code in an Access 2003 form that creates an
auto-incrementing requisition number. It works when operating on an
Access
2003 database. I am in the process of moving this database to SQL
server
and
using MS Access 2003 as the frontend for now, but the code
requisition
number
is null when the code is executed in this environment. Is there
something
special I need to do when the table is linked to a backend SQL
database?

Private Sub PurchasingAgent_AfterUpdate()

'Generate Requisition Number
Me![ReqNoInfo] = Right(DatePart("yyyy", Date), 2) &
[Forms]![frmRequisition]![Acronym] & "-" & [ReqNumber]

End Sub
Click to expand...
Click to expand...
Click to expand...
 
I am asking can I create the new requisition number by looking up the
requisiton number in the last record added, incrementing it by one, and then
inserting the record with this new requisition number (this is the Identity
field). Will SQL allow me to sprecify the value for an Identity field, or
does it need to do that?

Jeff Boyce said:
Not sure what you mean by that...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Reinhard said:
I guess I would have to do that to get the ID, increment it for the current
record, and then save it as a new record when the rest of the data in the
form was filled in. Would SQL let me specify the record id if that field
is
an Identify field?

Jeff Boyce said:
One way (a bit of a kludge) is to query the table for the most recent
record, and return the ID.

Regards

Jeff Boyce
Microsoft Office/Access MVP



Ok, that is what I thought. Is there a way for me to pick up the
Identify
field then to have it available in the form? The code I provided gets
executed in an After Update situation.

:

Aha! Now I understand.

Yes, in Access, the Autonumber on a new record is created when the new
record is first "dirtied" (i.e., anything added to any field).

In SQL-Server, the Identity on a new record is created ONLY when the
new
record is saved (i.e., after everything is added).

Regards

Jeff Boyce
Microsoft Office/Access MVP

The field has the Identity property. Could the problem be in the
timing
when
the field is assigned a value meaning that the field is still Null
until
the
record is inserted?

:

I have the following code in an Access 2003 form that creates an
auto-incrementing requisition number. It works when operating on an
Access
2003 database. I am in the process of moving this database to SQL
server
and
using MS Access 2003 as the frontend for now, but the code
requisition
number
is null when the code is executed in this environment. Is there
something
special I need to do when the table is linked to a backend SQL
database?

Private Sub PurchasingAgent_AfterUpdate()

'Generate Requisition Number
Me![ReqNoInfo] = Right(DatePart("yyyy", Date), 2) &
[Forms]![frmRequisition]![Acronym] & "-" & [ReqNumber]

End Sub
Click to expand...
Click to expand...
Click to expand...
 
As I understand SQL-Server, an "Identity" field doesn't allow you to insert
a value ... it works just like the Autonumber field.

That said, I would expect if you deleted a record, you would NOT get to
reused that Identity (that's the way it works w/ Autonumber).

Regards

Jeff Boyce
Microsoft Office/Access MVP


Reinhard said:
I am asking can I create the new requisition number by looking up the
requisiton number in the last record added, incrementing it by one, and
then
inserting the record with this new requisition number (this is the
Identity
field). Will SQL allow me to sprecify the value for an Identity field, or
does it need to do that?

Jeff Boyce said:
Not sure what you mean by that...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Reinhard said:
I guess I would have to do that to get the ID, increment it for the
current
record, and then save it as a new record when the rest of the data in
the
form was filled in. Would SQL let me specify the record id if that
field
is
an Identify field?

:

One way (a bit of a kludge) is to query the table for the most recent
record, and return the ID.

Regards

Jeff Boyce
Microsoft Office/Access MVP



Ok, that is what I thought. Is there a way for me to pick up the
Identify
field then to have it available in the form? The code I provided
gets
executed in an After Update situation.

:

Aha! Now I understand.

Yes, in Access, the Autonumber on a new record is created when the
new
record is first "dirtied" (i.e., anything added to any field).

In SQL-Server, the Identity on a new record is created ONLY when
the
new
record is saved (i.e., after everything is added).

Regards

Jeff Boyce
Microsoft Office/Access MVP

The field has the Identity property. Could the problem be in the
timing
when
the field is assigned a value meaning that the field is still
Null
until
the
record is inserted?

:

I have the following code in an Access 2003 form that creates an
auto-incrementing requisition number. It works when operating on
an
Access
2003 database. I am in the process of moving this database to
SQL
server
and
using MS Access 2003 as the frontend for now, but the code
requisition
number
is null when the code is executed in this environment. Is there
something
special I need to do when the table is linked to a backend SQL
database?

Private Sub PurchasingAgent_AfterUpdate()

'Generate Requisition Number
Me![ReqNoInfo] = Right(DatePart("yyyy", Date), 2) &
[Forms]![frmRequisition]![Acronym] & "-" & [ReqNumber]

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top