none
Running a Query in a subform of a subform RRS feed

  • Question

  • I finally got my query working, but then found it needed to be modified to work inside of the subforms I set up.

    The main form is frmMenu1.  From there I select frmReportMenu from the navigation bar.   I then select frmReportQryVendor from that menu's navigation bar.

    I can get the query to work correctly if I open frmReportMenu separately using 

    Like "*" & [Forms]![frmReportMenu]![NavigationSubform2].[Form]![txtVendor] & "*"

    Thus far, I haven't been able to make it work when running it from inside the double navigation forms.

    Like "*" & [Forms]![frmMenu1]![NavigationSubform2].[Form]![txtVendor] & "*"  Returns all records if I leave the field blank, but no records if I populate the field.

    Anything else I've tried just pops up wanting data for the field.

    Like "*" & [Forms]![frmMenu1]![NavigationSubform].[Form]![NavigationSubform2].[Form]![txtVendor] & "*"

    Thanks in advance for your help

    Floyd Adams

    Thursday, July 27, 2017 3:15 PM

All replies

  • Hi Floyd,

    The sequence for the syntax should be something similar to the following:

    Forms!MainFormName.FirstSubformControlName.Form!SecondSubformControlName.Form!TextboxName

    Hope it helps...

    Thursday, July 27, 2017 3:48 PM
  • Apparently I named both subforms the same.....

    This worked for all but the last two fields I'm using.  

    The field for txtTracking works, but the field for txtSerialNumber doesn't.

         Like "*" & [Forms]![frmMenu1]![NavigationSubform2].[Form]![NavigationSubform2].[Form]![txtTracking] & "*" OR [Forms]![frmMenu1]![NavigationSubform2].[Form]![NavigationSubform2].[Form]![txtTracking] is Null

         Like "*" & [Forms]![frmMenu1]![NavigationSubform2].[Form]![NavigationSubform2].[Form]![txtSerialNumber] & "*" OR [Forms]![frmMenu1]![NavigationSubform2].[Form]![NavigationSubform2].[Form]![txtSerialNumber] is Null

    It seems to just ignore the criteria altogether for txtSerialNumber

    Thanks,

    Floyd Adams



    Thursday, July 27, 2017 7:27 PM
  • Hi Floyd,

    What exactly is happening? Are you getting a parameter prompt?

    Thursday, July 27, 2017 8:31 PM
  • No parameter prompt.  It just seems to ignore it. 
    Thursday, July 27, 2017 8:49 PM
  • No parameter prompt.  It just seems to ignore it. 

    Are you saying if you enter a value you know exists, it still shows you all the records?
    Thursday, July 27, 2017 8:51 PM
  • Correct.  

    In the case of the txtTracking field, I can enter any part of the tracking number, say 1Z, and it will find all records where the tracking number contains 1Z.  If I leave it blank, it finds all records, including those with no data in that field.

    It should be working exactly the same way with the txtSerialNumber field, but it is not.

    Thursday, July 27, 2017 8:59 PM
  • Hi,

    Assuming txtSerialNumber is not a Memo field, test the criteria by entering an exact match first. For example, temporarily change the criteria to just this:

    [Forms]![frmMenu1]![NavigationSubform2].[Form]![NavigationSubform2].[Form]![txtSerialNumber]

    Then, enter an exact match to see if the form finds the record or not.

    Thursday, July 27, 2017 9:04 PM
  • It returns all records.
    Friday, July 28, 2017 1:00 AM
  • It returns all records.

    There might be something odd about the Serial Numbers. Why would it return all the records if you changed the criteria to only return matching records?
    Friday, July 28, 2017 2:51 AM
  • Hi FloydAdams,

    I suggest you to check that from your syntax you are able to fetch the value correctly or not.

    if you are able to fetch the value correctly then try to run that query manually by passing these values in it manually.

    check what is the result.

    if you go the same result then may be it is problem in your query and you can try to modify it to get correct result.

    you did not posted the whole query and values that are passing from the controls.

    if you post then we can try to make a test on our side and try to reproduce the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 28, 2017 3:00 AM
    Moderator
  • I stripped out all of the extra statements that get added into the query criteria when you put an OR statement in the first line of a criteria.  It was only then that I was able to finally query by a known serial number when I entered it directly as a criteria.

    Friday, July 28, 2017 11:15 AM
  • Using it as a baseline, slowly start adding the other criteria one at a time until you run into the problem again, and maybe it will give you a clue what is causing the problem.

    Good luck!

    Friday, July 28, 2017 2:33 PM
  • Hi FloydAdams,

    can you show us your query.

    if you can try to post it then we can provide you better suggestion to solve the issue.

    also post your testing result so that we can see how you are trying to run your query.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 31, 2017 2:03 AM
    Moderator