Allen Browne's FindAsUType module

[riccifs]

Hi to everyone in the N.G.,
can someone show me how to modify the Allen Browne's FindAsUType
module, to search a record inside a subform as well as I can do in a
bound form?
My main form is an unbounded one.

I have a second question about this module, why when I try to find in
a combobox with a row source to query or table the code does not fire?
it pop-ups a input box asking to me for a parameter...

Hear is the link where I find the code I'm using for search in a form:
http://allenbrowne.com/AppFindAsUType.html#Troubleshooting

Hope Allen or someone else will help me!
Bye, Stefano.

 

[Allen Browne]

The code is designed to be incredibly simple to implement (no changes
needed), but it does assume you are searching the form that contains the
search box and combo (for field selection.)

You could modify it to look at another form, but you would need to modify
the code to do that. I have not done that for you.

 

[riccifs]

The code is designed to be incredibly simple to implement (no changes
needed), but it does assume you are searching the form that contains the
search box and combo (for field selection.)

You could modify it to look at another form, but you would need to modify
the code to do that. I have not done that for you.

Hi Allen, thanks for your answer
how can I search for a record in a subform that is on unbounded main
form?

Hope you will help me in some way,
Bye, Stefano.

 

[Allen Browne]

Perhaps you could follow this example:
http://allenbrowne.com/ser-62.html

This one shows how to build up a filter string from several unbound boxes,
and apply it to the form's filter. You can adapt it to a subform by
changing:
Me.Filter = strWhere
Me.FilterOn = True
to:
Me.Sub1.Filter = strWhere
Me.Sub1.FilterOn = True

 

[riccifs]

Perhaps you could follow this example:
http://allenbrowne.com/ser-62.html

This one shows how to build up a filter string from several unbound boxes,
and apply it to the form's filter. You can adapt it to a subform by
changing:
Me.Filter = strWhere
Me.FilterOn = True
to:
Me.Sub1.Filter = strWhere
Me.Sub1.FilterOn = True

Hi Allen,
I'm using this code:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([myfield] Like ""*" &
Me.txtFilterMainName & "*"")" And ""
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing
in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something
there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen) 'Finally, apply the
string as the form's Filter.
Me.Nameofmysub.Filter = strWhere
Me.Nameofmysub.FilterOn = True
End If
End Sub

.... but it does not work. What I'm missing?
Many thanks, Stefano.

 

[Allen Browne]

Suggestions:

1. Quotes are wrong in the filter string:
strWhere = strWhere & "([myfield] Like ""*" & _
Me.txtFilterMainName & "*"") And "

2. You need to replace MyField above with your field name.

3. You omited the .Form bit:
Me.Nameofmysub.Form.Filter = strWhere
Me.Nameofmysub.Form.FilterOn = True
Explanation:
http://allenbrowne.com/casu-04.html

4. Errors suppressed.
You should be getting error messages with it the way it is. Therefore you
must have error message suppressed somewhere. In the VBA code window, choose
Options on the Tools menu. ON the General tab, make sure Error Trapping is
set to:
Break on Unhandled Errors

I also suggest you uncheck Compile on Demand.
And on the Editor tab, uncheck Auto Syntax Check, and check Require Variable
Declaration.

5. Compile
Then choose Compile on the Debug menu, and fix any errors.

 

[riccifs]

Suggestions:

1. Quotes are wrong in the filter string:
strWhere = strWhere & "([myfield] Like ""*" & _
Me.txtFilterMainName & "*"") And "

2. You need to replace MyField above with your field name.

3. You omited the .Form bit:
Me.Nameofmysub.Form.Filter = strWhere
Me.Nameofmysub.Form.FilterOn = True
Explanation:
http://allenbrowne.com/casu-04.html

4. Errors suppressed.
You should be getting error messages with it the way it is. Therefore you
must have error message suppressed somewhere. In the VBA code window, choose
Options on the Tools menu. ON the General tab, make sure Error Trapping is
set to:
Break on Unhandled Errors

I also suggest you uncheck Compile on Demand.
And on the Editor tab, uncheck Auto Syntax Check, and check Require Variable
Declaration.

5. Compile
Then choose Compile on the Debug menu, and fix any errors.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,
I'm using this code:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([myfield] Like ""*" &
Me.txtFilterMainName & "*"")" And ""
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing
in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something
there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen) 'Finally, apply the
string as the form's Filter.
Me.Nameofmysub.Filter = strWhere
Me.Nameofmysub.FilterOn = True
End If
End Sub

... but it does not work. What I'm missing?
Many thanks, Stefano.

Hi Allen,
your suggestions are fine! Now everything is working properly, thank
to you.
I really appreciate the help you give to me.

