?
Solved

Using an Option Box to filter a SubForm

Posted on 2011-04-22
11
Medium Priority
?
651 Views
Last Modified: 2013-11-28
I have created a form to search for records in my database.  I am currently using a combo box to select the employee name and an option group to select record status, but I cannot seem to get a filter to work to filter by record priority.  The employee name combo box is bound, and is linked to the subform with a master/child link, and works perfectly.  The option box has a case select set up that changes the record source of the child to different subforms based on queries that fit the requested status priority (I could not get the filters to work here either).  This is fully functional for me also, except that I want to be able to filter the subform for priority also.  If I use a bound combo box with another master/child link I can effectively filter, but I can't figure out how to add an option to the drop down list that will make all records available again.  Using the case select on the option group gives me the ability to remove all filters as my last option, but since I can't seem to make the filters work it's irrelevant.  I'm not proficient in any kind of code, but none of the snippets I have been able to find have worked.  Please help me figure out how to filter these records...
0
Comment
Question by:djanoian
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 35451279
You said>
I am currently using a combo box to select the employee name and an option group to select record status, but I cannot seem to get a filter to work to filter by record priority.  The employee name combo box is bound, and is linked to the subform with a master/child link, and works perfectly.

Which confuses me:
If the employee name combo is bound, then choosing an name from the list does NOT select the employee by name, it changes the name of the currently selected employee.  Please clarify.

You said>
 The option box has a case select set up that changes the record source of the child to different subforms based on queries that fit the requested status priority

Which also confuses me:
Does the option box change the record source or the subform?  
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35454415
What you want is complex enough that you need to post a sample.
Building, applying, removing and reworking filters is some of the more difficult stuff there is to code

< I'm not proficient in any kind of code>
<changes the record source of the child to different subforms >

That makes me wonder how you are getting stuff to work now.
Post something for us to work with, and detail on just what you want to have happen
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 35457286
Another comment:  the Master-Child link would be between the main form and subform -- not a combo box.  Not sure what you mean by the combo box being linked to the subform.  You can use an unbound combo box as a record selector (generally in the form header), using code like that below.  As others have mentioned, a bound combo box just changes the value of a field in the current record.
Private Sub cboSelect_AfterUpdate()
'Written by Helen Feddema 29-Jun-2009
'Last modified 11-Feb-2010

On Error GoTo ErrorHandler

   Dim strSearch As String

   'For text IDs
   strSearch = "[______ID] = " & Chr$(39) & Me.ActiveControl.Value _
      & Chr$(39)

   'For numeric IDs
   strSearch = "[______ID] = " & Me.ActiveControl.Value

   'Find the record that matches the control
   Me.Recordset.FindFirst strSearch

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:djanoian
ID: 35471668
Okay, perhaps I am confused...  I have a Combo Box in the Detail section of my main form that has a control source of "Assigned To" (which is one of my fields).  My subform, currently named Child38, shows a Master/Child link on this field.  The only Event data that I have on this combo box is an embedded macro to Show All Records After Update, but when I select a different name I get only records assigned to that name as I want.
My option group has an event procedure After Update that changes the Control Source for Child38, effectively changing it among five different subforms.  Each subform contains the info I want; one is complete, the other four are based on queries to show me records with only a specific status.  I am using a Select Case for this, as follows:

 
Private Sub StatusFilters_AfterUpdate()
Select Case Me.StatusFilters
Case 1
Me.Child38.SourceObject = "IT_subTicket Not Started Form"
Case 2
Me.Child38.SourceObject = "IT_subTicket In Progress Form"
Case 3
Me.Child38.SourceObject = "IT_subTicket Waiting Form"
Case 4
Me.Child38.SourceObject = "IT_subTicket Closed Form"
Case 5
Me.Child38.SourceObject = "IT_subTicket Detail Form"
End Select
End Sub

Open in new window


What I want now is to be able to filter Child38 (regardless of its control source) using another Option Group or a Combo Box that will filter by Priority but also allow me to remove the filter and view all records provided by Child38.  The option group seems like a great idea, since it's easy to create an "All Tickets" button that would remove any filters on that field, but I can't figure out how to get it to filter.  Using another combo box for this third set works great, except that I can't find a way to make it show all records again after I've selected a value for the field once.
 I've attached a screen shot of my form - hopefully that will be enough to provide some enlightenment - but if necessary I can make a copy of the database and delete all my records and identifying formatting so I can attach that too.  Please let me know if you can provide more assistance based on this information.
Thank you!
TicketInquiryForm.doc
0
 
LVL 42

Expert Comment

by:dqmq
ID: 35472608
>I have a Combo Box in the Detail section of my main form that has a control source of "Assigned To"

What is the rowsource of the combo box?

>What I want now is to be able to filter Child38 (regardless of its control source) using another Option Group or a Combo Box that will filter by Priority but also allow me to remove the filter and view all records provided by Child38.

