Solved

Filter Subform based on text box and combo boxes

Posted on 2013-06-06
15
1,395 Views
Last Modified: 2013-06-07
Hi,

I have found some answers on here, but I can't get them to work for my data. I have a "Documents" Form, with an unbound text box "Reference" and 4 unbound combo boxes "Doc Type", "Team" "Cupboard" and "Box". I then have a "DocumentsSubform" which displays the results of an "AllDocs" query.

I'd like the subform to be filtered by any combination of the reference field or the combo boxes. The "Reference" search also needs to be able to contain partial references of anything up to 12 alpha and numeric characters.

I don't want to use the after-update property for any of the boxes, I'd just like to trigger the query refresh from a "Search" button.

Can someone please help me write the code for the text box / combo box filter with the wildcards?

Many thanks
0
Comment
Question by:littlegembear
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
15 Comments
 
LVL 27

Expert Comment

by:jjafferr
ID: 39227004
Do a Main Form/Child relation:
click on the main form, then click once on the sub form,
in the properties you will see, Link Master field, so fill it as:

Link Master field: [Doc Type], Team, Cupboard,Box
Link child field: [Doc Type], Team, Cupboard,Box
this is assuming the subform fields have the same name as the main Form (but please watch the sequence of fields, which should be the same for both the master and child forms).

this will automatically make the subform bring the Records based on the fields selected.

as far as the field "Reference" is concern, you can add it to the end of the above links (for both the main and the subform), but what I didn't understand is, partial thing!!

nevertheless, add it and test it, with Reference and without.

jaffer
0
 

Author Comment

by:littlegembear
ID: 39227070
Hi,

Thanks for getting back to me so quickly. I tried the solution you provided above and it didn't work.

Where previously I had all the rows of data showing in the subform, when I applied the Master / Child link, it didn't show anything in the subform except the option to add a new row in dataset.

By partial, I mean if a reference looks like: 1234512AR000 or 1234512BR000 I'd like to be able to type in 12345 and it brings back anything beginning with 12345.  I'd also like it to use wildcards eg 1234512AR%%% might bring back 1234512AR000 or 1234512AR001.

I hope this makes sense. (to put some context around it - if you hadn't already guessed it's the beginnings of a basic document archiving system)
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 39227089
Hmmm
It would be much easier if you were able to upload your database to look at and correct.

Jaffer
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:littlegembear
ID: 39227147
Here you go. Apologies if it's not up to the standards you are used to - I'm still a newbie!

Thanks
Database1.accdb
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 39227263
Sorry littleGem, I didn't wait for your database, and did my own :(

Here is how to test it: open the Documents Form, where you will see DocumentsSubform:
please select these values from the Main Form:

Doc_Type=a  (later change it to aa)
Team=b
Cupboard=c
Box=d

Please don't enter anything in the Reference field yet, just select as I advised earlier,
you should get 3 Records.
Now change:
Doc_Type= aa

you should get 1 Record.

use Doc_Type= a , where you will get 3 Records again,
now in the field Reference, insert 123
you will get the same 3 Records without change,
now insert 1234
you will get 2 Records ONLY :)

 hope this is what you were looking for :)

what I did was, master and child relationship remained as:
Link Master field: Doc_Type, Team, Cupboard,Box
Link child field: Doc_Type, Team, Cupboard,Box

but for Reference, I made the subform FILTER it's data based on the Selected Reference value, like this (the event is afterupdate of Reference):

Private Sub Reference_AfterUpdate()

    If Len(Me.Reference & "") <> 0 Then
        Me.DocumentsSubform.Form.Filter = "[Reference] Like " & Chr(34) & Me.Reference & "*" & Chr(34)
        Me.DocumentsSubform.Form.FilterOn = True
    Else
        Me.DocumentsSubform.Form.FilterOn = False
    End If
    
End Sub

Open in new window


and again, sorry I didn't apply it to your database :(

jaffer
Main-Sub.mdb
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 39227274
Remember to use the Reference as the last field for filtering the subform :)

jaffer
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 39227336
and here is your database corrected :)

jaffer
Database1.accdb
0
 

Author Comment

by:littlegembear
ID: 39227377
Hi,

Thanks for doing this. It's kind of along the lines of what I wanted, however there's two key criteria missing:

1. I'd like to start off with the full recordset displayed, and then as search criteria are entered, the list should be filtered then. (either on after update, or through a command button)

2. The partial search doesn't work as I expected. If I just type in 12345, I would expect to see two rows of data returned from your sample database.

I'm sorry I'm being really picky, but I've got a tough crowd of people to please in the office!!

I really appreciate all your help so far.

If it's any help, I did previously get this working in a similar database through applying filters and using if / then statements. Unfortunately I'm kicking myself now, as I didn't take my creation with me when I left my old job :(
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 39228268
Here you go :)

jaffer

ps, instead of using the backspace/delete to clear one of the 5 fields on the main form, try double clicking that field :)
Database1.accdb
0
 

Author Comment

by:littlegembear
ID: 39228335
Thankyou. The reference search is great, and I love the double click to delete feature.

Just one last thing - I'm not sure the combinations are working quite as I'd expected. For example if I wanted to know what "Quotes" are in "Cupboard 1" for "D&O" team, or what's in "Cupboard 2" for the "PI" team is this possible? I'd like the filter to work on any combination of the Reference and the 4 combos if possible?

Thanks
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 39228345
The filter works on the 5 fields,  individually or combined.

Jaffer
0
 

Author Comment

by:littlegembear
ID: 39228525
Hi,

Please see screenshot with additional test data. Under the parameters I put in, I would only expect to see row ID 3 returned. It looks like the filter is = Team "or" Box, whereas I would like = Team "and" Box. The "and" combination should apply across all 5 boxes if possible.

Thanks
Filters.PNG
0
 
LVL 27

Accepted Solution

by:
jjafferr earned 500 total points
ID: 39228639
sorry :(

Here is the corrections :)

jaffer
Database1.accdb
0
 

Author Closing Comment

by:littlegembear
ID: 39228681
Amazing - you have just saved me many hours of work. Thankyou so much :)
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 39228711
You are welcome :-)
Man you don't sleep,  do you :-)
Thanks for points and the grade :-)

Jaffer
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question