Many thanks, I'm thinking you're the king of the search & filter
codes!
Bye, Stefano.

 

[efandango]

Allen,

I have a master Form called 'frm Runs', and a subform called 'frm_Points'
They are linked by a master/child field called: [Run_no] On each subform are
18 records.

How can I adapt your form/code to find the first instance of the field on
any Sub record accross all linked master records?

at the moment I can only get your controls to find a record based on the
Master Run_No that is showing.

My Subform has two relevant fields; [Run_point_Venue] and [Run_point_Address]
The Primary Key for my records on the subform is: [Point_ID]

 

[Allen Browne]

The FindAsUType code will not be simple to adapt. It is designed to examine
all the controls on the form, and figure out which ones are bound to a field
that can be filtered (which depends on lots of factors including your
version of Access) and their data types, what name the user knows the fields
by (which varies for controls with attached labels in Form view or labels in
the Form Header for continuous form view), and loads them into a combo
storing the actual filter-field name in a hidden column and displaying the
user-known name in the visible column. It would not be simple to adapt that
to read subform fields and work with them as well.

I think you would be better to adapt this one:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
This one provides specific boxes where the user can enter the values they
want in any combination, and your code builds the appropriate filter from
that. For the fields that are from the subform, you would build a filter
string that includes a subquery.

The filter string would end up something like this:
WHERE EXISTS (SELECT Point_ID FROM Table2
WHERE Table2.Run_no = Table1.Run_no)

For more help with subqueries, see:
http://allenbrowne.com/subquery-01.html

 

[efandango]

That's a real shame because your first form had the neat feature of whittling
down the fields as you searched, and was just a nice slick dialogue.

But if I have to use the 2nd example, at least i'll be able to search across
the whole record sets. I have a form and sql and was wondering how, and where
I would include your line:

WHERE EXISTS (SELECT Point_ID FROM Table2 WHERE Table2.Run_no =
Table1.Run_no)

I tried to put it after the FROM statement, but Access just complained that
it couldn't find 'Table2'... I don't even have a Table1. If it helps, I can
get by with just searching the one field, [Run_point_Venue]

MY SQL:

SELECT tbl_Points.Getround_Flag, tbl_Points.Point_ID, tbl_Points.Run_No,
tbl_Points.OrderSeq, tbl_Points.Run_point_Venue,
tbl_Points.Run_point_Address, tbl_Points.Restriction, tbl_Points.SetDown,
tbl_Points.SetDown_Direction, tbl_Points.LeaveBy1, tbl_Points.LeaveBy2,
tbl_Points.LeaveBy3, tbl_Points.LeaveBy4, tbl_Point_Notes.Point_Note,
tbl_Points.Run_Point_Postcode
FROM tbl_Runs INNER JOIN (tbl_Points INNER JOIN tbl_Point_Notes ON
tbl_Points.Point_ID = tbl_Point_Notes.Point_ID) ON tbl_Runs.Run_No =
tbl_Points.Run_No
ORDER BY tbl_Points.Run_No, tbl_Points.OrderSeq;

The FindAsUType code will not be simple to adapt. It is designed to examine
all the controls on the form, and figure out which ones are bound to a field
that can be filtered (which depends on lots of factors including your
version of Access) and their data types, what name the user knows the fields
by (which varies for controls with attached labels in Form view or labels in
the Form Header for continuous form view), and loads them into a combo
storing the actual filter-field name in a hidden column and displaying the
user-known name in the visible column. It would not be simple to adapt that
to read subform fields and work with them as well.

I think you would be better to adapt this one:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
This one provides specific boxes where the user can enter the values they
want in any combination, and your code builds the appropriate filter from
that. For the fields that are from the subform, you would build a filter
string that includes a subquery.

The filter string would end up something like this:
WHERE EXISTS (SELECT Point_ID FROM Table2
WHERE Table2.Run_no = Table1.Run_no)

For more help with subqueries, see:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I have a master Form called 'frm Runs', and a subform called 'frm_Points'
They are linked by a master/child field called: [Run_no] On each subform
are
18 records.

How can I adapt your form/code to find the first instance of the field on
any Sub record accross all linked master records?

at the moment I can only get your controls to find a record based on the
Master Run_No that is showing.

My Subform has two relevant fields; [Run_point_Venue] and
[Run_point_Address]
The Primary Key for my records on the subform is: [Point_ID]

 

[Allen Browne]

... I have a form and sql and was wondering how, and where
I would include your line:

WHERE EXISTS (SELECT Point_ID FROM Table2 WHERE Table2.Run_no =
Table1.Run_no)

It goes into the Filter string, i.e. you build up a string, and then assign
it to the form's Filter property (remembering to turn on FilterOn as well.)