If I understand correctly, you would need to put coding to effect the desired filtering on each of the subforms.  You could do it in the Record Source of the sub form, by using a query with criteria that references the option control or combo box on the main form. But, that approach can get a little obtuse.  I'd rather suggest, just change the record source of the subform or apply a filter to the subform.

Does that make sense?








0
 

Author Comment

by:djanoian
ID: 35478294
The row source of the functional "Assigned to" combo box is:
SELECT Techs.ID, [First Name] & " " & [Last Name] AS Expr2 FROM Techs;

If you read carefully, I am already changing the record source of my subform based on an option box - what I need to be able to do next is to filter whichever subform is being displayed based on a third selection on the main form.  Right now, my main form does this:

Combo Box "Assigned To" uses a master/child link to show only subform records with a matching "Assigned To" field.  This works for any of the subforms that are used to populate Child38.

Option Group "StatusFilter" uses the previously mentioned Case Select code to change the control source of Child38 among five different subforms, each based on a different query to match the selection in the Option Group.  This also works beautifully, giving me the recordset I want to see.

I also have a third control on my main form - we'll call it "PriorityX" - that I want to use to filter the results in Child38 (regardless of the source) among four different priority values and an "All Tickets" value that would remove all filters.  This is where I'm running into trouble.  I can't seem to get a macro to work to filter on condition, and I can't get any of the code I've found to work to filter the way I want.  If I use another Option Group, I get either no records or no change in my records.  If I use a combo box after creating a master/child link on Priority, it works perfectly for changing between the priority values but I can't make it go back to the "All Tickets" option which displays everything.  
I really don't want to have to code each subform differently - I'd like to keep the coding either on the main form level, or tied to Child38 so that it affects whatever subform is being used as the source.  Even a painfully simple option would be welcomed at this point; I've been trying to find answers for so long I may simply have over-thought it all and missed the best (and easiest) answer.
Thanks again!
0
 

Accepted Solution

by:
djanoian earned 0 total points
ID: 35479896
Okay, after a little more searching I figured out how to make the filters work from the combo box without creating the master/child link.  Once that was done, instead of assigning Show All Records to the "All Tickets" condition, I assigned a filter that looked for all records Like "*".  This returned all my records, which was my original goal, so I'm all set now.  
Thanks everyone for your input and your attempts to help me with this.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35480782
I am sorry that we could not help you sort your problem out
<What you want is complex enough that you need to post a sample.>

You did not do so, that did not help your cause.

<mine was accomplished with the macro GUI built in to the software>
Virtually no one works with the macros.  They lack the power, flexibilty and troubleshooting power that code does.
And Experts can help you write good, well-documented code you can understand and extend, but you did not give us the opportunity.

You are doing very sophisticated things
<Me.Child38.SourceObject>
And not doing fundamental things like using a proper naming convention.
Or using a bound control, which no expert ever would, as a method of changing and filtering a report.
That makes it very difficult to know how to help you

<I'd like to keep the coding either on the main form level, or tied to Child38 >
But you given us no input on your coding skills or a sample db to work with.

I am sorry we couldn't give you a workable solution--but you didn't give us enough to work with
0
 

Author Comment

by:djanoian
ID: 35485383
I did post the sample code, as requested, and a screen shot of the properties of my combo box since I wasn't sure whether it was bound or unbound - only that it worked.  I also offered to pare down the database to a point where I could post the pieces I was concerned about, but that I would prefer not to take the time if it wasn't necessary.  No one asked me to go ahead and do so.  I understand that code is very powerful, but I've been fighting with this form for a month so when I finally got the filters to work through the macro and then figured out that filtering for all records gave me the desired functionality when removing the filters did not, I was happy just to be done with it.  If it would make anyone out there feel better I can go ahead and clean up my db, post the sample, and let someone help me write code to their heart's content, as long as I can understand what the code is doing and why.  I just figured that since my simple solution did what I needed, I should probably stop wasting everyone's time.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35485524
Things are never a waste of time.  If you have a solution you are  happy with, good enough
<you need to post a sample>
That means a working mdb file, for almost every expert, that demonstrates where you are having grief.
<If it would make anyone out there feel better>
It's not about feeling better, it's about providing you with a good solution--and having you come back to ask something again.
0
 

Author Closing Comment

by:djanoian
ID: 35503429
The experts didn't seem to understand what I was asking, and the one who did get back to me in terms I could understand didn't seem to have read my posts or looked at my screen shot.  I accepted my own answer as the solution because I was able to figure out how to what I wanted on my own while I was waiting for input from the experts.  The solutions they seemed to be leaning towards all involved heavy coding, while mine was accomplished with the macro GUI built in to the software.  It was quicker and cleaner than all that code.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

839 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