Deleting last record causes key field to increment...

[Ozzone]

I have a primary key field that is a code - 2 numbers, a letter, then 2 more
numbers. So on my data entry form, i have code that auto-increments the last
2 digits by one, based off the previously entered value. Here's the code:

' global variable to store last used TreeID for this session
Dim gstrLastTreeID As String

Private Sub Form_Current()

' declare private variables
Dim strTempTreeID As String
Dim strBaseID As String
Dim intIncrementID As Integer

' assign the global to a private
strTempTreeID = gstrLastTreeID

' if TreeId has a value do nothing, if it's Null then...
If IsNull(Me![TreeID]) Then
' if the global has not been set yet then...
If strTempTreeID = "" Then
Exit Sub
'but if the global has been set...
Else
' then make the current TreeID one higher than the last one
strBaseID = Left$(strTempTreeID, Len(strTempTreeID) - 2)
intIncrementID = Val(Right(strTempTreeID, 2) + 1)
Me![TreeID] = strBaseID & IIf(intIncrementID < 10, "0" &
Format(intIncrementID),
Format(intIncrementID))
End If
End If
End Sub

Private Sub Form_AfterUpdate()
' only update the global variable if TreeID has a value
If Not IsNull(Me![TreeID]) Then gstrLastTreeID = Me![TreeID]
End Sub

It works great until i need to delete a record that is at the EndOfFile, or
the last record. If i try to delete the last record, it just increments the
TreeId every time i hit delete, no delete confirmation or anything. I can
delete other records normally.

When i first open the form, i can delete the last record, which means if the
global variable is empty, it all works fine.

I tried putting the code in the TreeId text box events Enter and AfterUpdate
but had a different problem... would only auto-increment if i typed the value
in, just
tabbing out didnt trigger the AfterUpdate event. So every other record i had
to type in the value.

im not sure why its behaving in this manner, but when i hit delete, the only
event being called (as fas as i can tell), is the Form_AfterUpdate. Maybe i
need to move that code to a different event?? I mention TreeId being a
primary key field because i have a feeling that has something to do with it
too.

Any and all input welcomed

 

[SteveS]

I have a primary key field that is a code - 2 numbers, a letter, then 2 more
numbers. So on my data entry form, i have code that auto-increments the last
2 digits by one, based off the previously entered value. Here's the code:

' global variable to store last used TreeID for this session
Dim gstrLastTreeID As String

Private Sub Form_Current()

' declare private variables
Dim strTempTreeID As String
Dim strBaseID As String
Dim intIncrementID As Integer

' assign the global to a private
strTempTreeID = gstrLastTreeID

' if TreeId has a value do nothing, if it's Null then...
If IsNull(Me![TreeID]) Then
' if the global has not been set yet then...
If strTempTreeID = "" Then
Exit Sub
'but if the global has been set...
Else
' then make the current TreeID one higher than the last one
strBaseID = Left$(strTempTreeID, Len(strTempTreeID) - 2)
intIncrementID = Val(Right(strTempTreeID, 2) + 1)
Me![TreeID] = strBaseID & IIf(intIncrementID < 10, "0" &
Format(intIncrementID),
Format(intIncrementID))
End If
End If
End Sub

Private Sub Form_AfterUpdate()
' only update the global variable if TreeID has a value
If Not IsNull(Me![TreeID]) Then gstrLastTreeID = Me![TreeID]
End Sub

It works great until i need to delete a record that is at the EndOfFile, or
the last record. If i try to delete the last record, it just increments the
TreeId every time i hit delete, no delete confirmation or anything. I can
delete other records normally.

When i first open the form, i can delete the last record, which means if the
global variable is empty, it all works fine.

I tried putting the code in the TreeId text box events Enter and AfterUpdate
but had a different problem... would only auto-increment if i typed the value
in, just
tabbing out didnt trigger the AfterUpdate event. So every other record i had
to type in the value.

im not sure why its behaving in this manner, but when i hit delete, the only
event being called (as fas as i can tell), is the Form_AfterUpdate. Maybe i
need to move that code to a different event?? I mention TreeId being a
primary key field because i have a feeling that has something to do with it
too.

