Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Help with search

    Hi

    I'm trying to create a search feature in my database, but can't get my query working correctly.

    I have created a form with a selection of textboxes, two comboboxes and a checkbox. These are set out like the following:-

    Textboxes: CompanyName, Town, County, Country, PostCode, NoOfEmployees

    ComboBoxes: CurriculumArea, BusinessArea

    CheckBox: OnStop

    I already have a form which lists all of the company's details, and used the search query as the record source. But it doesn't seem to bring back the correct results.

    I used the query wizard to select all of the records in the table, and in the criteria section added the following to CompanyName:-

    Like "*" & [Forms]![frmSearch]![txtEmpName] & "*"
    I have placed all the other criterias for the textboxes, comboboxes and checkbox under the OR section of the query on the second line.

    All of the other textboxes have the same criteria accept the last part which links to the name of it's control on the search form.

    The comboboxes and checkbox contain the following type of code:

    [Forms]![frmSearch]![cmboCArea]
    What I want to do is allow a user to be able to search by the company name primarily, but then add additional search criteria after e.g. Company Name & Country, but also I want the user to be able to select all of the companies in a curriculum area, without having to enter the company name.

    I'm not sure if I have set my query up wrong, but it doesn't seem to do this.

    Can anyone help?

    Thanks

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I admit to being a bit confused. Is frmSearch an open form, is it the current form, or is it closed altogether?

    Sam

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Are you outputting the results to a Report or another Form?

    BUD

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by KevCB226
    Hi

    What I want to do is allow a user to be able to search by the company name primarily, but then add additional search criteria after e.g. Company Name & Country, but also I want the user to be able to select all of the companies in a curriculum area, without having to enter the company name.

    I'm not sure if I have set my query up wrong, but it doesn't seem to do this.

    Can anyone help?

    Thanks
    Not sure of how you have your ComboBoxes to pull their data, but have your CompanyName ComboBox's ControlSource based on the Query with all of the Company names in it. Be sure to set it as Select Distinct so that you make sure to eliminate dups. From there create your action CommandButton (the one to pull the Form/Report) to show the results. On mine I pull up a Report to show the results as such:

    Code:
    Private Sub cmbOpenTestInventoryRpt_Click()
    On Error GoTo Err_cmbOpenTestInventoryRpt_Click
    
        Dim stDocName As String
        Dim stLinkCriteria As String
        
        stDocName = "TEST Inventory Dialog"
        
        stLinkCriteria = "[Category]= " & "'" & Me![Category] & "'"
        DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
        
    
    Exit_cmbOpenTestInventoryRpt_Click:
        Exit Sub
    
    Err_cmbOpenTestInventoryRpt_Click:
        MsgBox Err.Description
        Resume Exit_cmbOpenTestInventoryRpt_Click
        
    End Sub
    That's about it. Oh, the form is Unbound also.

    have a nice one,
    BUD

    btw, in mine where you see Category, that would be where you'd have CompanyName or whatever your field is called that contains the company names.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    maybe this demo can help. it illustrates my way of doing a search. it displays the hits in a list using the SQL as .rowsource but the same SQL could be used as .recordsource for a form.

    using SQL directly makes it easy to handle the something/nothing selected situation.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Sep 2005
    Posts
    240
    Thanks for the replies I managed to sort it out last week in my query. I found an example on utter access, so credit goes to him. And it's really simple as well, this is how I did it if you want a simple search in your db.

    What I had to do was do the following in my query:

    Set the first column as tblName.* to select the whole table.

    Then in the rest of the columns set the following for each field that will be used in the search, and deselect show.
    In the field row put in [FieldName] & ""
    And in the criteria put the following bit of code

    Like IIf([Forms]![frmSearch]![NameOfControl] Is Null,"*",[Forms]![frmSearch]![NameOfControl] & "*")
    This basically works by looking at the control and if it's null it uses a wildcard, if it's not null it uses the value of the control and adds a wildcard at the end.

    EDIT: Forgot to mention that the above also works with comboboxes on a form.
    Also just had a look at your search izyrider, looks good, more detailed that what I have, but might use it in a future development if that's OK.
    Last edited by KevCB226; 03-06-06 at 07:45.

Posting Permissions

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