Continuous form checkbox problem 2

[MHadden]

I currently have a form that opens when I open the database. This form is set to continuous, it has reminders that show up, with a task list of things that haven't been done yet. There is a checkbox & a date field. I need the form to stay open until either the checkbox is ticked, showing that the task is completed, or the date is changed to re-schedule it. The form is based on a query that causes it to only show un finished tasks.

The code I tried on the close button is below:

Private Sub cmd_Close_Reminders_Click()
On Error GoTo Err_cmd_Close_Reminders_Click

If [Complete] = -1 Or [ExpectedCompletionDate] > Date Then

    DoCmd.Close
    
Else
End If

Exit_cmd_Close_Reminders_Click:
    Exit Sub

Err_cmd_Close_Reminders_Click:
    MsgBox Err.Description
    Resume Exit_cmd_Close_Reminders_Click
    
End Sub

This works great, if there is only one task to be done, trouble is that when there are more than one tasks open, the form will close when only one is checked, or date is updated. I need all open / un finished projects to have the completed checkbox checked, or date changed. I have tried this with a single form as well & get the same results. I have searched this forum & found a few responses that made sense, but I still haven't been able to correct this problem.

Any help is appreciated!

Thank you - Michael

MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[jbpez]

If the query which your form is based on only shows 'Complete' = False or 'ExpectedCompletionDate' <= Date, then create a recordset of the query and check if there are any records, if not then the form can be closed.

John Borges

 

[willir]

Your form is probably checking for the status of the current record. You need to check the status of all records displayed.

A simple approach would be to use DCount or DLookup.

Dim intCount as Integer

intCount = DCount("Complete", "YourTable", "Complete = " _
& "False AND ExpectedCompletionDate < " & Date())

If Not intCount then 
   DoCmd.Close
End If

Another approach that may be faster would be to use a RecordSet to run the query.

Richard

 

[MHadden]

Thanks to both for the suggestions. I'm not having any luck, but will keep trying.

THANKS!
Michael

MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[PHV]

And something like this ?
Private Sub cmd_Close_Reminders_Click()
Dim rs As DAO.Recordset
Set rs = Me.Recordset
rs.MoveFirst
While Not rs.EOF
If [Complete] = 0 And [ExpectedCompletionDate] <= Date Then
Exit Sub
End If
rs.MoveNext
WEnd
DoCmd.Close
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

Michael

Do you know what intCount is per the code I provided?

Place a STOP statement in the code...

Dim intCount as Integer

intCount = DCount("Complete", "YourTable", "Complete = " _
& "False AND ExpectedCompletionDate < " & Date())

[COLOR=blue]STOP[/color]

If Not intCount then 
   DoCmd.Close
End If

Then, when the code hits the "STOP" statement, use CTRL-G to open the immediate window / debugger, and type...

?intCount

If the intCount is zero, then the WHERE clause is worng.

..."Complete = False AND ExpectedCompletionDate < " & Date())


Can you see what is wrong?

If you get an error, what is the error.

If you do not hit the STOP statement, then the code is not executing.

Post back with the results.
Thanks

Richard

 

[MHadden]

It worked Great!!!

You may find this hard to believe, but, using the suggestions from Richard & John, I was searching this site again & ran across some code very similar to this from around October '04 & started toying around with it.

After seeing your suggestion above, I looked back at the other code & saw that you were the author of the other code as well.

THANKS TO EVERYONE for all the helpful suggestions!


-Michael

MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[MHadden]

Richard,
So sorry, I didn't see your post before replying above.
You're right - I didn't quite understand the code. It wasn't doing anything, but I didn't know about the "Stop" statement.
Anyway, I'm definitely going to try using your code as well, to see if there is any better results, But either way, I do appreciate your help. Sorry for my lack of knowledge.

Thanks, Michael

MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!