SubForm control (2 Viewers)

[DavRob]

I have a form which uses a combo box to populate fields in a sub form which works as it should, the issue that I have is that I wish to use this form as a sub form.

The combo box "txtSearch" is in the Header of form "SearchCustFrm" this has the control source "SearchCustQry" column "CustName" in the query has the following criteria
Like "*" & [Forms]![SubForm]![frmCustSearch]![txtSearch].[Text] & "*"

As stated this form works fine until I use it as a Sub Form, when I open the Main Form I get a dialogue box requesting to "Enter Parameter Value"
Forms!SubForm!FrmCustSearch!xtSearch.Text & "*"

I believe the problem is to do with the referencing of sub form, I have searched but cannot resolve the problem.

Any help would be greatly appreciated'

DavRob

 

[arnelgp]

change the criteria and add the "main form" name:

Like "*" & [Forms]![MainFormName]![SubForm]![frmCustSearch].Form![txtSearch] & "*"

 

[Gasman]

Keep this in your bookmarks

Forms: Refer to Form and Subform properties and controls

 

[DavRob]

arnelgp​

Thanks for the reply, but still get the same outcome.

Gasman​

Thanks also for the reply, I had forgotten about using them, I'll use them in future

 

[June7]

I have never used dynamic parameterized queries. Review http://allenbrowne.com/ser-62.html

Why do you reference Text property?

So the criteria you posted is not the working version but what you attempted after embedding as subform? I am not surprised it fails.
Reference subform container control, not the form name. I always name container different from object it holds, like ctrDetails

Like "*" & [Forms].[MainFormName].[ctrDetails].Form.[txtSearch] & "*"

The article arnelgp linked shows that syntax. I think copy/paste bit Gasman and he did not notice the subform form name [frmCustSearch] which should not be there.

Or try SQL statement as subform's RecordSource:

SELECT * FROM tablename WHERE CustName LIKE "*" & [txtSearch] & "*";

Then shouldn't matter if form is opened standalone or as subform.

 

[Gasman]

One important factor is it is the subform control name NOT the subform name, which should be different. I always renamed the control name to make sure, but the wwizard defaults to the same name.

Mainform / Subform control / "Form" / control

 

[Pat Hartman]

I have never used dynamic parameterized queries

I use them for most of my queries. I only build SQL using VBA is the query is actually dynamic - meaning that I have to change something that will cause the execution plan to have to be rebuilt so it can't be a variable.

In this particular example, Access seriously messes up the SQL when you paste it in in order to facilitate its QBE display. This is the reason that many experts hate querydefs. For queries like this with multiple complex conditions in the where clause, I usually store a string copy of the query either right in the code for backup even though I use the querydef or sometimes I keep a table and post the SQL strings there because it is easier than making a VBA string.

What I did discover though is if you post the string into the SQL view and save it, Access won't rewrite the string unless you switch to QBE view and then save the querydef. If you do that, you can replace the string if you want to with your "backup".

 

[DavRob]

Hi
Thankyou for the replies

Apologies for the late reply

I still can't get this to work.
I am trying to use a subForm to search records using the search as you type method, regardless of the approach it brings up the "Enter Parameter Value" dialogue box.

I have added a sample DB

 

[Gasman]

Are we talking avout InputFrm here?
If so then the first form name needs to be that?

Now you are putting the cart before the horse though, as you cannot put a value in until that Inputfrm is opened?
I would probably just set a filter on either form and open with all data.

Tip: Use the builder to get the correct syntax?
That gave me the forms part and I just needed to add the control name with the Form prefix

Forms![InputFrm]![frmCustSearch].Form.txtSearch

However I always rename my subform control differently from the subform name to identify which I am talking about.

Forms![InputFrm]![ctrlfrmCustSearch].Form.txtSearch

Either txtSearch control does nothing though?