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)
