Check if Subform is empty when AllowAdditions = No

  • Thread starter Thread starter Tony Vrolyk
  • Start date Start date
T

Tony Vrolyk

I am trying to refer to a subform from a module and want to check if it is
empty. The subform has the following properties
AllowFitlers = No
AllowEdits = Yes
AllowDeletions = No
AllowAdditions = No
DataEntry = No

New records and record deletions are handled by shortcut menus. Of course if
there is no record I don't want the delete option to do anything at all. For
instance I use the code below to delete a record. However the "If IsNull..."
statement always resolves to True even when no records are displayed. I then
get a Run-time error "2427 - You entered an expresion that has no value". I
could use error handling to handle Error 2427 but I would prefer that no
prompt occur when there is no record.

For some reason I think this should be easy but it is eluding me

Thanks
Tony


**code start**
Function fPlans_Delete()
Dim db As DAO.Database, rst As Recordset

If Not IsNull(Forms!Clients!Subform.Form!PlanIDNumber) Then
If MsgBox("Permanently Delete Plan?", vbQuestion + vbYesNo +
vbDefaultButton1, "Confirm") = vbNo Then
DoCmd.CancelEvent
Else
Set db = CurrentDb
Set rst = db.OpenRecordset("Plans", dbOpenDynaset)

rst.FindFirst "[PlanIDNumber] ='" &
Forms!Clients.Subform!PlanIDNumber & "'"
If rst.NoMatch Then
MsgBox "No match found", vbOKOnly
Else
rst.Delete
End If

rst.Close
Forms!Clients!Subform.Requery

End If
End If
End Function

**code end**
 
Why not move the Confirm Delete to after you have checked the Record
On error goto ProcErr
SetDB
SetRSt
FindRecord
If Found then
If ConfirmDelete then DeleteRecord
Endif
ProcExit:
CloseRST
RemoveObjs
ExitSub
ProcErr:
Select case err.num
Case 2427
'Donothing
Case Else
MsgBOx Error
end Select
Resume ProcExit
end sub
 
Good idea but the error occurs on the rst.findfirst line so it can't even
perform the find since I am referring to a control that has no value.

Another thought of mine was to disable the Delete menu item OnCurrent when
the form has not records but I am running into the same issue. Aparently it
is as if there is no recordset when there are 0 records and AllowAditions is
false.

I am trying to avoid doing a dlookup since this is a more costly operation.
I figured that the form already has the recordset open whey go search
another? The backend is stored on a server and some PCs are quite slow so
trying to get it to run as quicly as possible.



Greg Kraushaar said:
Why not move the Confirm Delete to after you have checked the Record
On error goto ProcErr
SetDB
SetRSt
FindRecord
If Found then
If ConfirmDelete then DeleteRecord
Endif
ProcExit:
CloseRST
RemoveObjs
ExitSub
ProcErr:
Select case err.num
Case 2427
'Donothing
Case Else
MsgBOx Error
end Select
Resume ProcExit
end sub


I am trying to refer to a subform from a module and want to check if it is
empty. The subform has the following properties
AllowFitlers = No
AllowEdits = Yes
AllowDeletions = No
AllowAdditions = No
DataEntry = No

New records and record deletions are handled by shortcut menus. Of course if
there is no record I don't want the delete option to do anything at all. For
instance I use the code below to delete a record. However the "If IsNull..."
statement always resolves to True even when no records are displayed. I then
get a Run-time error "2427 - You entered an expresion that has no value". I
could use error handling to handle Error 2427 but I would prefer that no
prompt occur when there is no record.

For some reason I think this should be easy but it is eluding me

Thanks
Tony


**code start**
Function fPlans_Delete()
Dim db As DAO.Database, rst As Recordset

If Not IsNull(Forms!Clients!Subform.Form!PlanIDNumber) Then
If MsgBox("Permanently Delete Plan?", vbQuestion + vbYesNo +
vbDefaultButton1, "Confirm") = vbNo Then
DoCmd.CancelEvent
Else
Set db = CurrentDb
Set rst = db.OpenRecordset("Plans", dbOpenDynaset)

