Subform.Requery-problem is killing me.

[Codebuster]

Subform.Requery-problem is killing me.

In Access 97 i have a form with a subform on it. This subform is a
continuous form. Every record on this continuous form has a button do delete
that specific record. This operation is done by calling a public function
which opens the underlaying table of the subform (with the DAO methode) and
delete the specific record. After this deletion i want the subform only to
show the existing records in de table, so a command a requery of the
subform. If i open de main form and make no changes to the records in the
subform this operation works fine, but.....every record on the subform has
also a checkbox. If i click the checkbox and then call the public
delete-function by pressing the delete button of that record, Access tells
me that only a requery can be done afther the current field is saved. I
don't understant what's happening. Can anyone help me to solve this problem.
This is my deleting code:

Public Function WisAktieHand(AktieID As Long, Volgnummer As Long, AktieDate
As Date, AktieWhere As String, lblAktietekst As String, chkAktie As Boolean)

Dim dbs As Database
Dim rst As Recordset
Dim ctl As Control

Set ctl = Forms!frmplanning!frmPlanItem

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblRappel")

rst.FindFirst "AktieID =" & AktieID

With rst
.Delete
.Close
End With

ctl.Requery

End Function



Thank for any help, Edwin Kooijman

 

[Guest]

Hi Edwin

if you just want to delete record in subform, would it not be easier (also cheaper in terms of processing) to use the code that is created by Command Button Wizard (to do Record Delete) instead of calling function that opens a recordset, run Findfirst, do Requery?. The code looks like this (Access 2000)

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer7
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer7

HT

----- Codebuster wrote: ----

Subform.Requery-problem is killing me

In Access 97 i have a form with a subform on it. This subform is
continuous form. Every record on this continuous form has a button do delet
that specific record. This operation is done by calling a public functio
which opens the underlaying table of the subform (with the DAO methode) an
delete the specific record. After this deletion i want the subform only t
show the existing records in de table, so a command a requery of th
subform. If i open de main form and make no changes to the records in th
subform this operation works fine, but.....every record on the subform ha
also a checkbox. If i click the checkbox and then call the publi
delete-function by pressing the delete button of that record, Access tell
me that only a requery can be done afther the current field is saved.
don't understant what's happening. Can anyone help me to solve this problem
This is my deleting code

Public Function WisAktieHand(AktieID As Long, Volgnummer As Long, AktieDat
As Date, AktieWhere As String, lblAktietekst As String, chkAktie As Boolean

Dim dbs As Databas
Dim rst As Recordse
Dim ctl As Contro

Set ctl = Forms!frmplanning!frmPlanIte

Set dbs = CurrentDb(
Set rst = dbs.OpenRecordset("tblRappel"

rst.FindFirst "AktieID =" & AktieI

With rs
.Delet
.Clos
End Wit

ctl.Requer

End Functio



Thank for any help, Edwin Kooijma

 

[Dirk Goldgar]

Subform.Requery-problem is killing me.

In Access 97 i have a form with a subform on it. This subform is a
continuous form. Every record on this continuous form has a button do
delete that specific record. This operation is done by calling a
public function which opens the underlaying table of the subform
(with the DAO methode) and delete the specific record. After this
deletion i want the subform only to show the existing records in de
table, so a command a requery of the subform. If i open de main form
and make no changes to the records in the subform this operation
works fine, but.....every record on the subform has also a checkbox.
If i click the checkbox and then call the public delete-function by
pressing the delete button of that record, Access tells me that only
a requery can be done afther the current field is saved. I don't
understant what's happening. Can anyone help me to solve this
problem. This is my deleting code:

Public Function WisAktieHand(AktieID As Long, Volgnummer As Long,
AktieDate As Date, AktieWhere As String, lblAktietekst As String,
chkAktie As Boolean)

Dim dbs As Database
Dim rst As Recordset
Dim ctl As Control

Set ctl = Forms!frmplanning!frmPlanItem

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblRappel")

rst.FindFirst "AktieID =" & AktieID

With rst
.Delete
.Close
End With

ctl.Requery

End Function



Thank for any help, Edwin Kooijman

If the current subform record is dirty -- that is, if it has been
modified but not saved -- then you can't delete it. So you must either
Undo the changes to the record or save them before deleting that record.
I think I might do it like this:

Set ctl = Forms!frmplanning!frmPlanItem

With ctl.Form
If .Dirty Then .Undo
End With

CurrentDb.Execute _
"DELETE FROM tblRappel WHERE AktieID = " & AktieID, _
dbFailOnError

ctl.Requery