Results 1 to 7 of 7
  1. #1
    adeebarsad is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    15

    Show only records you choose in a form

    I'm trying to create a form that when you choose a test(s), all data related to the test will be shown based on the test you have chosen. I want it to be shown in a grid but I don't know how to connect it between the test chosen and the grid from the table. There is only one table I've created. *Edited: Let say I want to add 10 tests, that means the tests I've chosen will be shown in the list below.

    Here is what I want it to look like:
    Click image for larger version. 

Name:	InkedGrid_LI.jpg 
Views:	21 
Size:	21.6 KB 
ID:	27920

    Last edited by adeebarsad; 03-20-2017 at 02:07 AM.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    create a query that works if you manually put in the test value, i.e. it would give you the records you want to appear on your subform. Then change the test reference in the query to Forms!frmYourFormName.txtComboBoxName and it should still work if the form is open with a value in the combo box showing. Then when in form design view, on the property sheet, choose the code builder for the AfterUpdate event for the combo. Between the Private Sub and End Sub lines that you should now see, enter

    Code:
    Forms("MainFormName").Controls("subformControlName").Form.Recordsource = "qryYourQueryName"
    If you manage all this and it doesn't error out, you should not have to force Access to requery the subform, but if the records are not changing and the query worked before, add
    Code:
    Forms("MainFormName").Controls("subformControlName").Form.Requery
    You could make the query a filter query and apply it as the subform filter property but I thought the suggestion would be easier.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    adeebarsad is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    15
    How do you change the test reference in the query?

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Another way would be to create a temporary table containing the Test ID. In the OnClick event of the "Add" button, run an append query to append that test ID to the table and requery the subform. The subform will have a query as its record source based on that table (and all the other fields). You will also need a "Clear" button which will delete all records in the temp table.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    How do you change the test reference in the query?
    "test" being your table field or control (or whatever) name you're using. You replace that with Forms! and the names of your objects, not the made up ones I used.
    Forms!frmYourFormName.txtComboBoxName

  6. #6
    adeebarsad is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    15
    How do I create a temporary table?

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    A temporary table is just a regular table that isn't part of the relationships, it comes and goes and has no permanent meaning. Sometimes these are created in a make-table query, sometimes - like now - you will create it and leave it there, and use append and delete queries on the data. So create a table, the only field will be the test id, and this will be the list of test id's to show in the subform (add it to the record source).

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

Similar Threads

  1. Show all records on form
    By Brightspark98 in forum Forms
    Replies: 4
    Last Post: 01-14-2017, 02:58 AM
  2. filtered subform unshown records when choose another brand
    By georgesobhyy@gmail.com in forum Forms
    Replies: 6
    Last Post: 09-19-2016, 07:32 AM
  3. Replies: 8
    Last Post: 06-08-2012, 02:27 PM
  4. Show only some records in Form View
    By Douglasrac in forum Forms
    Replies: 3
    Last Post: 05-15-2011, 11:01 AM
  5. How do I choose multiple records for a report
    By admaldo in forum Reports
    Replies: 2
    Last Post: 03-03-2006, 06:02 AM

Tags for this Thread

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