Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Filter Subform based on text box and combo boxes

Posted on 2013-06-06
15
Medium Priority
?
1,486 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
Industry Leaders: 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!

 

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 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

730 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