Query doesn't work to filter in form

[DeannaKate]

Hi all... I've got a single form ("Lead Data") that has Cascading Combo boxes that work perfectly, entering data into "tblLeadData":

• cboMatterTypeID
  • cboMatterID
    • cboAttyID
    • cboPlglID

Attorney & Paralegal are the people assigned to the Matter. My problem is in finding a way to allow a specific Attorney or Paralegal to filter for only his or her records. I made a query of tblLeadData that works perfectly as a query, but when I use it as a filter in an "on click" macro event, it doesn't work. I suspect it's because of the cascading combos, because I've successfully used this kind of query based macro filter in the past.

Okay, more on how it is set up. The same people are always assigned to a specific matter, so when you pull down the Atty & Plgl combo boxes, there's only one person. So it isn't a true Parent/Child relationship, but it's working. And there were two advantages of this set up over an autopopulate set up (which I considered): 1) When I change something in the reference tables (refAtty and refPlgl), it also changes in tblLeadData & 2) in case there's an exception to the usual assignment pattern, we can just leave Atty & Plgl blank and put the correct assignment in a text box called "AssignmentNotes."



So my query of tblLeadData that works, qryLeadDataAssign, uses the following fields:

• tblLeadData.AttyID
• refAtty.Atty
• tblLeadData.PlglID
• refPlgl.Plgl
• tblLeadData.AssignmentNote
• Expr1: [Atty] & " " & [Plgl] & " " & [LeadAssignmentNotes]
  • Criteria: Like "*" & [Who?] & "*"

The Join Properties in the query between tblLeadData and refAtty is set to "2: Include ALL records from 'tblLeadData' and only those records from refAtty" where the joined fields are equal." And the same for Plgl.

When I run the query, it asks me a single time, "Who?", I put in the name and it pulls up all instances of the name from any of the 3 fields. It acts as a "contains" filter, not an "equals" one.



As for my cascading combos, here are the settings

• MatterTypeID
  • Row Source: SELECT refMatterType.MatterTypeID, refMatterType.MatterType, refMatterType.[MatterType] FROM refMatterType ORDER BY refMatterType.[MatterType];
  • On Change Event:
    • Me.cboMatter.Requery
• MatterID
  • Row Source: SELECT tblMatter.MatterID, tblMatter.Matter FROM tblMatter WHERE (((tblMatter.MatterTypeID)=[Forms].[LeadData].[cboMattertype])) GROUP BY tblMatter.MatterID, tblMatter.Matter, tblMatter.Matter ORDER BY tblMatter.Matter;
  • On Change Event:
    • Me.cboAtty.Requery
    • Me.cboPlgl.Requery
• AttyID
  • RowSource: SELECT tblMatter.AttyID, refAtty.Atty FROM refAtty INNER JOIN tblMatter ON refAtty.AttyID = tblMatter.AttyID WHERE (((tblMatter.MatterID)=[Forms].[LeadData].[cboMatter])) GROUP BY tblMatter.AttyID, refAtty.Atty;
• PlglID set up along the same lines as AttyID
• Form
  • On Current Event
    • cboMatter.Requery
    • cboAtty.Requery
    • cboPlgl.Requery

I put a button on the form and put an embedded macro as an "On Click" event. The macro is an "ApplyFilter" and the filter name is qryLeadDataAssign. When I click on the button, I am asked to enter data 3 times:

1. Enter Parameter Value: Atty
2. Enter Parameter Value: Plgl
3. Enter Parameter Value: Who?

Clearly, the expression in the query doesn't function in the button. And the result, no matter what I put in, is that all of the records are still there, although the filtered button is activated.

I tried putting the expression from the query into the macro builder window, but I for sure don't know what I'm doing there and haven't been able to make it work.

Help! It's taken me so long to get where I am with this thing. The trial and error has been great for learning, but I'm my deep wells of trial and error juice have just about run dry. I need to get this thing done.

Thanks so much! and I can certainly supply more detail if needed.

DeannaKate

 

[Mihail]

Attorney & Paralegal are the people assigned to the Matter

I am tired now so, maybe, i am wrong.
But this sentence say me that the peoples from Matter has the property to be "Attorney" or "Paralegal" (or both ???) (BTW: I don't understand this words )
So, you don't need 2 separate table for this.
You should use a table named... "tblPeoples" with this fields:
tblPeoples
ID_People - AutoNumber (PK)
ID_Property - LookUp on table tblProperies (see bellow) (FK)
Other fields like Names, Adresses and so on.
tblProperties
ID_Property - AutoNumber (PK)
PropertyName - Text (Att or Plg)

In your Matter table you should have a reference to table tblPeoples. Something like this:
tblMatter
ID_Matter - AutoNumber (PK)
ID_People - LookUp on table tblPeoples (FK)
Other fields

Now should be easy to define the queries.

 

[DeannaKate]

Thanks so much, Mihail. I'll consider your suggestion in the morning when I'm back at my desk. (I'm on the east coast of the U.S.)

 

[DeannaKate]

I don't think Mihail's suggestion will fix my problem. The attorney and paralegal are people, but they fulfill different functions and need to be different fields. refAtty and refPlgl are just look up tables. I don't need to keep any other info about these folks except their names. (Others in the firm keep personnel info.) And I do need to have that extra text field, "AssignmentNotes," in order to record the odd exception.

Again, defining the query isn't the problem. The problem is getting the query to work as a filter in the form in the context of cascading combos.

Thanks!

 

[DeannaKate]

Okay, the problem seems to be that the query involves several different tables. Once I put an Atty and Plgl field into tblLeadData and put them into the query instead of the corresponding fields from refAtty and refPlgl, the query filters just fine in the form. This solution, however, does require that I put all that extra data into tblLeadData, which I've been trying to keep lean. So it's not the best solution, but it is working.