By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,910 Members | 552 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,910 IT Pros & Developers. It's quick & easy.

Creating a Form to find a Record using Multiple Criteria

P: 1
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!
Jun 5 '10 #1
Share this Question
Share on Google+
1 Reply

P: 181
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Search_Click()
  2.     Const cInvalidDateError As String = "You have entered an invalid data."
  3.     Dim strWhere As String
  4.     Dim strError As String
  5.     strWhere = "1=1"
  6.     ' If canditate name
  7.     If Not IsNull(Me.candidatename) Then
  8.     'Create Predicate
  9.         strWhere = strWhere & " AND " & "applications table name.[candidateID] = " & Me.yourcandidate control& ""
  10.     End If
  11.   Else
  12.     If strError <> "" Then
  13.         MsgBox strError
  14.         End If
  15.         Me.your subform name.Form.Filter = strWhere
  16.         Me.your subform name.Form.FilterOn = True
  17. End Sub
Welcome to BYTES
Jun 5 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.