Filter on form with subforms problems :(

[mattkbishop]

Matt I explained how to do it but you don't understand, I think.

Use Wayne's example. Change the boxes to include your category

Ammend the query to take out the criteria in the boxes you removed. Add the criteria to Category.

This will, providing you fill in the search boxes, Orgs in say Bedford with special needs capability.

I'm sorry mate, I don't get that either...

Could you explain it a bit simpler for me please?

Also:

A friend sent me this....

"Filters only work (normally) at the level of the form they are on. So if you apply a filter to a sub-form it merely limits which records are shown on that sub-form, not on the main form (as you have discovered). You could code your way around this and "promote" the filter up to the main form, but it's nasty stuff."

Maybe that would be an easier solution?

 

[neileg]

You could code your way around this and "promote" the filter up to the main form, but it's nasty stuff."

Maybe that would be an easier solution?

Your mate is spot on. It would be nasty stuff. His explanation of what is happening with your two filters is the same as mine, so I wouldn't argue with that.

The trouble is that you need to use queries and you don't understand these. Take Wayne's query and remove the criteria that he has inserted. In the column ContactPostalTown type in Bedfors in the Criteria line. In the column Category type in the same criteria line Special Needs. Run the query by pressing the ! in the menu bar.

Your query will return records that have Bedford as the Postal Town and Special Needs as the Category. This is how the query should work for you.

Now use the Boxes that Wayne inserted in the search tab and point them at the columns in the query like he did, but select whichever columns you want. If you include criteria in the same line, all conditions must be met ( this is an AND relationship). Put them on separate lines and it becomes an OR where the record will be returned if any conditions are met.

Good luck!

 

[mattkbishop]

The trouble is that you need to use queries and you don't understand these. Take Wayne's query and remove the criteria that he has inserted. In the column ContactPostalTown type in Bedfors in the Criteria line. In the column Category type in the same criteria line Special Needs. Run the query by pressing the ! in the menu bar.

Your query will return records that have Bedford as the Postal Town and Special Needs as the Category. This is how the query should work for you.

Now use the Boxes that Wayne inserted in the search tab and point them at the columns in the query like he did, but select whichever columns you want. If you include criteria in the same line, all conditions must be met ( this is an AND relationship). Put them on separate lines and it becomes an OR where the record will be returned if any conditions are met.

Good luck!

Ok I think Wayne's way might work....

Basically, I want what he did, but on the proper whole version of mine. Plus if I do it myself I will understand it.

Basically, I copied his query across. I see the:

Like "*" & [Forms]![frmProfessionalInfo]![txtSearchOrg] & "*"

...bit is referencing to the current form, and an unbound text field. THen, I copied the unbound fields and code over also.

But it doesn't work when I type a village. What am I doing wrong?

Obviously I want all 133 records to be shown, and not duplicated like Wayne's.

And is there a way to search on the subforms like this? Else there's not much point. Since multiples would have to be selected, I would want a list (list box?) where you can hold CTRL and click multiples.

So um.... help again please about how to do this. Maybe I should start Wayne's bit from scratch on my whole database - how?

 

[WayneRyan]

Matt & Neil,

Wow, have you guys been busy. I have been swamped at
work this week, so I haven't spent much time on it.

The query that I used was just a copy of the existing query
in the database. The record source for the main form was
just the table tblProfessionalInfo. I remembered that it
didn't return the exact recordset of the table and I think
it had something to do with a field that should have values
from 1-6 and some were null. To me, it didn't matter at the
time.

As I don't use filters etc., I just wanted to submit an example
of a technique that can be used. The unbound textboxes
can be replaced with unbound combos if you wish.

I'll try to get the time today to read the last few posts.

Wayne

 

[mattkbishop]

Thanks.

 

[neileg]

I'm going to let Wayne pick up on this. You can do what you want.

As Wayne has commented previously, your visual design is great, but your understanding of handling the data leaves something to be desired.

One problem you're having is that you are confusing the display of the information with the information itself. You have refered to omitting duplicate records, and to searching subforms.

The forms are a way of showing the data. The data is held in tables, or in queries based on tables. You don't search the forms, you search the underlying data. It is irrevevant whether your query returns what appears to be duplicated data, so long as it is presented to the user in the correct way.

 

[mattkbishop]

Right, I'm going Wayne's route now, but I've hit a few snags.

I've added a SEARCH tab to the form, and got an unbound text field named "filtTelNumber". In a query which queries all the fields bar the memo ones, I have the criteria:

[Forms]![frmProfessionalInfo]![filtTelNumber]

frmProfes.... is the form, and filtTelN.... is the unbound text field as I just stated.

So I enter a number, run the query, and up pops a datasheet with my result. That works fine.

So far I'm happy, since I can autoform that query, and get the button to open the form, where I'll add some controls such as print records and the like.

First problem - I can't perform a wildcard search like this (to say pull up every number beginning with 01234)

Next problem - I am happy with this way, except I have no clue how to search on my categories like it. Ideally, I would want it like you have a list box, only the user can hold CTRL to select multiple items, and search.

This is made worse by how my table structure is. I obviously don't want it to show one record for every time it has a certain category (if I search for more than one category).

Once I get the multiple selection thing working like this for category, I could do the same for age ranges, and then things like Town would be easy.

So.... any help please would be great

 

[neileg]

To use a wildcard search you need the 'like' operator with the *, e.g.
Like "*" & [YourControl] & "*"

To use multi select combo boxes, search the forums. There have been a number of mini tutorials on this subject.

To ensure that you don't get multiple answers to your searches, you can use the unique values property.

 

[mattkbishop]

To use a wildcard search you need the 'like' operator with the *, e.g.
Like "*" & [YourControl] & "*"

Hmmm I tried Like "*" [Forms]![frmProfessionalInfo]![filtTelNumber] & "*" but I got a syntax error.

EDIT: Ah, I found Like [Forms]![frmProfessionalInfo]![filtTelNumber] & "*" works. That's solved thankyou

To use multi select combo boxes, search the forums. There have been a number of mini tutorials on this subject.

To ensure that you don't get multiple answers to your searches, you can use the unique values property.

Hmmm...... I had a quick search mate but i don't really know what I'm looking for.

And the Unique values property is whereabouts?

 

[neileg]

Hmmm I tried Like "*" [Forms]![frmProfessionalInfo]![filtTelNumber] & "*" but I got a syntax error.

Oops! Should have been
Like "*" & [Forms]![frmProfessionalInfo]![filtTelNumber] & "*"

Missing out the first * as you have done means it will search the start of each field. Putting in the * will find the parameter anywhere in the field.

Hmmm...... I had a quick search mate but i don't really know what I'm looking for.

Never used multi select combos myself, but I've read about them here. Try searching for "multi select"

And the Unique values property is whereabouts?

In the query properties.

 

[WayneRyan]

Hi guys,

Haven't been around much lately. My only activity here lately
was to post a plea for help with a report dealing with several
large memo fields.

Matt, what you are looking for is a multi-select list box.
This will mean that you won't be able to use a query to
specify your criteria. The new record source for your form
will be:

SQL = "Select field1, field2 ... " & _
           "From YourTable " & _
           "Where field1 = '" & Me.Listbox(1) & "' And " & _
           "            field2 = '" & Me.Listbox(7) & "'"
Forms![MainForm].RecordSource = SQL
Forms![MainForm].Requery

Just a few thoughts. There examples of multi-select list
boxes here.

Wayne

 

[mattkbishop]

Matt, what you are looking for is a multi-select list box.
This will mean that you won't be able to use a query to
specify your criteria. The new record source for your form
will be:

SQL = "Select field1, field2 ... " & _
           "From YourTable " & _
           "Where field1 = '" & Me.Listbox(1) & "' And " & _
           "            field2 = '" & Me.Listbox(7) & "'"
Forms![MainForm].RecordSource = SQL
Forms![MainForm].Requery

Just a few thoughts. There examples of multi-select list
boxes here.

Wayne

This means I'm back to square one. I'll take some time to think and maybe start a new thread in a bit once I've decided on my route, I'm sure I've seen it done this way before... maybe not.