Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    Ontario, Canada
    Posts
    7

    Arrow Unanswered: Filter form based on column in query results

    Good afternoon everybody, I have been building a rather large computer assets inventory database for a week or so now and have run into a snag.

    Heres a basic rundown of the part of the database I'm having trouble with. I have a table [Assets] which contains various information regarding IT hardware, I also have a table [SoftwareList] which contains information about the various software we own licenses for. In the middle of this I have a table [AssetsSoftware] which links [Assets] to [SoftwareList] to fix the many <-> many relationship.

    Now I have a query that returns all the Asset#'s that don't have a certain Software# installed which works OK. But I would like to use the Asset#'s returned by this query as a filter for another form. I tried something along the lines of:

    AssetNum IN(queryname!columnname)

    as the filter for the form but this doesn't seem to do it.

    If anybody knows a way for me to get this working, all help is greatly appreciated, I'd be happy to give more information about the database in general if it would help straighten things out. Thanks for the help.

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Filter form based on column in query results

    Originally posted by Wally_Lawless
    Good afternoon everybody, I have been building a rather large computer assets inventory database for a week or so now and have run into a snag.

    Heres a basic rundown of the part of the database I'm having trouble with. I have a table [Assets] which contains various information regarding IT hardware, I also have a table [SoftwareList] which contains information about the various software we own licenses for. In the middle of this I have a table [AssetsSoftware] which links [Assets] to [SoftwareList] to fix the many <-> many relationship.

    Now I have a query that returns all the Asset#'s that don't have a certain Software# installed which works OK. But I would like to use the Asset#'s returned by this query as a filter for another form. I tried something along the lines of:

    AssetNum IN(queryname!columnname)

    as the filter for the form but this doesn't seem to do it.

    If anybody knows a way for me to get this working, all help is greatly appreciated, I'd be happy to give more information about the database in general if it would help straighten things out. Thanks for the help.
    The following code worked for me using some of my data. I just used a command button to test it and monitored the record results.

    My form was based on the table tblAircraft. The table tblWO contained some of the records from tblAircraft so as an experiment:

    Dim strSQL as string
    strSQL = " (((tblAircraft.RegID) In (Select Distinct RegID from tblWO)))"
    Me.FilterOn = True
    Me.Filter = strSQL

    Seems like you could set up something similar for your data.

    Hope it helps.

    Gregg

  3. #3
    Join Date
    Nov 2003
    Location
    Ontario, Canada
    Posts
    7
    Thanks basicmek, your code worked perfectly. For those that may be reading this later, here's what I did to make it work with a query.

    1. Changed query type to 'action query' which posted results to a temporary table.
    2. Executed query from VBA after user enters information and hits OK button.
    3. Ran code suggested by basicmek after the query has completed, performing the search on the temporary table that the modified query creates.
    4. Applied filter to form.

    Here is the actual code I used:
    Dim stDocName As String
    Dim SearchString As String

    stDocName = "Software Exclusion Query"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

    SearchString = "AssetNum In (Select AssetNum from softexcludetemp)"
    DoCmd.OpenForm "Assets2", , , SearchString

    That did it. Thanks again for the help.

Posting Permissions

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