Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92

    Unanswered: Lookup/Search Query Problem

    Starting with the simple questions first.

    1. I have done standard queries before (using the wizard), but now need to do one that searches for an entry from the user.
    IE..user enters part of a company name or city and the query only selects those companies that meet that criteria.
    I guess I'm having trouble figuring out how to create a query based off of a text box or dropdown list.

    2. After querying info for number 1, I need to do another query using the ComapnyID based off of the chosen results that will open a new form and fill the info with it.
    I'm having trouble here doing queries based off of a field already shown on the screen (CompanyID), as well as the combination of queries and opening forms.

    I guess these are both basically the same problems.

    3. I see people entering SQL script into MS Access to get what they want. How do I get to this screen?

    Can anyone give advice, or a place that will explain this all simply?
    I've looked up many places, but the ones I've found only cover the basic queries without using search fields entered by the user.

  2. #2
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169

    Hi

    Create a new form and go to the data tab in the properties
    Click the Record Source and then click the ... button after it. It should bring up a query box. Double click the table you want to use and put in the fields that you want to use in the query. In the criteria enter the location on the field that you want to filter by (Drop Down etc) this will be something like

    forms!CompanyFrm!CompanyID

    If you want to display lines with only part of the field, say you want to search for Company but only want to type com then:

    like "*" & forms!CompanyFrm!CompanyID & "*"

    All you have to do is enter a criteria in the ID field and the other field you want to filter by and then you have a filtered form.

    Once you have finished it come out of the query builder and add the field to the form in a line

    <CompanyID> <CompanyName> <Company Address>

    When you open the form now it will filter out on whatever you have filled in on the other forms and put them in lines down the page

    To make it look better go into the properties of the page and click the format tab and change the default view to continuous forms.

    Hope that helps

  3. #3
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169

    Sql

    SQL can also be entered from the query builder in the top of the screen where you click to view the query you have just done, if you click the little drop down next to it then it will say "SQL"

  4. #4
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    Thanks Chris! I think thats just what I'm looking for. I new there was a way to do it, just never had to do it in Access before.

    Your help and time is much appreciated!

  5. #5
    Join Date
    May 2004
    Posts
    65

    Have you looked at the combobox or listbox wizard.

    In a form you can create a combobox or listbox based on information in an underlying table or query.

    Alternatively you can use the after update event procedure to set the rowsource setting of your second combobox.

    If you want an example have a look the attached database.

    Run the combodemo form and then go into form design and have a look at the visual basic code.

    Regards

    Gavin
    Attached Files Attached Files

  6. #6
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    Thanks Gavin,
    I think I'll use that in my forms!

  7. #7
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    ok, I've tried the method, but have a problem. First, I dont have a Record Source in my data tab, I'm using Access 2003, so it might be different.

    Although I have been able to locate the area in the vb area, it appears a bit different.

    I've got 2 problems with it. 1) It wont do partial sorts (like entering "Com" istead of "Company"

    and 2) how in the world do I unsort a screen like this to bring back up all the records?


    Here's what I got so far:

    **************************
    Private Sub Command58_Click()
    On Error GoTo Err_Command58_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmHotelInfo"

    stLinkCriteria = "[strCity]=" & "'" & Me![Text56] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_Command58_Click:
    Exit Sub

    Err_Command58_Click:
    MsgBox Err.Description
    Resume Exit_Command58_Click

    End Sub
    Private Sub Command61_Click()
    On Error GoTo Err_Command61_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmHotelInfo"

    stLinkCriteria = "[strHotelName]=" & "'" & Me![Text59] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_Command61_Click:
    Exit Sub

    Err_Command61_Click:
    MsgBox Err.Description
    Resume Exit_Command61_Click

    End Sub
    ************************************

Posting Permissions

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