rst.FindFirst "[PlanIDNumber] ='" &
Forms!Clients.Subform!PlanIDNumber & "'"
If rst.NoMatch Then
MsgBox "No match found", vbOKOnly
Else
rst.Delete
End If

rst.Close
Forms!Clients!Subform.Requery

End If
End If
End Function

**code end**
Click to expand...
Click to expand...
 
Good idea but the error occurs on the rst.findfirst line so it can't even
perform the find since I am referring to a control that has no value.

Another thought of mine was to disable the Delete menu item OnCurrent when
the form has not records but I am running into the same issue. Aparently it
is as if there is no recordset when there are 0 records and AllowAditions is
false.

I am trying to avoid doing a dlookup since this is a more costly operation.
I figured that the form already has the recordset open whey go search
another? The backend is stored on a server and some PCs are quite slow so
trying to get it to run as quicly as possible.
Click to expand...

It sounds as though you need to get the subform's recordcount prior to making
any reference to a field or control. This would be the syntax:

'***
If Forms!Clients!Subform.Form.RecordsetClone.RecordCount > 0 Then
'The subform's recordset contains one or more records
Else
'The subform's recordset contains no records
End If
'***

It is assumed that "Subform" is the name of the subform control on the main
form, not the name of the subform (although both may share the same name).
 
Ahh, but the error in FindFirst will be trapped by the error handler,
and the rest of the code won't run

And as we are expecting Err = 2427 if we are at a new record, then
that error is ignored, and it will appear to the user as if Clicking
the delete button does nothing.
(You could always use a static variable to track how often a user does
this and pop up a "will you stop doing that!" message if they retry
the same thing several times)

(Finally got my sig up and running on my new NewsReader)

Regards Greg Kraushaar
Wentworth Falls Australia
(Do not email - the reply address is a Spam spoofer)
(If you really must, remove all UCase and numbers)

Good idea but the error occurs on the rst.findfirst line so it can't even
perform the find since I am referring to a control that has no value.

Another thought of mine was to disable the Delete menu item OnCurrent when
the form has not records but I am running into the same issue. Aparently it
is as if there is no recordset when there are 0 records and AllowAditions is
false.

I am trying to avoid doing a dlookup since this is a more costly operation.
I figured that the form already has the recordset open whey go search
another? The backend is stored on a server and some PCs are quite slow so
trying to get it to run as quicly as possible.



Greg Kraushaar said:
Why not move the Confirm Delete to after you have checked the Record
On error goto ProcErr
SetDB
SetRSt
FindRecord
If Found then
If ConfirmDelete then DeleteRecord
Endif
ProcExit:
CloseRST
RemoveObjs
ExitSub
ProcErr:
Select case err.num
Case 2427
'Donothing
Case Else
MsgBOx Error
end Select
Resume ProcExit
end sub


I am trying to refer to a subform from a module and want to check if it is
empty. The subform has the following properties
AllowFitlers = No
AllowEdits = Yes
AllowDeletions = No
AllowAdditions = No
DataEntry = No

New records and record deletions are handled by shortcut menus. Of course if
there is no record I don't want the delete option to do anything at all. For
instance I use the code below to delete a record. However the "If IsNull..."
statement always resolves to True even when no records are displayed. I then
get a Run-time error "2427 - You entered an expresion that has no value". I
could use error handling to handle Error 2427 but I would prefer that no
prompt occur when there is no record.

For some reason I think this should be easy but it is eluding me

Thanks
Tony


**code start**
Function fPlans_Delete()
Dim db As DAO.Database, rst As Recordset

If Not IsNull(Forms!Clients!Subform.Form!PlanIDNumber) Then
If MsgBox("Permanently Delete Plan?", vbQuestion + vbYesNo +
vbDefaultButton1, "Confirm") = vbNo Then
DoCmd.CancelEvent
Else
Set db = CurrentDb
Set rst = db.OpenRecordset("Plans", dbOpenDynaset)

rst.FindFirst "[PlanIDNumber] ='" &
Forms!Clients.Subform!PlanIDNumber & "'"
If rst.NoMatch Then
MsgBox "No match found", vbOKOnly
Else
rst.Delete
End If

rst.Close
Forms!Clients!Subform.Requery

End If
End If
End Function

