Results 1 to 14 of 14
  1. #1
    nygirl is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2018
    Posts
    6

    Creating parameters form with results in subform

    Hey all,



    I have been working with Access many years ago, I am a bit rusty and would appreciate your help very much.
    I have a pretty simple data architecture and relationship, please see attachments.

    What I want to do is to be able to choose from 3 different combo box the parameters and to display result set in a subform (please see attachment)

    Your help is much appreciated!
    Thank you!
    Attached Thumbnails Attached Thumbnails Form.PNG   Relationship.PNG   AccessTables.png  

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You can start by using the LinkMasterChildFields properties of the SubFormControl. You'll then see the problem with "AND/OR" which you can address in the RecordSource of the SubForm in a query.

  3. #3
    nygirl is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2018
    Posts
    6
    Thank you for your reply, is there any video you can refer me to? It's a great direction but I'm afraid I need more than that because I have forgotten everything..

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You can look for "search using multiple fields" or something like that and it will give you lots of options.

  5. #5
    nygirl is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2018
    Posts
    6
    Hey RuralGuy,

    I think I got it! I am just wondering why this prompt?

    could you please take a look?
    Much appreciated!


    Click image for larger version. 

Name:	PromptAccess.PNG 
Views:	14 
Size:	17.0 KB 
ID:	32219Click image for larger version. 

Name:	AfterEventAccess.PNG 
Views:	14 
Size:	53.5 KB 
ID:	32220

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Let's start with you not using Reserved words which can drive Access nuts. :http://www.allenbrowne.com/AppIssueBadWord.html#S

    1) SQL is reserved.
    2) A Form (or SubForm) will automatically requery when you set the RecordSource.
    3) Use descriptive names for your controls. "Combo9" is not very descriptive.
    4) Queries that ask for parameters are usually caused by misspelling something.

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Too strident? Did I lose you? Are you still working on this?

  8. #8
    nygirl is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2018
    Posts
    6
    So sorry only seeing this now.
    I have tried to tweak the code but wasn't successful..

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Did the Query you created return any records?

  10. #10
    nygirl is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2018
    Posts
    6
    When I get the prompt and I enter whatever value (A/B/C/D/E) I do get results back so query is working.
    I think from some reason it doesn't recognizes the parameter name in the query? or doesn't pass the value?

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Use the Query Builder to create the query. It knows how to reference controls on a form.

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You're still going to need to decide how to deal with the three controls. Do you only want records that match all three controls (which is how you have it now)?

  13. #13
    nygirl is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2018
    Posts
    6
    The way I envisioned this was as follows:
    I am going to have a few comboxes and maybe 1 free text.
    The user can use whatever combination of selection, can choose from all comboboxes or none then it will retrieve everything.
    Is it even doable?

  14. #14
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Certainly doable but would probably require a dynamic query that you create with VBA. Is that okay with you?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query Parameters Returning All Results
    By turk1559 in forum Queries
    Replies: 5
    Last Post: 07-07-2014, 08:02 AM
  2. Replies: 1
    Last Post: 07-22-2013, 12:00 PM
  3. Replies: 2
    Last Post: 04-12-2012, 05:16 PM
  4. Replies: 14
    Last Post: 03-07-2012, 03:46 AM
  5. Replies: 1
    Last Post: 07-21-2011, 01:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums