Putting a form with searchfields into a subform

[Bongobob21]

Hi,

I created a form with 30 searchfields. All those are linked to a query. Now I wanted to use this form in a Subform on another form. When I pulled the form into the subform, I got the error that the searchfields are not working anymore, as they can't find the path anymore.
When I did some research I found out, that I have to change the Criteria in the Query, as I have change the criteria from this: [Forms]![MainForm]![Subform].[Form]![name] to [Forms]![MainForm]![name].
As there are so many searchfield where I would have to change it, is there a possibility to do this automatically or just link the form to a subform so that I can keep the criteria as it is?




Thank you!

[ranman256]

The search form does NOT have to be in the sub-form to function. Or is this just where you want to see it? If it is in the main form, you have less 'code'.

USE THE BUILDER TOOL. You can pick from a list where your boxes are and there is no misspelling.
You are searching in the table not the form. So your criteria would be [Forms]![MainForm]![name] (in main form)
if your search boxes are in the sub form:
[Forms]![MainForm]![SubformName].[Form]![name]

[keviny04]

Hi,

I created a form with 30 searchfields. All those are linked to a query. Now I wanted to use this form in a Subform on another form. When I pulled the form into the subform, I got the error that the searchfields are not working anymore, as they can't find the path anymore.
When I did some research I found out, that I have to change the Criteria in the Query, as I have change the criteria from this: [Forms]![MainForm]![Subform].[Form]![name] to [Forms]![MainForm]![name].
As there are so many searchfield where I would have to change it, is there a possibility to do this automatically or just link the form to a subform so that I can keep the criteria as it is?


Thank you!

Open up the query in SQL view and copy the SQL statement to a text editor that can search and replace. Then you can use it to search and replace form references as needed. Then paste the SQL back to the query. This would be the quick and dirty way that takes care of your immediate concern.

For long term, since you have so many criteria fields and may need to modify queries from time to time, consider building your queries with VBA code. E.g. Me.RecordSource = "SELECT * FROM MyTable WHERE crit1='" & Forms![MainForm]![crit1] & "'"

If I were you, I would put the names of the 30 search fields in an array to make it easier to construct SQL with code. E.g.

CritFieldNames = Array("crit1", "crit2", "crit3", ...)
sqlstr = "SELECT * FROM MyTable WHERE "
For i = 1 To 30
    sqlstr = sqlstr & CritFieldNames(i) & "='" & Forms("MainForm")(CritFieldNames(i)) & "' AND"
Next i
sqlstr = sqlstr & " True"
Me.RecordSource = sqlstr

Another advantage of using code is that you can search and replace text in code directly.

[Bongobob21]

Thank you guys, I got it to work with your inputs :-)