**code end**
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your suggestion (and Bruce's) - they both would work fine. I had
tried something like Bruce's but must have had the syntax wrong. I just
tried it again and got it to work. And Greg, yours would also work fine.
When I replied to your last suggestion I had the error handling commented
out so I would get the debug prompt and it just didn't click.

Anyway what I finally did was to create a small function that would do a
DCount to see if there are any subrecords and if not, disable the approriate
shortcut menu item. I call the function on open of the subform and on the
New and Delete functions. Since it is not being called too often there
really is no speed problem. I think this solution looks more professional
anyway since it mimics how most modern programs handle their menus.

For anyone who might be interested or who might come across this post in the
future here is the function I used. Watch for word wrap.

**code start**
Function fPlans_ButtonsEnable()
Dim intPlanCount As Integer

intPlanCount = DCount("[PlanIDNumber]", "Plans", "ClientID = " &
Forms!Clients.ClientID & "")
If intPlanCount = 0 Then
CommandBars("Clients_Plans_Subform").Controls("Delete").Enabled =
False
CommandBars("Clients_Plans_Subform").Controls("Export").Enabled =
False
Else
CommandBars("Clients_Plans_Subform").Controls("Delete").Enabled =
True
CommandBars("Clients_Plans_Subform").Controls("Export").Enabled =
True
End If


End Function
**code end**



Thanks
Tony



Greg Kraushaar said:
Ahh, but the error in FindFirst will be trapped by the error handler,
and the rest of the code won't run

And as we are expecting Err = 2427 if we are at a new record, then
that error is ignored, and it will appear to the user as if Clicking
the delete button does nothing.
(You could always use a static variable to track how often a user does
this and pop up a "will you stop doing that!" message if they retry
the same thing several times)

(Finally got my sig up and running on my new NewsReader)

Regards Greg Kraushaar
Wentworth Falls Australia
(Do not email - the reply address is a Spam spoofer)
(If you really must, remove all UCase and numbers)

Good idea but the error occurs on the rst.findfirst line so it can't even
perform the find since I am referring to a control that has no value.

Another thought of mine was to disable the Delete menu item OnCurrent when
the form has not records but I am running into the same issue. Aparently it
is as if there is no recordset when there are 0 records and AllowAditions is
false.

I am trying to avoid doing a dlookup since this is a more costly operation.
I figured that the form already has the recordset open whey go search
another? The backend is stored on a server and some PCs are quite slow so
trying to get it to run as quicly as possible.



Greg Kraushaar said:
Why not move the Confirm Delete to after you have checked the Record
On error goto ProcErr
SetDB
SetRSt
FindRecord
If Found then
If ConfirmDelete then DeleteRecord
Endif
ProcExit:
CloseRST
RemoveObjs
ExitSub
ProcErr:
Select case err.num
Case 2427
'Donothing
Case Else
MsgBOx Error
end Select
Resume ProcExit
end sub


On Mon, 1 Dec 2003 14:25:25 -0600, "Tony Vrolyk"

I am trying to refer to a subform from a module and want to check if
Click to expand...
it
is
empty. The subform has the following properties
AllowFitlers = No
AllowEdits = Yes
AllowDeletions = No
AllowAdditions = No
DataEntry = No

New records and record deletions are handled by shortcut menus. Of
Click to expand...
course
if
there is no record I don't want the delete option to do anything at
Click to expand...
all.
For
instance I use the code below to delete a record. However the "If IsNull..."
statement always resolves to True even when no records are displayed.
Click to expand...
I
then
get a Run-time error "2427 - You entered an expresion that has no
Click to expand...
value".
I
could use error handling to handle Error 2427 but I would prefer that no
prompt occur when there is no record.

For some reason I think this should be easy but it is eluding me

Thanks
Tony


**code start**
Function fPlans_Delete()
Dim db As DAO.Database, rst As Recordset

If Not IsNull(Forms!Clients!Subform.Form!PlanIDNumber) Then
If MsgBox("Permanently Delete Plan?", vbQuestion + vbYesNo +
vbDefaultButton1, "Confirm") = vbNo Then
DoCmd.CancelEvent
Else
Set db = CurrentDb
Set rst = db.OpenRecordset("Plans", dbOpenDynaset)

rst.FindFirst "[PlanIDNumber] ='" &
Forms!Clients.Subform!PlanIDNumber & "'"
If rst.NoMatch Then
MsgBox "No match found", vbOKOnly
Else
rst.Delete
End If

rst.Close
Forms!Clients!Subform.Requery

End If
End If
End Function

**code end**
Click to expand...
Click to expand...
Click to expand...
 
Actually Bruce, I ended up using a variation of my solution and yours. The
DCount was still too costly so I used your method in combination with mine

If Forms!Plans!Subform.Form.RecordsetClone.RecordCount = 0 Then
CommandBars("Menu").Controls("Control").Enabled = False
....

Thanks again
Tony

Tony Vrolyk said:
Thanks for your suggestion (and Bruce's) - they both would work fine. I had
tried something like Bruce's but must have had the syntax wrong. I just
tried it again and got it to work. And Greg, yours would also work fine.
When I replied to your last suggestion I had the error handling commented
out so I would get the debug prompt and it just didn't click.

Anyway what I finally did was to create a small function that would do a
DCount to see if there are any subrecords and if not, disable the approriate
shortcut menu item. I call the function on open of the subform and on the
New and Delete functions. Since it is not being called too often there
really is no speed problem. I think this solution looks more professional
anyway since it mimics how most modern programs handle their menus.

For anyone who might be interested or who might come across this post in the
future here is the function I used. Watch for word wrap.

**code start**
Function fPlans_ButtonsEnable()
Dim intPlanCount As Integer

intPlanCount = DCount("[PlanIDNumber]", "Plans", "ClientID = " &
Forms!Clients.ClientID & "")
If intPlanCount = 0 Then
CommandBars("Clients_Plans_Subform").Controls("Delete").Enabled =
False
CommandBars("Clients_Plans_Subform").Controls("Export").Enabled =
False
Else
CommandBars("Clients_Plans_Subform").Controls("Delete").Enabled =
True
CommandBars("Clients_Plans_Subform").Controls("Export").Enabled =
True
End If


End Function
**code end**



Thanks
Tony



Greg Kraushaar said:
Ahh, but the error in FindFirst will be trapped by the error handler,
and the rest of the code won't run

And as we are expecting Err = 2427 if we are at a new record, then
that error is ignored, and it will appear to the user as if Clicking
the delete button does nothing.
(You could always use a static variable to track how often a user does
this and pop up a "will you stop doing that!" message if they retry
the same thing several times)

(Finally got my sig up and running on my new NewsReader)

Regards Greg Kraushaar
Wentworth Falls Australia
(Do not email - the reply address is a Spam spoofer)
(If you really must, remove all UCase and numbers)
Click to expand...
Aparently
it
Click to expand...
AllowAditions
is
Click to expand...
displayed.
I
Click to expand...
that
no
prompt occur when there is no record.

For some reason I think this should be easy but it is eluding me

Thanks
Tony


**code start**
Function fPlans_Delete()
Dim db As DAO.Database, rst As Recordset

If Not IsNull(Forms!Clients!Subform.Form!PlanIDNumber) Then
If MsgBox("Permanently Delete Plan?", vbQuestion + vbYesNo +
vbDefaultButton1, "Confirm") = vbNo Then
DoCmd.CancelEvent
Else
Set db = CurrentDb
Set rst = db.OpenRecordset("Plans", dbOpenDynaset)

rst.FindFirst "[PlanIDNumber] ='" &
Forms!Clients.Subform!PlanIDNumber & "'"
If rst.NoMatch Then
MsgBox "No match found", vbOKOnly
Else
rst.Delete
End If

rst.Close
Forms!Clients!Subform.Requery

End If
End If
End Function

**code end**
Click to expand...
Click to expand...
Click to expand...
 
Actually Bruce, I ended up using a variation of my solution and yours. The
DCount was still too costly so I used your method in combination with mine

If Forms!Plans!Subform.Form.RecordsetClone.RecordCount = 0 Then
CommandBars("Menu").Controls("Control").Enabled = False
...

Thanks again
Click to expand...

Glad I could help.

:-)
 
Back
Top