Results 1 to 4 of 4

Thread: Search button

  1. #1
    Join Date
    Sep 2002
    Location
    USA
    Posts
    66

    Unanswered: Search button

    Greetings,

    I have a main form (frmHouseHold) and a subform (sfrmApplicants). In both forms I have a field called Social Security Number (HouseHold’s SS#) and (Aplicant’s SS#).
    I was trying to create a search capability, whereby when a user clicks a button will return the value from both main & subform. For instance, the user is looking SS# 123-45-6789 and doesn’t know if it’s a HouseHold or an Applicant, then the user will click a button to search throughout the database (both main form and subform) and returns the value.

    Thank you in advance!

    OCM

  2. #2
    Join Date
    Jan 2004
    Posts
    75

    Re: Search button

    Originally posted by OCM
    Greetings,

    I have a main form (frmHouseHold) and a subform (sfrmApplicants). In both forms I have a field called Social Security Number (HouseHold’s SS#) and (Aplicant’s SS#).
    I was trying to create a search capability, whereby when a user clicks a button will return the value from both main & subform. For instance, the user is looking SS# 123-45-6789 and doesn’t know if it’s a HouseHold or an Applicant, then the user will click a button to search throughout the database (both main form and subform) and returns the value.

    Thank you in advance!

    OCM
    Question: What is the name of the table where the SSN information is stored? You will need to run a simple query that queries both fields with the same expression in criteria for both... using the 'like' & '*' with that expression.

  3. #3
    Join Date
    Sep 2002
    Location
    USA
    Posts
    66
    The HouseHold SSN is stored in tblHouseHold (field name: HholdSSN) and in tblApplicants (field name: AppSSN). Since each HouseHold can have more than one Applicants, I then created a main form HouseHold and a subform Applicants.

    Please let me know if this clarifies...

    Thanks!

  4. #4
    Join Date
    Jan 2004
    Posts
    75
    If I am understanding what you want...

    1) Create an unbound control on your form frmHousehold for the user to enter the SSN they wish to search for.

    2) Create a query from both tblHouseHold and tblApplicants using the field from your form ([forms]![frmHouseHold]![social security number]) as the criteria. SQL view would look like:

    SELECT tblHouseHold.HholdSSN, tblApplicants.AppSSN
    FROM tblHouseHold, tbl Applicants.AppSSN
    WHERE (((tblHouseHold.HholdSSN) Like [Forms]![frmHouseHold]![social security number] & "*") AND ((tblApplicants.AppSSN) Like [Forms]![frmHouseHold]![social security number] & "*"));

    3) Create a form to display the results of the query

    4) Create a command button. On the On_Click event, open the created form using the query you built in step 2.

    Would that work?
    Last edited by sublimsos; 01-16-04 at 11:26.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •