Trying to close a form when empty

[DawnTreader]

Hello All

i am working on a form that pops up when a user logs in and has at least 1
of any number of 5 types of items. for instance the item types are issues,
service, warranty, order, returns. if a user has created any 1 of these items
and it is older than 14 days and has not been "finished" a form pops up with
5 list boxes, 1 list box for each type, showing the items that arent finished.

when they double click the item in the list it opens the item in its form so
they can finish it. when they do so and close the item form it refreshes the
pop up so that the item no longer appears on the list.

i have it set up so that if there are no items to worry about the form
doesnt pop up. i also have it set up so they can close the pop up. but the
one thing i would like to have happen is when they "empty" each list of every
item the whole pop up should disappear.

everything but the popup disappearing works.

here is the code for all the stuff that does work:

on the popup form called frmItemAttentionNeededByEmployee

Private Sub Form_Load()
'Check to see if there is any items that are more than 14 days old by
the current employee that are unfinished.
If IANValidation() = False Then
MsgBox "empty", vbOKOnly, "empty"
DoCmd.Close acForm, "frmItemAttentionNeededByEmployee"
End If
End Sub

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
'check to see if there are any items that are more than 14 days old by
the current employee that are unfinished before we allow them to leave the
form.
If IANValidation() = True Then
If MsgBox("Are You Sure You Want To Close?", vbYesNo, "Items Still
Need Your Attention") = vbYes Then
DoCmd.Close acForm, "frmItemAttentionNeededByEmployee"
End If
End If

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox err.Description
Resume Exit_cmdClose_Click

End Sub

Private Function IANValidation() As Boolean
Dim ANIssue As Boolean
Dim ANServiceReport As Boolean
Dim ANWarrantyClaim As Boolean
Dim ANPartOrder As Boolean
Dim ANPartReturn As Boolean

ANIssue = False
ANServiceReport = False
ANWarrantyClaim = False
ANPartOrder = False
ANPartReturn = False

If Me.lstANIssues.ListCount >= 1 Then ANIssue = True ' check the listbox
containing the Issues
If Me.lstANServiceReports.ListCount >= 1 Then ANServiceReport = True '
check the listbox containing the Service Reports
If Me.lstANWarrantyClaims.ListCount >= 1 Then ANWarrantyClaim = True '
check the listbox containing the warranties
If Me.lstANPartOrders.ListCount >= 1 Then ANPartOrder = True ' check the
listbox containing the Part Orders
If Me.lstANPartReturns.ListCount >= 1 Then ANPartReturn = True ' check
the listbox containing the Part Returns

' if even one of the above is true, open the form by sending a true,
otherwise close the form by sending a false
If ANIssue = True Or ANServiceReport = True Or ANWarrantyClaim = True Or
ANPartOrder = True Or ANPartReturn = True Then
IANValidation = True
Else
IANValidation = False
End If
End Function

like i say everything here works fine. what i want to do is at some point in
the process of "finishing" items there might be a point where there are no
more. if that is the case i would like to have this form close itself. i have
tried putting the same code as the load function into the current, activate
and after update event of the form, as well as putting it into the after
update of each list. i figured that it should work somewhere.

when a user opens an unfinished item and finishes it, the form for the item
refreshes the lists for all items. this is a public sub that i call from each
of the item forms when they close. that code looks like this:


Public Sub refresh_lists()
Forms!frmManageAssets.frmSubAssetInfo.Requery
Forms!frmManageAssets.lstWarranty.Requery
Forms!frmManageAssets.lstServiceReport.Requery
Forms!frmManageAssets.lstIssue.Requery
Forms!frmManageAssets.lstPartOrders.Requery
Forms!frmManageAssets.lstPartReturn.Requery

'if the attention needed form is open, refresh those lists too
If fIsLoaded("frmItemAttentionNeededByEmployee") = True Then
Forms!frmItemAttentionNeededByEmployee.lstANIssues.Requery
Forms!frmItemAttentionNeededByEmployee.lstANServiceReports.Requery
Forms!frmItemAttentionNeededByEmployee.lstANWarrantyClaims.Requery
Forms!frmItemAttentionNeededByEmployee.lstANPartOrders.Requery
Forms!frmItemAttentionNeededByEmployee.lstANPartReturns.Requery
End If

End Sub

i have tried looking at documentation in the help on the order of events,
but nothing seems to fit the bill. so basically i need someone to tell me,
where do i put this code:

'Check to see if there is any items that are more than 14 days old by
the current employee that are unfinished.
If IANValidation() = False Then
MsgBox "empty", vbOKOnly, "empty"
DoCmd.Close acForm, "frmItemAttentionNeededByEmployee"
End If

so that my form will see that the user has finished all open items and there
is no longer any data in the list boxes and close this form?

 

[Jeanette Cunningham]

Hi Dawn,
the general idea to use is to check to see if the lists are empty.
Every time one of the lists is updated, run some code that checks the count
of records in the row source for each list.
If your code finds that there are no records showing in any of the lists,
then you can use code to close the form.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[DawnTreader]

Hello Jeanette

that is exactly what the Function i created does.

IANValidation looks at each list and if they are all empty it returns false.

i am having trouble figuring out what event to put it in. i have another
piece of code which i use to refresh a few different lists:

Public Sub refresh_lists()
Forms!frmManageAssets.frmSubAssetInfo.Requery
Forms!frmManageAssets.lstWarranty.Requery
Forms!frmManageAssets.lstServiceReport.Requery
Forms!frmManageAssets.lstIssue.Requery
Forms!frmManageAssets.lstPartOrders.Requery
Forms!frmManageAssets.lstPartReturn.Requery

'if the attention needed is open, refresh those lists too
If fIsLoaded("frmItemAttentionNeededByEmployee") = True Then
Forms!frmItemAttentionNeededByEmployee.lstANIssues.Requery
Forms!frmItemAttentionNeededByEmployee.lstANServiceReports.Requery
Forms!frmItemAttentionNeededByEmployee.lstANWarrantyClaims.Requery
Forms!frmItemAttentionNeededByEmployee.lstANPartOrders.Requery
Forms!frmItemAttentionNeededByEmployee.lstANPartReturns.Requery
End If

End Sub

i tried putting the check in the current, dirty, activate events of the form
and the focus, change and the after update of the lists themselves. nothing
worked.

any ideas about where to put the check?

 

[Jeanette Cunningham]

Dawn,
Thanks for posting your code.
The code needs to go on the after update event of every list - every combo
or listbox that has a list.
Private Sub Me.List1_AfterUpdate()
Call refresh_lists
End Sub


The location of that public sub is also an issue.
If you are using frmManageAssets and the lists are on frmManageAssets,
I would try to code it like this:

Public Sub refresh_lists()
Me.frmSubAssetInfo.Requery
Me.lstWarranty.Requery
Me.lstServiceReport.Requery
Me.lstIssue.Requery
Me.lstPartOrders.Requery
Me.lstPartReturn.Requery

'if the attention needed is open, refresh those lists too
If fIsLoaded("frmItemAttentionNeededByEmployee") = True Then
With Forms!frmItemAttentionNeededByEmployee
.lstANIssues.Requery
.lstANServiceReports.Requery
.lstANWarrantyClaims.Requery
.lstANPartOrders.Requery
.lstANPartReturns.Requery
End With
End If

End Sub

Then use code like this to call that sub, change it to suit each control
that needs to call the sub.

Private Sub Me.List1_AfterUpdate()
Call refresh_lists
End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jeanette Cunningham]

