Can't Delete last record

[Guest]

I copied a module from the Microsoft site allowing me to carry over values
from the last record to a new appended record, however after the record is
appended the last record will not delete when the Delete button is pressed.
What do I need to do to allow the user to delete the last record?

The code in the module is shown below.

Option Compare Database
'Option Explicit


Function AutoFillNewRecord(F As Form)

Dim rs As DAO.Recordset, C As Control
Dim FillFields As String, FillAllFields As Integer

On Error Resume Next

' Exit if not on the new record.
If Not F.NewRecord Then Exit Function

' Goto the last record of the form recordset (to autofill form).
Set rs = F.RecordsetClone

rs.MoveLast

' Exit if you cannot move to the last record (no records).
If Err <> 0 Then Exit Function

' Get the list of fields to autofill.
FillFields = ";" & F![AutoFillNewRecordFields] & ";"

' If there is no criteria field, then set flag indicating ALL
' fields should be autofilled.
FillAllFields = Err <> 0

F.Painting = False

' Visit each field on the form.
For Each C In F
' Fill the field if ALL fields are to be filled OR if the
' ...ControlSource field can be found in the FillFields list.
If FillAllFields Or InStr(FillFields, ";" & (C.Name) & ";") > 0 Then
C = rs(C.ControlSource)
End If
Next
F.Painting = True
End Function

Thank you,
Garry

 

[Ron2006]

Under what conditions/what events is that function being executed?

 

[Ron2006]

Do you have an autonumber field on the record?

Is it perhaps actually deleting the record, but creating a new
identical record just before you delete the old one.?

If there is an autonumber check before and after the attempted delete
to determine if that is happening.

 

[Guest]

The function executes when the user clicks the record selector at the bottom
of the form. It executes anytime the user moves past the last record on the
form. Yes there is an autonumber field on the record. There is an
autonumber both before and after the attempt to delete. Also the field data
from the last record is carried over to the last record that won't delete.

Thanks for your help

 

[Ron2006]

The reason for checking the autonumber field was to try to determine if
the record was being deleted and a new one being created.

Does the autonumber have the same value before the attempted delete and
After the attempted delete.

I am trying to determine if the delete is actually working BUT the
function is creating a new record before the delete has finished.

So you are executing the function in the oncurrent event when it
recognizes that it is on a newrecord.?

 

[Guest]

You are right. The number is being deleted, and a new one is being added
instantly. Can you you help me stop this behavior.

Thanks so much for you help,
Garry

 

[Ron2006]

Let's see what we can come up with.

Your answer: "The function executes when the user clicks the record
selector at the bottom
of the form. It executes anytime the user moves past the last record
on the
form. " does not specifically say what event is triggering this to
occur.

Can you be a bit more specific. It is probably the oncurrent, but we
need to know which event.

 

[Guest]

I'm sorry. Yes, it is the oncurrent event.

Thank you,
Garry

 

[Ron2006]

OK, I played with the following approach and I think it will cover what
we need to do.

1) Go into one of the events on the actual subform for the records that
we are having the problem with. Go to the top of the form under the
"Option Compare Database: add:

Public dltFlag As Boolean

2) in the ondelete event of the same form add

Private Sub Form_Delete(Cancel As Integer)
dltFlag = True

End Sub

3) in the oncurrent event of the form where the subroutine is being
called arrange it to be something like the following:

If dltflag Then
dltflag = False
' this will turn the flag off BUT also NOT call the add
record subroutine
Else
If Me.NewRecord Then

' Put in here ALL of the logic pertinent to the copy
record logic
' it is probably all of the code currently in the
oncurrent event.

End If
End If

It may be that we want to reverse those two tests to make it something
like the following. You will have to judge that depending on what code
is in the oncurrent event.

If me.NewRecord Then
if dltflag = false then
AutoFillNewRecord ' other code also?
endif
else
AutoFillNewRecord ' other code also?
End If

dltflag = False

' rest of oncurrent event EXCEPT for the AutoFillNewRecord.


Hope this gives you some ideas to play with to get everything done that
you need to have done.

Ron

 

[Guest]

Thanks Ron,

That worked fine. I added one line to move back to the previous undeleted
recorded and it worked great. Thanks again.