Any and all input welcomed

Here's what I think is happening.

You have the [TreeID] increment code in the Form_Current event. This
event occurs when a form is opened, when the form is refreshed or
requeried, and whenever the focus leaves one record and moves to another.

So when the focus is the last record and you delete it, the new record
gets the focus. The Form_Current event fires, sees that [TreeID] is null
and put a value in the field.

I think a better event would be the Form_BeforeUpdate().

-----------------------
Something else I noticed that might be of concern...

What will happen when you reach 99. I don't see any code to increment
the letter.

If the primary key you create will (must?) *ALWAYS* be two numbers, a
letter and two numbers, what happens when you reach 99? It look like 100
will be after the letter. And it gets worse at 199.

Lets say the current number is "22A199". the next number (by your code)
will be "22A1100", not "22A200". The number after that will be
"22A1101"; I don't think that is what you want.

The reason I say the next number will be "22A1101" is this line:

strBaseID = Left$(strTempTreeID, Len(strTempTreeID) - 2)

It sets strBaseID to the number minus the last two chars. If after the
letter is 100, strBaseID (using my example of the number - "22A100")
will be "22A1" , not "22A".

A better line might be:

strBaseID = Left$(strTempTreeID, 3)

but only if the first three chars of [TreeID] will ALWAYS be
"number, number, Letter"



HTH

 

[Ozzone]

Thanks for responding Steve.

The last 2 digits of TreeID will rarely go over 50 and should not ever
exceed 99, in this instance. Nice observation For the benefit of
other readers, if i was to make the change to strBaseID as you suggest, i
would also have to change intIncrementID to:

intIncrementID = Val( Right( strTempTreeID, Len( strTempTreeID )-3 ) + 1 )

I tried moving the incrementing code to the Form_BeforeUpdateEvent,
expecting it to increment the record i was exiting. I need the incrementing
event to happen as i enter the record, not as i leave it, as i believed the
BeforeUpdateEvent to do.

To my surprise, nothing happened. TreeID is always empty when i tab thru to
a new record. So i set a toggle point in each event, Form_BeforeUpdate and
Form_AfterUpdate. When the BeforeUpdate event fires, TreeID is not Null so
the the first If statement fails. I could tweak the code fit the event but i
still think theres a timing issue. Heres the scenerio, as i see it:

Open form, goto new record.
Type in first TreeID, followed by other data.
Update the global variable before the record loses focus.
New record gets focus. Check value of TreeID.
If it has a value, do nothing. Dont want to change existing values by
clicking in the field or changing records. This check also prevents anything
from happening when the form is opened.
If it is empty, insert a calculated value based off the stored global
variable.
Update the global variable before the record loses focus.
Rinse and repeat as needed...

This is my first real application with Access, though i do have some
programming skills. Thanks for explaining the Form_Current event, that makes
sense now. I still dont know the ordering for event triggers and i guess
thats where my problem lies.

If you can make heads or tails of it all, please advise.....thanks

Here's what I think is happening.

You have the [TreeID] increment code in the Form_Current event. This
event occurs when a form is opened, when the form is refreshed or
requeried, and whenever the focus leaves one record and moves to another.

So when the focus is the last record and you delete it, the new record
gets the focus. The Form_Current event fires, sees that [TreeID] is null
and put a value in the field.

I think a better event would be the Form_BeforeUpdate().

-----------------------
Something else I noticed that might be of concern...

What will happen when you reach 99. I don't see any code to increment
the letter.

If the primary key you create will (must?) *ALWAYS* be two numbers, a
letter and two numbers, what happens when you reach 99? It look like 100
will be after the letter. And it gets worse at 199.

Lets say the current number is "22A199". the next number (by your code)
will be "22A1100", not "22A200". The number after that will be
"22A1101"; I don't think that is what you want.

The reason I say the next number will be "22A1101" is this line:

strBaseID = Left$(strTempTreeID, Len(strTempTreeID) - 2)

