Filtering field in subform

[Tamara]

I have a separate form called Frm_ProvinceSelect. This form just has one
combo box called cb_ProvinceSelect. The combo box is unbound and selects the
name of a province from a list. Once one selects the province one then
presses OK - pressing OK opens the form 'Form_ByProvince'.

Form_ByProvince contains a whole lot of data. It then contains a subform
called Frm_Visit. Within the subform 'Frm_Visit' there is a field called
'province'. On the main form in properties the source is a query called
Qry_ByProvince which basically contains all fields and then under the
province field the criteria is:
[Forms]![Frm_ProvinceSelect]![Cb_ProvinceSelect]. If I run the filter in
datasheet view it works but won't work in the form view.

My relationships set up are as follows: Main Table to Visit Table is one to
many relationship with the ReferralID being the key in Main Table and the
VisitID being the key in the Visit Table but ReferralID is also listed in
Visit Table. One to many relationship is direct between ReferralID in Main
Table and ReferralID in Visit Table. The gist of the database is that it
contains referrals received - for each referral there may be many different
visits.

I have been stuck at this all day it is driving me insane I would appreciate
any help that can be provided.

 

[KARL DEWEY]

Post the SQL of your Qry_ByProvince.

 

[Tamara]

Here is the sql

SELECT Tbl_Referral_Main.PPReferralID, Tbl_Visit.VisitID,
Tbl_Referral_Main.[Subclass of Visa], Tbl_Referral_Main.Surname,
Tbl_Referral_Main.[First Name], Tbl_Referral_Main.[Date of birth],
Tbl_Referral_Main.[File Number], Tbl_Referral_Main.[Internal/External
Referral], Tbl_Referral_Main.[Source Referred From],
Tbl_Referral_Main.[Lodgement Date - Internal / Date of Referral - External],
Tbl_Referral_Main.[Referral type if external], Tbl_Referral_Main.[Due Date],
Tbl_Referral_Main.[Referral Finalised?], Tbl_Referral_Main.[Date referral
finalised], Tbl_Referral_Main.Outcome, Tbl_Referral_Main.Comment,
Tbl_Referral_Main.Priority, Tbl_Referral_Main.[Case >9 months old?],
Tbl_Visit.Address, Tbl_Visit.Province, Tbl_Visit.District, Tbl_Visit.[Date of
proposed visit], Tbl_Visit.[Date of actual visit], Tbl_Visit.[Date site visit
report finalised]
FROM Tbl_Referral_Main LEFT JOIN Tbl_Visit ON Tbl_Referral_Main.PPReferralID
= Tbl_Visit.PPReferralID
WHERE
(((Tbl_Visit.Province)=[Forms]![Frm_ProvinceSelect]![Cb_ProvinceSelect]));