Creating a Form to find a Record using Multiple Criteria

[mbbum88]

Hi there,

I'm still relatively new to Access and am trying to build a database to track applicants for jobs.

The form I'm having a problem with is one for "Editing applications". I want to use this form to look up an application and then use a subform to add on information about interviews.

I think I actually have two problems:

1.) I want to search for a single record using two criteria and I'm not sure how

2.) When I'm trying to search for a record using the foreign key in a combobox, the foreign key is listed several times, once for each time it's in a record of the table.


I have an autonumber ApplicationID as my primary key for the Application table, but would like to select a Candidate using their Name, instead of by the CandidateID (which is the foreign key of the Application Table).The problem is that because some candidates apply to more than one Position, the combobox shows their name for as many applications as they already have.

For example, if Homer Simpson, Marge Simpson, Bart Simpson and Lisa Simpson all applied to a "Cartoonist" position, but Lisa also applied to a "Lawyer" position, when I try to search by the candidate name (or even Candidate ID), it will look like this:

Bart Simpson
Homer Simpson
Lisa Simpson
Lisa Simpson
Marge Simpson


I don't want Lisa to show up twice just because she has two applications. If I were looking for the record in the Application table where Lisa wants to be a Cartoonist, I wouldn't be able to know which one it would be. Likewise, if I were to start with the other combobox and search by position, then I would see 4 entries of "Cartoonist" and one for "Lawyer" with no way of figuring out which one is the actual record that I want.

I want the form to allow me to choose from a list of Positions that are vacant, and then after that, select the Candidate and then view the Application information specific to those two criteria.

I know this is a really complicated question that involves a lot of work, but if anyone can even point me towards another place I can find the answer, please let me know.

I've been trying to figure this out for about a week now. =(. Any help would be GREATLY appreciated!

[mseo]

hi,mbbum88

I don't want Lisa to show up twice just because she has two applications.

you shouldn't use bound controls in search form
you should use unbound combobox and get the candidate name by query to be listed in the combobox and the same for your second criteria control
then put your search cmbbutton on the form and use the following code behind it:

Private Sub Search_Click()
    Const cInvalidDateError As String = "You have entered an invalid data."
    Dim strWhere As String
    Dim strError As String
    strWhere = "1=1"
    ' If canditate name
    If Not IsNull(Me.candidatename) Then
    'Create Predicate
        strWhere = strWhere & " AND " & "applications table name.[candidateID] = " & Me.yourcandidate control& ""
    End If
  Else
    If strError <> "" Then
        MsgBox strError
        End If
        Me.your subform name.Form.Filter = strWhere
        Me.your subform name.Form.FilterOn = True
End Sub
 

Welcome to BYTES