Results 1 to 5 of 5
  1. #1
    Iron_Horse is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Location
    Sussex, UK
    Posts
    5

    Question Query - (Run by Macro). Criteria source from multiple forms, but problem when one form is closed.

    I have a solution to my problem, but it's clumsy. I'm sure I can do this with better written Query Criteria.
    Has anyone got a slicker solution?

    So.
    Two tables (linked)
    [TbClient] and [TbJobs]


    Clients are unique but can have several Jobs

    Two Forms:
    [FmClientSearch] and [FmJobSearch]
    The user will only have one of these open at a time (and we're too far down the rabbit hole to amalgamate them into one form)

    My Query [QySearch] objective is to populate a listbox on a third form [FmResults], for user to click on choice of one property.

    (Chosen property then used within a variety of reports.)
    Query takes source data from [TbJobs] table, but criteria from the forms.

    Query Field 1 is [ClientName]
    Query Field 2 is [JobReference]
    (Actually about 6 fields, but 2 for the sake of this discussion)

    [ClientName] Criteria: Like "*" & [Forms]![FmClientSearch]![CliTargetField] & "*" OR [Forms]![FmClientSearch]![CliTargetField] Is Null
    (this criteria is looking at a target field on a form coming originally from TblClient)

    [JobReference] Criteria: Like"*" & Forms![FmJobSearch]![JobRefSrch] & "*" OR Forms![FmJobSearch]![JobRefSrch] Is Null
    (this criteria is looking at a user-populated field on a form coming from TblJobs)


    So it doesn't matter if one of the fields is blank, it simply runs the query on the other criteria.
    Which is fine if both of my forms are open. But not if one of them is closed.
    The user will have one form open in front of them.
    My solution is to use the "Run Query" button macro to open the other form in the background with a blank field, and then close it all again afterwards, but it all feels a bit clumsy and likely to fall over at some point.

    Is there a simple argument I can add to the criteria along the lines of " iif [FmClientSearch] isn't open then treat [CliTargetField] as Null "

    I've been banging my head on the wall for hours about this, and know it has to be simple.

    It's been many years since I used Access regularly, so I thought you guys might be able to help!

    Cheers. :)

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Something about the process doesn't make sense
    The user will have one form open in front of them.
    yet you say it works fine if both are open. If both are open just so you can see if it fixes the problem, then isn't the problem that you are trying to perform two different searches with the same bit of code?
    Either
    - run separate sql statements or queries; one per form
    - build separate sql statements in code, depending on which form was selected to be opened
    - test which form is open and either run the required sql statement or the required query based on the result

    The last makes the least sense to me. As long as you reference 2 different forms in your criteria yet only open one, what you are doing will never work.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Iron_Horse is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Location
    Sussex, UK
    Posts
    5
    Thanks Micron
    Yes thats right. It's a bit of a cross-roads query - 2 routes coming in, all ending with a list box on a single Navigation-menu Form - which then gives a menu of options for multiple routes out, using that single record.
    I've solved it by using Macros to open the other form in the background, and then close it when it's no longer needed. The alternative would be to duplicate queries and the menu form.

    I was hoping for a phrase to add to the criteria... "Look for this, but if you can't find it because the source is closed, then ignore it." I thought the IsNull might sort it but no such luck.
    Hey Ho. Every day's a learning experience.

    Cheers!

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Such an approach is possible, certainly if using code. I don't use macros so cannot say for that case. If it were code, it could be something like
    If CurrentProject.AllForms("FormName").IsLoaded = False Then
    do this
    Else
    do that
    End If

    The do is either run this sql statement or this other one, or open this query or this other one, or whatever it needs to be.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Or you could write a custom function that returns the desired value from the "open" form or "*" if the form is closed and you use it with Like fnJob().
    This is the function I use to test if a form is open (Micron's version works too):
    Code:
    Public Function isOpen(ByVal strFormName As String) As Boolean
    '=======================
    'creates IsOpen function
    '=======================
    Const conDesignView = 0
    Const conObjStateClosed = 0
    isOpen = False
    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
        If Forms(strFormName).CurrentView <> conDesignView Then
            isOpen = True
           
        End If
    End If
    End Function
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 2
    Last Post: 07-12-2015, 08:48 AM
  2. if Form is closed ignore Query criteria
    By quicova in forum Queries
    Replies: 3
    Last Post: 09-20-2013, 04:50 PM
  3. Replies: 1
    Last Post: 03-28-2013, 07:54 AM
  4. Replies: 3
    Last Post: 12-02-2012, 04:35 PM
  5. Replies: 4
    Last Post: 08-17-2011, 05:30 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