On second thought (second thoughts are *always* after I have just clicked
the send button).


Probably the easiest way is to use a query or series of queries to check if
there are any records for those combo, listbox, subform controls.
You would use DCount on each query you need.
Something like this (untested air code)

Dim lngMyListCount as Long

lngMyListCount = DCount("*", "NameOf1stQuery") + DCount("*",
"NameOf2ndQuery") + etc, etc,

You may be able to create a single query that would check for the info you
need instead of using the idea above.
It all depends on your database and your data.



You can also get this from the open forms using the ideas below.

You need to get the no. of records in each list.
Instead of requerying each list, get it's list count.

For a combo you can use:
Me.NameOfCombo.ListCount

For a listbox
Me.NameOfListBox.ListCount

For a subform
Me.NameOfSubformControl.Form.RecordsetClone.RecordCount



Set up a variable to hold the value of the listcount like this:
Dim lngTheListCount as Long


With Forms!frmManageAssets.frmSubAssetInfo
lngTheListCount = .lstWarranty.ListCount + .lstServiceReport.ListCount
+ 'etc, etc
'for all the controls on that form
End With

Repeat that for frmItemAttentionNeededByEmployee

With Forms!frmItemAttentionNeededByEmployee
lngTheListCount = lngTheListCount + lstANIssues.ListCount + etc,
etc 'for all the controls on that form
End With

If the list count of every combo, list and subform is 0 then you know there
are no more lists and you can close that form.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia