apply a child form filter on parent form

[souris]

I wanted to apply a filter on dropdown box for children form.
I do not know is it possible or not.

For example, I have a form which connects a table. The table's primary key
is Account_Number.
There is a child form has Account_Number and Call_Type primary key.
May I filter call_type on the parent form?
Any information is great appreciated.

Souris,

 

[Allen Browne]

You want to filter the main form such that it only displays accounts that
have a particular Call_Type in the subform?

If so, see:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html
The article explains how to effectively filter the main form, by assinging
its RecordSource to an INNER JOIN statement.

 

[souris]

thanks millions,

Souris,

You want to filter the main form such that it only displays accounts that
have a particular Call_Type in the subform?

If so, see:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html
The article explains how to effectively filter the main form, by assinging
its RecordSource to an INNER JOIN statement.

 

[souris]

It is a nice article for filter children.
Thanks for the information,

I use following SQL statement to Me.RecordSource.

SELECT A.ACCOUNT_NUMBER, MAX(B.TIMESTAMP) FROM A

JOIN C on A.ACCOUNT_NUMBER = C.ACCOUNT_NUMBER

JOIN B ON A.ACCOUNT_NUMBER = B.ACCOUNT_NUMBER

WHERE C.CALL_TYPE = 1

GROUP BY A.ACCOUNT_NUMBER

HAVING COUNT(C.NUMBER) >= 1

ORDER BY A.ACCOUNT_NUMBER



The SQL is working when I use SQL server stored procedure, but It does not
work when I use MS Access.

I got 'syntax error clause FROM '

If I take out the JOIN then it is working.

Any idea about this?

Any information is great appreciated.

Souris,

 

[Allen Browne]

Try adding brackets around one of the JOINs.

You could also use the graphical query grid in Access to mock up the query,
and then switch it to SQL View (View menu from query design), and copy what
you see there.

 

[souris]

Thanks I found the right SQL and just working fine now.
If the result recordset is empty then lost all the controls on the form.
Are there any recordcount property to verify it before filter to avoid this
happen?
Thanks again,

Inung Huang

 

[Allen Browne]

Yes, if there are no records and no new records can be added, the Detail
section goes completely blank.

After applying the filter, you could examine:
Me.RecordsetClone.RecordCount

If you want to test before, you might be able to use DLookup() to see if
there is at lease one match.