Deleting All Records From A SubForm

T

Tru

Here is what I am trying to do (below is the code)
I have a Option Group with two choices "yes and no" that is associated with
a subform. If "yes" is selected then the subform is enabled and the user can
enter records into the subform. If "no" is selected the subform continues to
be disabled.

The problem is when the user have entered records into the subform and now
decides to select "no". When the users selects "no" I prompt them with a
message box explaining to them that if they continue all the records will be
deleted in the subform.

I think the problem is that I am trying to open a recordset that is already
opened by the subform and this is the error message, "Error 13".

I appreciate the help.


Dim Cancel As Integer
Dim MyDb As Database, MySet As Recordset

Set MyDb = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDb.OpenRecordset("fqryCYFIHSPFactors")

If optSafetyFactors.Value = 1 Then
subSPFactors.Enabled = True
Else
Response = MsgBox("[Safety Concerns]" & NewLine _
& "Changing the above field will result in deleting all of" & NewLine _
& "the safety factors, do you wish to continue?", vbYesNo +
vbExclamation, "Trubase")
If Response = vbNo Then
Me.optSafetyFactors.Value = 1
Cancel = True
Else
With MySet
If .RecordCount > 0 Then
.MoveFirst
Do
.Delete
'Without this MoveNext, ADO would continually
'try to delete the same row, the first one
.MoveNext
Loop Until .EOF
End If
End With
MySet.Close
MyDb.Close

'Need this to requery and save the record before disabling
'the SubForm
Me.Requery
subSPFactors.Enabled = False
End If
End If
 
D

Dirk Goldgar

Tru said:
Here is what I am trying to do (below is the code)
I have a Option Group with two choices "yes and no" that is
associated with a subform. If "yes" is selected then the subform is
enabled and the user can enter records into the subform. If "no" is
selected the subform continues to be disabled.

The problem is when the user have entered records into the subform
and now decides to select "no". When the users selects "no" I
prompt them with a message box explaining to them that if they
continue all the records will be deleted in the subform.

I think the problem is that I am trying to open a recordset that is
already opened by the subform and this is the error message, "Error
13".

I appreciate the help.


Dim Cancel As Integer
Dim MyDb As Database, MySet As Recordset

Set MyDb = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDb.OpenRecordset("fqryCYFIHSPFactors")

If optSafetyFactors.Value = 1 Then
subSPFactors.Enabled = True
Else
Response = MsgBox("[Safety Concerns]" & NewLine _
& "Changing the above field will result in deleting all of" &
NewLine _ & "the safety factors, do you wish to continue?",
vbYesNo +
vbExclamation, "Trubase")
If Response = vbNo Then
Me.optSafetyFactors.Value = 1
Cancel = True
Else
With MySet
If .RecordCount > 0 Then
.MoveFirst
Do
.Delete
'Without this MoveNext, ADO would continually
'try to delete the same row, the first one
.MoveNext
Loop Until .EOF
End If
End With
MySet.Close
MyDb.Close

'Need this to requery and save the record before disabling
'the SubForm
Me.Requery
subSPFactors.Enabled = False
End If
End If

Error 13 is not the standard message; it's just the error number. The
message for that number is "Type mismatch". It's probably not for the
reason you think, but rather because (I'd guess) you have a reference
set to the ADO object Library as well as the DAO object library, and
Access thinks that MySet is an ADODB Recordset, when really it's a DAO
Recordset. These are incompatible types.

If I'm right, you *could* get past this specific error by just changing
your Dim statement:

Dim MyDb As DAO.Database, MySet As DAO.Recordset

Technically, you don't need the identify MyDb as a DAO Database, because
ADO doesn't have a Database object. However, it's good practice.

However, I think your code is more complicated than it needs to be.
Remember, the subform doesn't really hold records; it just displays
them. You can run a delete query to delete the records from the table
where they are actually stored, and it will be both simpler and quicker
than doing it via a recordset -- even if you used the subform's own
recordset.

Is the query, "fqryCYFIHSPFactors", designed so that it returns the same
records as the subform, filtered by the same Link Master Fields as the
subform is filtered by? If so, you can probably delete all the records
with one statement, like this:

DBEngine.Workspaces(0).Databases(0).Execute _
"DELETE * FROM fqryCYFIHSPFactors", _
dbFailOnError

You wouldn't need the database object or the recordset object or any
looping.

Be careful, though -- make sure first that the query does in fact
include only the records currently displayed on the subform, bearing in
mind the subform's linkage to the current record on the main form.
Also, it may well be that a simpler delete query than that could be
written.
 
T

Tru

Dirk, You The Man baby! Thanx for pointing out my mistake.
--
Thanx!


Dirk Goldgar said:
Tru said:
Here is what I am trying to do (below is the code)
I have a Option Group with two choices "yes and no" that is
associated with a subform. If "yes" is selected then the subform is
enabled and the user can enter records into the subform. If "no" is
selected the subform continues to be disabled.

The problem is when the user have entered records into the subform
and now decides to select "no". When the users selects "no" I
prompt them with a message box explaining to them that if they
continue all the records will be deleted in the subform.

I think the problem is that I am trying to open a recordset that is
already opened by the subform and this is the error message, "Error
13".

I appreciate the help.


Dim Cancel As Integer
Dim MyDb As Database, MySet As Recordset

Set MyDb = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDb.OpenRecordset("fqryCYFIHSPFactors")

If optSafetyFactors.Value = 1 Then
subSPFactors.Enabled = True
Else
Response = MsgBox("[Safety Concerns]" & NewLine _
& "Changing the above field will result in deleting all of" &
NewLine _ & "the safety factors, do you wish to continue?",
vbYesNo +
vbExclamation, "Trubase")
If Response = vbNo Then
Me.optSafetyFactors.Value = 1
Cancel = True
Else
With MySet
If .RecordCount > 0 Then
.MoveFirst
Do
.Delete
'Without this MoveNext, ADO would continually
'try to delete the same row, the first one
.MoveNext
Loop Until .EOF
End If
End With
MySet.Close
MyDb.Close

'Need this to requery and save the record before disabling
'the SubForm
Me.Requery
subSPFactors.Enabled = False
End If
End If

Error 13 is not the standard message; it's just the error number. The
message for that number is "Type mismatch". It's probably not for the
reason you think, but rather because (I'd guess) you have a reference
set to the ADO object Library as well as the DAO object library, and
Access thinks that MySet is an ADODB Recordset, when really it's a DAO
Recordset. These are incompatible types.

If I'm right, you *could* get past this specific error by just changing
your Dim statement:

Dim MyDb As DAO.Database, MySet As DAO.Recordset

Technically, you don't need the identify MyDb as a DAO Database, because
ADO doesn't have a Database object. However, it's good practice.

However, I think your code is more complicated than it needs to be.
Remember, the subform doesn't really hold records; it just displays
them. You can run a delete query to delete the records from the table
where they are actually stored, and it will be both simpler and quicker
than doing it via a recordset -- even if you used the subform's own
recordset.

Is the query, "fqryCYFIHSPFactors", designed so that it returns the same
records as the subform, filtered by the same Link Master Fields as the
subform is filtered by? If so, you can probably delete all the records
with one statement, like this:

DBEngine.Workspaces(0).Databases(0).Execute _
"DELETE * FROM fqryCYFIHSPFactors", _
dbFailOnError

You wouldn't need the database object or the recordset object or any
looping.

Be careful, though -- make sure first that the query does in fact
include only the records currently displayed on the subform, bearing in
mind the subform's linkage to the current record on the main form.
Also, it may well be that a simpler delete query than that could be
written.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top