Results 1 to 7 of 7
  1. #1
    DBenz is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    76

    Combo List on Form to pick field entry for Query upon which form is based ?

    Hi, I have a database on scale Modelling stocks, the form F Modelling Stocks displays (with images) records with scale, source, title, price, description, location and so on, it also has a subform FrmForAircraftType and having for example entered a new record for Merlin lll engine 1/48 scale and all the other details for the main form fields, I would then in its subform select in the combolist Spitfire then create a second subform record selecting from the Combolist Hurricane then a third selecting Defiant and a fourth selecting Battle and a fifth selecting Wellington.



    The two tables T Modelling Stocks and TblForAircraftType are related on ID ModellingStocks. One cannot filter by form on the subform, choosing Spitfire for example, it asks for ID ModellingStocks ! .

    I now have a query Qry T Modelling Stocks for Filtering that brings together T ModellingStocks and TblForAircraftType on ID ModellingStocks and a copy of the main form called F Modelling Stocks For Query Aircraft Type that now draws its content on that query .

    Method is clunky though...
    1) launch query in design view then type for Criteria in the AircraftType field Like "*Spitfire*" and save and close query.
    2) launch F Modelling Stocks For Query Aircraft Type and it displays records for subform entry Spitfire.
    3) close F Modelling Stocks For Query Aircraft Type and launch F Modelling Stocks For Query Aircraft Type and alter entry to Like "*Lancaster*" and save and close
    4) launch F Modelling Stocks For Query Aircraft Type and it displays records for subform entry Lancaster. otherwise it doesnt refresh !

    I wish to have a combolist on the main form displaying the AircraftType field entries so choosing from that list would enter that choice in the criteria box in the query on which the form is based and have the form display the items for that aircraft.

    Better still to have that on the original main form F Modelling Stocks that is showing all records, and have it launch the form F Modelling Stocks For Query Aircraft Type that is based on Qry T Modelling Stocks for Filtering , saves me closing one form and opening the other ! Can one do this ?

    I am very rusty on the setup having last dabbled many years ago, so a talk through 'dummies guide' step by step of what to do would be most welcome and essential.

    I also need to have access to this Access database when at shows, how is this best done ? I dont want to carry about a laptop !
    Thanking you in advance.

    DBenz

    p.s typed this out in the box then when submit it lost all the gaps and created one massive text block, why ? had to insert type tool and hit enter to unravel it again.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How about posting a copy of the database? It's often easier if you present the issue in simple English --no jargon and no database terminology. Once readers understand WHAT you are trying to accomplish, they often respond with suggestions/options for How it might be done with Access.
    Good luck.

  3. #3
    DBenz is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    76
    Hi,
    Its 12Mb and the images 729Mb

    I am very proud of this database and protective, releasing it to the entire world loses me any marketing control on it and reveals my possessions as well. Uneasy as such on doing so and as for the size ????

    I simply wish to know if a combo list can be created on the form that enables me to alter the word in the criteria box in the query that the form draws its results from.

    I would click on combolist, select Hurricane, and the query gets updated with Like "*Hurricane*" from its current Like "*Spitfire*" in field AircraftType and the form now displays records for Hurricane.

    opening query, typing name in after checking with another query/table how its spelt, closing query with a save then opening the form based on the query again is not slick.

    just looking at 5 aircraft just now meant 30 separate open and closes when selecting the 5 aircraft each in turn from the combo would have been 5 easy steps.

    DBenz

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You could :
    -select an entry in your combobox
    -use the afterUpdate event of a combo box, to modify an SQL statement,
    -requery your form to get the info based on the modified SQL statement/recordsource.

    I wasn't asking you to divulge anything confidential nor potentially commercial, I was looking for a small sample of data and a form to illustrate your issue. My bad, for not being more explicit in my post.

    Good luck.
    Last edited by orange; 12-03-2017 at 09:38 AM. Reason: spelling

  5. #5
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    You can make a copy of the db with imaginary data.
    Make a copy and delete all the data from the copy.
    Enter a couple of example dummy records, and make that work correctly so that we can see the problem.
    Perhaps delete any forms, tables, queries and reports from the copy that don't come into play for this purpose.
    Post copy here.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Seem to describe a dynamic parameterized query. I never use them.

    SELECT * FROM tablename WHERE somefield LIKE "*" & Forms!formname!controlname & "*";

    I prefer VBA code setting form Filter and FilterOn properties or the WHERE CONDITION of OpenForm/OpenReport. Review http://allenbrowne.com/ser-62.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 1
    Last Post: 12-28-2016, 11:39 AM
  2. Replies: 1
    Last Post: 11-05-2015, 04:16 AM
  3. Replies: 3
    Last Post: 11-26-2014, 06:05 PM
  4. Replies: 1
    Last Post: 04-14-2014, 01:38 PM
  5. Replies: 1
    Last Post: 12-05-2013, 01:15 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