Search all table records from filtered form

[BenEl via AccessMonster.com]

Hi. I'm hoping someone can help me with this search question...

MT_Position / PK = PositionID
MT_Candidate / PK = CandidateID
MT_Interview / PK = FirstName, LastName, Email / FK = PositionID, CandidateID

I have a tabbed form. On tab 1 is the main form (recordsource = MT_Position).
On tab 2 is a sub form (recordsource = QryCandidateInterview). The subform
displays all the Candidates who have applied to 1 Position. I would like to
include on this subform a searable list box with all the Candidates (not just
those related to the position). How can I do this? Any search I've been able
to put into the form, only searches the attached Candidates.

Thanks!

 

[Lord Kelvan]

Sorry to say that table structure seems well nuts it should be

MT_Position / PK = PositionID / fields = position fields ie
positionname,positiondescription, etc
MT_Candidate / PK = CandidateID / fields = FirstName, LastName,
Email
MT_Interview / PK & FK = PositionID, CandidateID / fields = (only
thing i can thinks might be useful here is an appointment type thing
such as interviewdatetime)

Ok first for the form i would have the recordsource as MT_Position
On the first tab have a hidden text box which has the controlsource of
positionid and name it something like txtpositionid
For the listbox on the second tab (NO SUBFORMS I think they are evil)
name it something like lstCandidates and with the properties of number
of columns as 4 and column widths as 0;2.5;2.5;4

In the forms onopen or onload event have this code
'=====code start======
lstCandidates.rowsource = "select MT_Candidate.CandidateID,
FirstName,LastName, Email from MT_Interview ,MT_Candidate where
MT_Candidate.CandidateID = MT_Interview.CandidateID and PositionID =
" & cint(txtpositionid.value)
lstCandidates.requery
'=====code end======'

My suggestion is based on the changes i have made to your table
structure as your table structure does not make sense unless the
candidate changes his/her name for every interview.

Hope this helps

Regards
Kelvan