Filtering on field from related table

[RipperT]

Friends,



I have 2 unbound cbo boxes on a form that are used to filter records (via
VB) based on values from 2 fields of the form's underlying table. (I have it
set up so that only one cbo box can be used at a time) I would like to add a
third cbo box to filter based on a field that is in another table but linked
to the forms underlying table thru the PK-FK relationship. Is there an easy
way to do this?



Regards,



Rip

 

[Allen Browne]

Use a subquery in the Filter

The example assumes that the form is bound to MyMainTable, and contains a
field named Fruit. You then filter on MyOtherTable if it has a matching
Fruit:

Me.Filter = "EXISTS (SELECT MyId FROM MyTable WHERE MyOtherTable.Fruit =
MyMainTable.Fruit)"
Me.FilterOn = True

If subquerier are a new idea, here is Microsoft's introduction:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Of course, the Filter property can be anything that is valid in the WHERE
clause of a query.

 

[RipperT]

Thanx, Allen,

I am not new to subqueries, but I am new to using them in filters. Can you
clarify the differences between MyTable, MyMainTable and MyOtherTable in
your example? And also, I need to use whatever value is input into the
cboBox in the filter. What might that look like?

Thanx again.

Rip

 

[Allen Browne]

Sorry: MyTable is the same as MyMainTable in the example.

Okay, if subqueries are familiar, you know you can use them in the WHERE
clause of a query. Since the form's Filter is effectively a WHERE clause,
you can use them in the Filter as well.

To put a literal value into the filter, just concatenate it into the string,
e.g.:
WHERE MyOtherTable.Fruit = """ & Me.MyCombo & """"
Drop the extra quotes if the Bound column of the (unbound) combo is a Number
type field, not a Text type field.

The other alternative is to change the RecordSource of the form to an INNER
JOIN statement, so it only contains records that match the other table.
That's suitable if the other table is a lookup (one-side of the relation);
if it is on the many side, you end up with the records multiple times.
Example of this approach in this article:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

 

[RipperT]

Sorry: MyTable is the same as MyMainTable in the example.

No need for apologies; I assumed as much, but wanted to clarify.
I came up with something similar (after much head scratching) and it works
well.

Thanx for the help! Cheers!

Rip

 

[RipperT]

Hello,

I have used your suggested code and it works except if the filter returns no
records, the entire form goes blank and I am forced to close it out. I
notice that if I 'allow additions', this doesn't happen, but I must set the
'allow additions' property to 'no'. How can I fix this?

Many thanx,

Rip

 

[Allen Browne]

If Access cannot display the new record, and there are no records to show,
the entire Detail section of the form goes blank, and even controls in the
form header and footer may not display correctly.

The simplest workaround it probably to set Allow Additions to Yes, and
cancel the Before Insert event procedure of the form:
Private Sub Form_BeforeInsert(Cancel Is Integer)
Cancel = True
MsgBox "You cannot add new records."
End Sub

 

[RipperT]

Thanx, Allen. Thanx to everyone! You guys sure are making me look good!

Rip

 

[RipperT]

This is odd, I've added the code you suggest to the before_insert event and
it works when logged on as a user with admin permissions, but it doesn't
work when logged on as a user without admin permissions. Any ideas?

Thanx,

Rip

 

[Allen Browne]

Perhaps the non-admin user does not have permissions to add records?

 

[RipperT]

This is true, but I don't understand how that affects the problem. I cannot
give them permissions to add records. Would it be possible to have a msgBox
come up informing the user that no records were found, then maybe turn off
the filter?

Rip

 

[Allen Browne]

In whatever code you use to apply the filter, you could:
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "Nobody here."
Me.FilterOn = False
End If