Repost - Nearly got it, need fresh perspective(s)...

[Ozzone]

This is a repost. The original post is 4 pages down from this one entitled
"Deleteing a record causes key field to increment".

TreeId is a primary key field, and its initial value is typed on a form,
subsequent values are calculated based off what was typed in the first new
record for that session. This is what im trying to accomplish:

Open form, do nothing.
Goto new record. Type in first TreeID, followed by other data.
Update a global variable before the record loses focus.
New record gets focus. Check if this is a new record.
If it is, insert a calculated value based off the global variable.
If its not, do nothing.
Update the global variable before the record loses focus.
Rinse and repeat as needed...

In my original code (see original post), i had problems when deleting the
last record. With SteveS help, ive got the code tweaked to this:

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 on a form.

As it is, it's nearly working perfectly. After creating a few new records,
I can now delete the last record, however...

Problem #1
If i delete a group of records which includes the last record, the delete
confirmation never appears, so the AfterDelConfirm event never fires. Why is
this and how do i correct/compensate?

Problem #2
When i delete the last record by itself, the focus ends up in the new record
row with the TreeID field empty. I have to change records, then come back to
the new record for the TreeId value to be filled in. If i then delete that
record before creating another new one, the delete confirmation never
appears, so the AfterDelConfirm event never fires. If i move on and create a
2nd new record, then im back to Problem #1.

The most common reason for deleting the last record is tabbing thru and
having a new record created when you are finished with the data entry
session. Because TreeID is automatically filled in, that record must be
deleted. It would be very uncommon to have to delete multiple records that
includes the last record, but i want to fully understand whats happening
behind the scenes and be prepared for all possible scenarios.

Whats preventing the delete confirmation in the above scenarios? How do
check to see if im in the last record on a form?

All feedback welcomed, especially if you know an easier, softer way
Ozzone

 

[Ozzone]

bah, i stated that the AfterDelConfim event doesnt fire but i misspoke. The
event fires, but because the delete confirmation dialog doesnt appear,
acDeleteOK does not evaluate to true, therefore the flag doesnt get set in
the afore mentioned scenarios. Sorry for any confusion.

Ozzone

This is a repost. The original post is 4 pages down from this one entitled
"Deleteing a record causes key field to increment".

TreeId is a primary key field, and its initial value is typed on a form,
subsequent values are calculated based off what was typed in the first new
record for that session. This is what im trying to accomplish:

Open form, do nothing.
Goto new record. Type in first TreeID, followed by other data.
Update a global variable before the record loses focus.
New record gets focus. Check if this is a new record.
If it is, insert a calculated value based off the global variable.
If its not, do nothing.
Update the global variable before the record loses focus.
Rinse and repeat as needed...

In my original code (see original post), i had problems when deleting the
last record. With SteveS help, ive got the code tweaked to this:

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 on a form.

As it is, it's nearly working perfectly. After creating a few new records,
I can now delete the last record, however...

Problem #1
If i delete a group of records which includes the last record, the delete
confirmation never appears, so the AfterDelConfirm event never fires. Why is
this and how do i correct/compensate?

Problem #2
When i delete the last record by itself, the focus ends up in the new record
row with the TreeID field empty. I have to change records, then come back to
the new record for the TreeId value to be filled in. If i then delete that
record before creating another new one, the delete confirmation never
appears, so the AfterDelConfirm event never fires. If i move on and create a
2nd new record, then im back to Problem #1.

The most common reason for deleting the last record is tabbing thru and
having a new record created when you are finished with the data entry
session. Because TreeID is automatically filled in, that record must be
deleted. It would be very uncommon to have to delete multiple records that
includes the last record, but i want to fully understand whats happening
behind the scenes and be prepared for all possible scenarios.

Whats preventing the delete confirmation in the above scenarios? How do
check to see if im in the last record on a form?

All feedback welcomed, especially if you know an easier, softer way
Ozzone

 

[Dirk Goldgar]

bah, i stated that the AfterDelConfim event doesnt fire but i
misspoke. The event fires, but because the delete confirmation
dialog doesnt appear, acDeleteOK does not evaluate to true, therefore
the flag doesnt get set in the afore mentioned scenarios. Sorry for
any confusion.

Ozzone

This is a repost. The original post is 4 pages down from this one
entitled "Deleteing a record causes key field to increment".

TreeId is a primary key field, and its initial value is typed on a
form, subsequent values are calculated based off what was typed in
the first new record for that session. This is what im trying to
accomplish:

Open form, do nothing.
Goto new record. Type in first TreeID, followed by other data.
Update a global variable before the record loses focus.
New record gets focus. Check if this is a new record.
If it is, insert a calculated value based off the global variable.
If its not, do nothing.
Update the global variable before the record loses focus.
Rinse and repeat as needed...

In my original code (see original post), i had problems when
deleting the last record. With SteveS help, ive got the code
tweaked to this:

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 on a form.

As it is, it's nearly working perfectly. After creating a few new
records, I can now delete the last record, however...

Problem #1
If i delete a group of records which includes the last record, the
delete confirmation never appears, so the AfterDelConfirm event
never fires. Why is this and how do i correct/compensate?

Problem #2
When i delete the last record by itself, the focus ends up in the
new record row with the TreeID field empty. I have to change
records, then come back to the new record for the TreeId value to be
filled in. If i then delete that record before creating another new
one, the delete confirmation never appears, so the AfterDelConfirm
event never fires. If i move on and create a 2nd new record, then
im back to Problem #1.

The most common reason for deleting the last record is tabbing thru
and having a new record created when you are finished with the data
entry session. Because TreeID is automatically filled in, that
record must be deleted. It would be very uncommon to have to delete
multiple records that includes the last record, but i want to fully
understand whats happening behind the scenes and be prepared for all
possible scenarios.

Whats preventing the delete confirmation in the above scenarios?
How do check to see if im in the last record on a form?

All feedback welcomed, especially if you know an easier, softer way
Ozzone

Since you sort of asked me to stick my nose into this, here I am -- but
I haven't much time at the moment to put into this.

My first reaction is that you're doing more work than I think you need
to -- if, that is, I've correctly figured out what you're trying to have
happen with this form. As I understand it, you're trying to have each
new record after the first one entered in an editing session
automatically be assigned a TreeID that is derived from the first one
entered. With your current approach, you're having problems and
complications due to the fact that are assigning a value to TreeID
whenever you come to a the new record, so you run the risk having the
now-dirtied record saved even if that record wasn't actually wanted.

But it seems to me that you could use the DefaultValue property of the
TreeID text box to accomplish all this, setting a new DefaultValue in
the form's AfterInsert event. If you use the DefaultValue property for
this, the form won't be dirtied until the user actually types something
into one of the other fields.

Taking this approach, you can (I think) replace all the code you
currently have with something along these lines:

'----- start of proposed code -----
Private Sub Form_AfterInsert()

Dim strTreeID As String

strTreeID = Me!TreeID & vbNullString

If Len(strTreeID) > 0 Then

strTreeID = Left$(strTreeID, 3) & _
Format(Val(Mid(strTreeID, 4)) + 1, _
String(Len(strTreeID) - 3, "0"))

Me!TreeID.DefaultValue = Chr(34) & strTreeID & Chr(34)

End If

End Sub
'----- end of proposed code -----

Please note that the expression I've used to come up with the new TreeID
is different from what you posted, and is doing what I *think* yours was
doing, but I'm not sure. I could be mistaken, so take that part with a
grain of salt. And if the numeric portion of TreeID actually has a
fixed number of digits, the code could be simplified.

 

[SteveS]

Hi, its me again.. comments inline...

bah, i stated that the AfterDelConfim event doesnt fire but i misspoke. The
event fires, but because the delete confirmation dialog doesnt appear,
acDeleteOK does not evaluate to true, therefore the flag doesnt get set in
the afore mentioned scenarios. Sorry for any confusion.

See below at Problem #1

Ozzone

See Problem#1 below

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

This If() statement will *ALWAYS* be True! Walking thru it:

Let's set [TreeID] = "22A01".
Evaluating Nz([TreeID]), the function Nz() returns "22A01" - no changes
So IsNull(Nz([TreeID])) is False
and Not IsNull(Nz([TreeID])) is True.
---
Now let's set [TreeID] to Null.
Evaluating Nz([TreeID]) , the function Nz() returns a zero length string
(""), but doesn't change [TreeID]; it still is NULL.

So IsNull("") = False. A zero length string is Empty, but that's not the
same as NULL
and
Not IsNull("") is again True.

Since the If statement is True, gstrLastTreeID is set to TreeID, which is
NULL.

I would use

If Len(Trim(Nz(Me!TreeID)))>0 then

or you could use Dirk's solution (does the same thing but shorter)

strTreeID = Me!TreeID & vbNullString
If Len(strTreeID) > 0 Then

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 on a form.

As it is, it's nearly working perfectly. After creating a few new records,
I can now delete the last record, however...

