Running a Query in a subform of a subform

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

Answer 1

Hi Floyd,

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

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

Hope it helps...

Answer 2

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

Answer 3

Hi Floyd,

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

Answer 4

No parameter prompt.  It just seems to ignore it. 

Answer 5

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?

Answer 6

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.

Answer 7

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.

Answer 8

It returns all records.

Answer 9

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?

Answer 10

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 [e-mail].

Answer 11

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.

Answer 12

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!

Answer 13

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 [e-mail].