View Single Post
  #2  
Old May 21st, 2010, 02:57 AM posted to microsoft.public.access.forms
Lord Kelvan
external usenet poster
 
Posts: 637
Default Search all table records from filtered form

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