Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Thread: Query Form

  1. #1
    Join Date
    May 2006
    Posts
    25

    Unanswered: Query Form

    I'll admit from the start that I'm a newbie at Access 2003 and I've tried looking in the help file and internet searches. But I've become stumped on how to create what I'm looking for.

    I would like to create a form that performs a query that has drop down menus with info from each specific column in a table. That way I don't have to necessarily need to know off the top of my head what data is entered in that column, but can click the drop down menu and get all of the data listed in that particular column. Once have a particular set of info selected, then have a button like "submit" and then the query results appear in a form and click through each page or results.

    Is this possible?

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Yes it is. And there are limitless ways for you to do it ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    If you are new to Access then I would assume you are new to programming in VBA. If so then I personally don't know how you would be able to do it. I would consider what you are trying to do an intermediate level project. You will first have to learn VBA (Visual Basic for Applications), Access forms and table design as well as SQL.

    The jist of the process is the create an unbound form with all of your controls (combo boxes, check boxes, text boxes, etc.). Then create a subform. The subform should be a continuous form with all of the fields you want the user to see (this will be your results). Add the subform to the form. Then behind the form (in the form module) add the code that builds a WHERE clause based on the selections the user picked. Then change the RecordSource of the subform to display the filtered records.

    The more controls you add to the form the more complicated the WHERE clause construction becomes.

    When I was learning VBA I ran across a very good explanantion for how to create a search form. The explanation took an entire chapter. I would recommend starting with a book on VBA programming. The rest you should pick up as you go.

  4. #4
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    Take a look at the download i have attached to this post.

    http://www.dbforums.com/showthread.php?t=1217158

    It uses the same principles for what you want;

    hope this helps

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

  5. #5
    Join Date
    May 2006
    Posts
    25
    Dan,

    Thanks for the info. I'll try that out and see what I can come up with. If (I should say "When") I have questions, I'll post them here ok?

    Thanks for your help. Truly appreciate it.

  6. #6
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    Sure no worries.
    sometimes simple is best.... and i'm just a simple fellow.

  7. #7
    Join Date
    May 2006
    Posts
    25
    Dan

    I'm not quite getting it yet. I tried making the item you talked about in that link you posted. But I can't get it to work correctly.

    I guess I'm confused with this line you posted...

    Create a new form, bind it to "tbl_search" and drop in the field [criteria], disable the navigations buttons also. Add in a button to open the query/report/dataentry form with filter/form with list box, to display the results.

    Could you please explain this a bit more?

    Thanks

  8. #8
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    look at the full set of posts in that link, there is a download. if that doesnt help post me why it isnt working, i'll see what i can do.

    dan
    sometimes simple is best.... and i'm just a simple fellow.

  9. #9
    Join Date
    May 2006
    Posts
    25
    danlindley...

    would it help me to send you my file and I can tell you what I'm looking for?

  10. #10
    Join Date
    May 2006
    Posts
    25
    Dan,

    I'll put it this way. It's probably my ineptness at Access. I've even tried to do this example of MS's on their website without the results they say I should have at this link. http://support.microsoft.com/kb/304428/en-us

    I create everything the way they say to do it in the instructions and I don't get the query results they talk about. I always get a full set of records whenever I hit the "search" button on the example form there.

    I do find one error though in the query table. I enter the line...

    Forms![QBF_Form]![WhatCustomer] Or Forms![QBF_Form]![WhatCustomer] Is Null

    Just like they say to do so that it's a single line under criteria. When I close that query and reopen it, the table in design view is messed up. The first part of the line Forms![QBF_Form]![WhatCustomer] is in the location where I put it. But there is a new column created and has "is null" in it's location.

    I'm just not sure where I'm going wrong.

  11. #11
    Join Date
    May 2006
    Posts
    25
    It works correctly if I click on the QBF_Query if I click under the Queries area in the Database Window and enter the search criteria that MS says to on the website. It's only when I enter it into the form do I get the same results every time. I close the query results window each time.

  12. #12
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    Hi;

    Sure, post your DB as a zip and I'll take a look at it for you.

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

  13. #13
    Join Date
    May 2006
    Posts
    25

    Database post...

    Dan,

    Here's my copy of the sample Database that Microsoft provided I tried to enter the info into that and can't get the form to work. If I have that example working I can modify it to make it work for my own database I believe.

    Ultimately, I would like a form that has drop down menus that allows a user to select a given response from a table and then "search" for any given results with the multiple criteria. If blank, then "null" statement works.

    But for now, I'd just go for getting a form to work for a search.

    Thanks again for your help.
    Attached Files Attached Files

  14. #14
    Join Date
    May 2006
    Posts
    25
    Dan,

    This file is based on this example http://support.microsoft.com/kb/304428/en-us

  15. #15
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    Can you post Your database? as a zip, I can take a look at it and get it running for you.

    dan
    sometimes simple is best.... and i'm just a simple fellow.

Posting Permissions

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