Solved

Filter Subform based on text box and combo boxes

Posted on 2013-06-06
15
1,301 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
  • 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now