It sets strBaseID to the number minus the last two chars. If after the
letter is 100, strBaseID (using my example of the number - "22A100")
will be "22A1" , not "22A".

A better line might be:

strBaseID = Left$(strTempTreeID, 3)

but only if the first three chars of [TreeID] will ALWAYS be
"number, number, Letter"



HTH

 

[SteveS]

Thanks for responding Steve.

The last 2 digits of TreeID will rarely go over 50 and should not ever
exceed 99, in this instance. Nice observation For the benefit of
other readers, if i was to make the change to strBaseID as you suggest, i
would also have to change intIncrementID to:

intIncrementID = Val( Right( strTempTreeID, Len( strTempTreeID )-3 ) + 1 )

I tried moving the incrementing code to the Form_BeforeUpdateEvent,
expecting it to increment the record i was exiting. I need the incrementing
event to happen as i enter the record, not as i leave it, as i believed the
BeforeUpdateEvent to do.

To my surprise, nothing happened. TreeID is always empty when i tab thru to
a new record. So i set a toggle point in each event, Form_BeforeUpdate and
Form_AfterUpdate. When the BeforeUpdate event fires, TreeID is not Null so
the the first If statement fails. I could tweak the code fit the event but i
still think theres a timing issue. Heres the scenerio, as i see it:

Open form, goto new record.
Type in first TreeID, followed by other data.
Update the global variable before the record loses focus.
New record gets focus. Check value of TreeID.
If it has a value, do nothing. Dont want to change existing values by
clicking in the field or changing records. This check also prevents anything
from happening when the form is opened.
If it is empty, insert a calculated value based off the stored global
variable.
Update the global variable before the record loses focus.
Rinse and repeat as needed...

This is my first real application with Access, though i do have some
programming skills. Thanks for explaining the Form_Current event, that makes
sense now. I still dont know the ordering for event triggers and i guess
thats where my problem lies.

If you can make heads or tails of it all, please advise.....thanks

If you still want to use the Form_Current event, you could check to see
if you were in a new record before checking if [TreeID] has a value or
not. If not a new record the exit sub... else run the code.

See => http://www.mvps.org/access/forms/frm0010.htm


I'm still thinking about your problem.... meanwhile...


Order of events From A2K Help:

----For FORM ----
When you move the focus to an existing record on a form, enter or change
data in the record, and then move the focus to another record, the
following sequence of events occurs for the form:

Current (form) => BeforeUpdate (form) => AfterUpdate (form) => Current
(form)


----For NEW RECORD ----
When you move the focus to a new (blank) record on a form and then
create a new record by typing in a control, the following sequence of
events occurs:

Current (form) => Enter (control) => GotFocus (control) => BeforeInsert
(form) => AfterInsert (form)

The BeforeUpdate and AfterUpdate events for the controls on the form and
for the new record occur after the BeforeInsert event and before the
AfterInsert event.
---




Also, you might try substituting

If IsNull(Me![TreeID]) Then .....

with

If Len(Trim(Nz(Me.[TreeID],""))) = 0 Then ......


It check for Null, zero length string and string with spaces.

 

[Ozzone]

Thanks again Steve, youve been quite helpful. I am now using the
Form.NewRecord property instead of testing for a null value in Tree ID and
after much mulling over the event orders, this is what ive come with so far:

Dim gstrLastTreeID As String
Dim gbRecordDeleted As Boolean

Private Sub Form_AfterDelConfirm(Status As Integer)
Select Case Status
Case acDeleteOK
gbRecordDeleted = True
End Select
End Sub

Private Sub Form_AfterUpdate()
' only update the global variable if TreeID has a value
If Not IsNull(Nz(Me![TreeID])) Then gstrLastTreeID = Me![TreeID]
End Sub

Private Sub Form_Current()
Dim strTempTreeID As String
Dim strBaseID As String
Dim intIncrementID As Integer

' assign the global to a private
strTempTreeID = gstrLastTreeID

