Opening a form filtering out specific records based on another form

[rlumich]

I have a table named "cases" that stores a number of cases that need to be viewed by a list of users, the table contains an AutoNumber field named "CaseNo"
A separate table named "caseschecklist" records which users have completed certain cases and contains the field "StaffID" (identifies each user by an assigned number which is also stored in the global variable "currentStaffID"), "CaseNo" which essentially gets populated with a record everytime a user looks at a case. Another Field on that table "Complete" is a true/false field that becomes "True" when the user completes a case.
I would like to open a form that will open the table "cases" for a certain user but only select their incomplete cases - i.e. use the table "cases" only including the records where "CaseNo" has no matches to "CaseNo" in the table "caseschecklist" where for that "StaffID", "Complete" is true.
Hope this makes sense and I appreciate any help.

[TG_W]

Question while I sort through some of my databases to see if I have a code you could use - do you really need 2 tables for "cases" and "caseschecklist"? All of the data described in your post seems like it could all be in one table. If it cannot, then I understand. It is just a suggestion and would make the code easier to write.

[rlumich]

I think I do, because in the first table ("cases") each case is a unique record with a long case attached to it that includes attachments and in the second table only the identifier "CaseNo" is replicated for each user that has seen it.

[TG_W]

For users that have seen a case or are assigned to a case? You could add a column for whom the case is assigned to in the main cases table rather than tracking when a member of the staff views it otherwise you could end up with multiple cases coming up for multiple users and have 2 or more people working the same case at the same time.

[rlumich]

each user will ultimately have to view and complete every case so cases are not assigned to anyone in particular. whenever a user opens up a particular case for the first time a record is added to the "caseschecklist" table so i know that they've seen it. when they click on a completed button, it will mark the "Complete" field true for the record in "caseschecklist" with the user's "StaffID" and for that "CaseNo." Thanks

[TG_W]

Ok, that helps to understand your database a bit more, but are you wanting opened cases but not complete or cases not viewed/not opened? Cases not viewed/not opened would require a link between the two tables where incomplete cases would only require "caseschecklist" according to my understanding of the information you provided.

[TG_W]

Sorry to ask so many questions about your problem, but the two different filters are VERY different. One would be a really long explanation on how to work it out properly (not opened/not viewed) and the other is fairly short (cases opened but not complete), at least as far as I can see.

[rlumich]

no problem with the questions - thank you for your help. I actually listened closer to what you said earlier about the need for another table and found that you were correct, it is much easier with keeping it all on one table. I made a multi-value field call "CompletedBy" that stores the user ID numbers of all users that completed that case. That made it much easier to bring up the complete or incomplete cases for each user.

Unfortunately, I have come across one more difficulty now.
When I open a particular record on the form, I want to display certain things if this case has been completed by the current user.
I tried the statement:

If Me.CompletedBy = CurrentStaffID Then

hoping that this would basically check if the multivalue field "CompletedBy" contained as one of its values "CurrentStaffID" for the current record it would execute the If statement. This obviously did not work. Any idea how I can do this?

[TG_W]

If the field is multi-value, wildcards would be needed. Is it possible for you to post the whole code so I can test it? Also, what are you trying to display?

[rlumich]

This is the code for the 'On Current' Property of the Form:
Right now, all I'm having it do is if the case presented in this record has been completed by the current user a picture of a check mark (named "CheckMarkCompleted") will become visible.
The way it knows the user has completed this case is his ID number would be one of the values in the "CompletedBy" multi-value field for this record, his ID number is also concurrently stored as the global variable "CurrentStaffID"

Private Sub Form_Current()
If Me.CompletedBy.Value = CurrentStaffID Then
Me.CheckMarkCompleted.Visible = True
Else
Me.CheckMarkCompleted.Visible = False
End If
End Sub

Thank you

[TG_W]

Are you selecting the case# from a form and having it show on a subform for the user?

[rlumich]

The case number is on the same table (each record is a different case with a unique case number). So, whatever record is being viewed refers to that particular case. The user can navigate through cases by moving to different records. No subforms are being used for this part. The actual case number is stored on the same table in a field called "CaseNo" but is not being displayed on the form.

[TG_W]

Is it possible for you to upload the database?

[rlumich]

I would rather not upload it for company privacy reasons, is it possible to answer this without the upload? Essentially I am not sure how to reference or search through a multi-value field through code.

[TG_W]

It is, but it just might be easier to answer it. Let me try playing with the wild cards some. It might take a little bit since I am pretty busy today.