Problem #1
If i delete a group of records which includes the last record, the delete
confirmation never appears, so the AfterDelConfirm event never fires. Why is
this and how do i correct/compensate?

From Help:

"Note: The BeforeDelConfirm and AfterDelConfirm events don't occur and the
Delete Confirm dialog box isn't displayed if you clear the Record Changes
check box under Confirm on the Edit/Find tab of the Options dialog box,
available by clicking Options on the Tools menu."


The only way I could stop the delete confirmation from appearing was to
uncheck the Record Changes checkbox. You might make sure you have it checked.

Also, you could change the AfterDelConfirm() code to this while debugging:
-----begin code -----
Private Sub Form_AfterDelConfirm(Status As Integer)
Select Case Status
Case acDeleteOK
MsgBox "Deletion occurred normally."
gbRecordDeleted = True
Case acDeleteCancel
MsgBox "Programmer canceled the deletion."
Case acDeleteUserCancel
MsgBox "User canceled the deletion."
End Select
End Sub
----- end code -------

I still think the BeforeUpdate() is the event to use to put a value in
TreeID. I've tried to use the OnCurrent() event before but could never get my
code tweaked just right and ended up using a different event. Like this:
<grin>

--- begin example code -----
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strTempTreeID As String
Dim strBaseID As String
Dim intIncrementID As Integer

' assign the global to a private
strTempTreeID = gstrLastTreeID & ""

'removed the check for deleted. not needed (I think) since
' this only runs when adding a new record

' if this is a new record and data entered
If Me.NewRecord And Me.Dirty Then
If strTempTreeID = "" Then
' if the global is empty ...
MsgBox "Please enter a value for TreeID"
'cancels the update
Cancel = True
Exit Sub
Else
'but the global has been set...
' 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
'Else
' not a new record - do nothing
End If
End Sub
--- end code -----

Maybe (hopefully) Dirk or someone else has more ideas

HTH

 

[Ozzone]

heh, you guys are amazing

Im an old skool programmer, GWBasic and QuickBasic, dabbled a tad in VB4.
So im still learning about objects, properties, methods, events, and the
like. Learning ALOT from watching these boards, heh, anyway...

Dirk, you were absolutely correct. Thank you. I took all my code out,
replaced it with yours, and it works almost like a charm. AND, i understand
why. However i dont understand the purpose of appending vbNullString to the
value being checked in your example.

There is one undesirable aesthetic issue. Using a continuous form, as soon
as the New record becomes dirty, the next New Record row pops with the same
value in TreeID as the now dirty record. The New Record row then increments
after it receives focus. Using a single form, this is not an issue.

Steve, thank you. I followed you completely when evaluating:

If Not IsNull(Nz(Me![TreeID])) Then gstrLastTreeID = Me![TreeID]

Thanks for pointing that out. Another lesson learned

The AfterDelConfirm event was firing. I just wast getting the confirmation
dialog box under the specific circumstances i stated. If i didnt have the
Record Changes checked, the event wouldnt fire at all. So i still dont know
why that was happening but its irrelevant now

I like the Me.Dirty check you through in there. By incorporating that
check, i believe i can avoid the issue mentioned above.

Bottom line.... you guys have given me enough info to figure the rest out on
my own. Its so nice to have a resource like this to goto for advice. Thanks
to you both

Ozzone

 

[Dirk Goldgar]

heh, you guys are amazing

Im an old skool programmer, GWBasic and QuickBasic, dabbled a tad in
VB4.
So im still learning about objects, properties, methods, events, and
the like. Learning ALOT from watching these boards, heh, anyway...

I learned Access the same way.

Dirk, you were absolutely correct. Thank you. I took all my code out,
replaced it with yours, and it works almost like a charm. AND, i
understand why. However i dont understand the purpose of appending
vbNullString to the value being checked in your example.

If Me!TreeID is Null, concatenating it to a zero-length string (I used
the defined constant vbNullString, but I could have just used "") will
yield a zero-length string. Since I need to convert TreeID to a string
anyway, for later processing, this was a handy way of converting to a
string without bothering to test whether TreeID was Null first. Then in
a subsequent statement I just test for Len(strTreeID) > 0 to make sure
TreeID wasn't originally Null.

There is one undesirable aesthetic issue. Using a continuous form,
as soon as the New record becomes dirty, the next New Record row pops
with the same value in TreeID as the now dirty record. The New
Record row then increments after it receives focus. Using a single
form, this is not an issue.

Hmm, that is a problem. I can't think of a good way to get around that,
on a continuous form, without more elaborate programming. I'll think
about it.