If gbRecordDeleted = True Then
gbRecordDeleted = False
Exit Sub
Else
' if this is a new record...
If Me.NewRecord Then
' and if the global has not been set yet then...
If strTempTreeID = "" Then
Exit Sub
'but the global has been set...
Else
' then make the current TreeID one higher than the last one
strBaseID = Left$(strTempTreeID, 3)
intIncrementID = Val(Right(strTempTreeID, Len(strTempTreeID) -
3) + 1)
Me![TreeID] = strBaseID & IIf(intIncrementID < 10, "0" &
Format(intIncrementID), Format(intIncrementID))
End If
End If
End If
End Sub

As you can see, im using a flag to indicate whan a record has been deleted,
so i can exit the sub if thats the case. I want to put the Select statement
in an IF statetment to first test if its the last record, but i dont know how
to test for the last record.

As it is, it's nearly working perfectly. I can delete the last record, but
only if im deleting it by itself. If i delete a group of records, the delete
confirmation box nor the AfterDelConfirm event fires. Also, when i delete
the last record by itself, i have to change records, then come back to the
new record for the TreeId value to be filled in. Too tired to go on tonight,
but its getting there.

Again, youre input is appreciated (and needed)


:

If you still want to use the Form_Current event, you could check to see
if you were in a new record before checking if [TreeID] has a value or
not. If not a new record the exit sub... else run the code.

See => http://www.mvps.org/access/forms/frm0010.htm


I'm still thinking about your problem.... meanwhile...


Order of events From A2K Help:

----For FORM ----
When you move the focus to an existing record on a form, enter or change
data in the record, and then move the focus to another record, the
following sequence of events occurs for the form:

Current (form) => BeforeUpdate (form) => AfterUpdate (form) => Current
(form)


----For NEW RECORD ----
When you move the focus to a new (blank) record on a form and then
create a new record by typing in a control, the following sequence of
events occurs:

Current (form) => Enter (control) => GotFocus (control) => BeforeInsert
(form) => AfterInsert (form)

The BeforeUpdate and AfterUpdate events for the controls on the form and
for the new record occur after the BeforeInsert event and before the
AfterInsert event.
---




Also, you might try substituting

If IsNull(Me![TreeID]) Then .....

with

If Len(Trim(Nz(Me.[TreeID],""))) = 0 Then ......


It check for Null, zero length string and string with spaces.

 

[Ozzone]

My bad, when deleting multiple records which includes the last record, the
AfterDelConfirm event does fire, but because the delete confirmation dialog
does not take place, the flag does not get set.

Thanks for responding Steve.

The last 2 digits of TreeID will rarely go over 50 and should not ever
exceed 99, in this instance. Nice observation For the benefit of
other readers, if i was to make the change to strBaseID as you suggest, i
would also have to change intIncrementID to:

intIncrementID = Val( Right( strTempTreeID, Len( strTempTreeID )-3 ) + 1 )

I tried moving the incrementing code to the Form_BeforeUpdateEvent,
expecting it to increment the record i was exiting. I need the incrementing
event to happen as i enter the record, not as i leave it, as i believed the
BeforeUpdateEvent to do.

To my surprise, nothing happened. TreeID is always empty when i tab thru to
a new record. So i set a toggle point in each event, Form_BeforeUpdate and
Form_AfterUpdate. When the BeforeUpdate event fires, TreeID is not Null so
the the first If statement fails. I could tweak the code fit the event but i
still think theres a timing issue. Heres the scenerio, as i see it:

Open form, goto new record.
Type in first TreeID, followed by other data.
Update the global variable before the record loses focus.
New record gets focus. Check value of TreeID.
If it has a value, do nothing. Dont want to change existing values by
clicking in the field or changing records. This check also prevents anything
from happening when the form is opened.
If it is empty, insert a calculated value based off the stored global
variable.
Update the global variable before the record loses focus.
Rinse and repeat as needed...

This is my first real application with Access, though i do have some
programming skills. Thanks for explaining the Form_Current event, that makes
sense now. I still dont know the ordering for event triggers and i guess
thats where my problem lies.

If you can make heads or tails of it all, please advise.....thanks

If you still want to use the Form_Current event, you could check to see
if you were in a new record before checking if [TreeID] has a value or
not. If not a new record the exit sub... else run the code.

See => http://www.mvps.org/access/forms/frm0010.htm


I'm still thinking about your problem.... meanwhile...


Order of events From A2K Help:

----For FORM ----
When you move the focus to an existing record on a form, enter or change
data in the record, and then move the focus to another record, the
following sequence of events occurs for the form:

Current (form) => BeforeUpdate (form) => AfterUpdate (form) => Current
(form)


----For NEW RECORD ----
When you move the focus to a new (blank) record on a form and then
create a new record by typing in a control, the following sequence of
events occurs:

Current (form) => Enter (control) => GotFocus (control) => BeforeInsert
(form) => AfterInsert (form)

The BeforeUpdate and AfterUpdate events for the controls on the form and
for the new record occur after the BeforeInsert event and before the
AfterInsert event.
---




Also, you might try substituting

If IsNull(Me![TreeID]) Then .....

with

If Len(Trim(Nz(Me.[TreeID],""))) = 0 Then ......


It check for Null, zero length string and string with spaces.

 

[Ozzone]

i started a new thread...."Repost - Nearly got it, need freash
perspective(s)...

Thanks for responding Steve.

The last 2 digits of TreeID will rarely go over 50 and should not ever
exceed 99, in this instance. Nice observation For the benefit of
other readers, if i was to make the change to strBaseID as you suggest, i
would also have to change intIncrementID to:

intIncrementID = Val( Right( strTempTreeID, Len( strTempTreeID )-3 ) + 1 )

I tried moving the incrementing code to the Form_BeforeUpdateEvent,
expecting it to increment the record i was exiting. I need the incrementing
event to happen as i enter the record, not as i leave it, as i believed the
BeforeUpdateEvent to do.

To my surprise, nothing happened. TreeID is always empty when i tab thru to
a new record. So i set a toggle point in each event, Form_BeforeUpdate and
Form_AfterUpdate. When the BeforeUpdate event fires, TreeID is not Null so
the the first If statement fails. I could tweak the code fit the event but i
still think theres a timing issue. Heres the scenerio, as i see it:

Open form, goto new record.
Type in first TreeID, followed by other data.
Update the global variable before the record loses focus.
New record gets focus. Check value of TreeID.
If it has a value, do nothing. Dont want to change existing values by
clicking in the field or changing records. This check also prevents anything
from happening when the form is opened.
If it is empty, insert a calculated value based off the stored global
variable.
Update the global variable before the record loses focus.
Rinse and repeat as needed...

This is my first real application with Access, though i do have some
programming skills. Thanks for explaining the Form_Current event, that makes
sense now. I still dont know the ordering for event triggers and i guess
thats where my problem lies.

If you can make heads or tails of it all, please advise.....thanks

If you still want to use the Form_Current event, you could check to see
if you were in a new record before checking if [TreeID] has a value or
not. If not a new record the exit sub... else run the code.

See => http://www.mvps.org/access/forms/frm0010.htm


I'm still thinking about your problem.... meanwhile...


Order of events From A2K Help:

----For FORM ----
When you move the focus to an existing record on a form, enter or change
data in the record, and then move the focus to another record, the
following sequence of events occurs for the form:

Current (form) => BeforeUpdate (form) => AfterUpdate (form) => Current
(form)


----For NEW RECORD ----
When you move the focus to a new (blank) record on a form and then
create a new record by typing in a control, the following sequence of
events occurs:

Current (form) => Enter (control) => GotFocus (control) => BeforeInsert
(form) => AfterInsert (form)

The BeforeUpdate and AfterUpdate events for the controls on the form and
for the new record occur after the BeforeInsert event and before the
AfterInsert event.
---




Also, you might try substituting

If IsNull(Me![TreeID]) Then .....

with

If Len(Trim(Nz(Me.[TreeID],""))) = 0 Then ......


It check for Null, zero length string and string with spaces.