Filter Lookup Code for form's Text Box Control

F

Felix314

Registered User.
Local time
Today, 00:48
Joined
Jan 9, 2013
Messages
22
Hello,

I am trying to spare users from finding the filter button on the toolbar window for a text box control on a form. Instead I would like the action to be handled on the form itself.

The name of the control is "Semester". There are three semesters (Spring, Summer, Fall) and each semester also has a year in it (2009 - 2012).

I would like the user to be able to either click the control and have the option to set the filter for a specific semester or to click on a separate button which could all for the same filter action. Basically I am hoping for the form to be able to reprodue the action occured by clicking the traditional "Filter" button on the main Access "Home" toolbar.

Is there a VBA code which will allow for this action?

Thank you for any help. It is much appreciated :)
 
Thank you JBB.

So I looked at the coding on the "option group" discussion and I tried it out. I created an option group for the "Semesters" field and put this code in for the "On Click" Event:

Private Sub Semester_Click()
Dim strFilter As String
Select Case [Semester]
Case 1
strFilter = "[Semester] = 'Spring 2009'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True

Case 2
strFilter = "[Semester] = 'Summer 2009'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True

Case 3
strFilter = "[Semester] = 'Fall 2009'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True

Case 4
strFilter = "[Semester] = 'Spring 2010'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True

Case 5
strFilter = "[Semester] = 'Summer 2010'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True

Case 6
strFilter = "[Semester] = 'Fall 2010'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True
Case 7
strFilter = "[Semester] = 'Spring 2011'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True

Case 8
strFilter = "[Semester] = 'Summer 2011'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True

Case 9
strFilter = "[Semester] = 'Fall 2011'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True

Case 10
strFilter = "[Semester] = 'Summer 2011'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True

Case 11
strFilter = "[Semester] = 'Fall 2011'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True

Case 12
strFilter = "[Semester] = 'Spring 2012'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True

Case 13
strFilter = "[Semester] = 'Summer 2012'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True

Case 14
strFilter = "[Semester] = 'Fall 2012'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True
End Select
End Sub

Unfortunately, this doesn't do anything because when I click on an option value in the form, it says "This Recordset is not updateable"
I know there are threads out there which discuss this, and I have looked at them, but none are referring to an ADP project. My form is based off a union query view. Is that the reason I cannot click on an option? My Recordset Type for the form only allows for "Snapshot" and "Updateable Snapshot".

Could really use some specific help. Thank you so much!
 
Oops, I meant..

Private Sub Semester_Click()
Dim strFilter As String
Select Case [Semester]
Case 1
strFilter = "[Semester] = 'Spring 2009'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True

Case 2
strFilter = "[Semester] = 'Summer 2009'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True

Case 3
strFilter = "[Semester] = 'Fall 2009'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True

Case 4
strFilter = "[Semester] = 'Spring 2010'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True

Case 5
strFilter = "[Semester] = 'Summer 2010'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True

Case 6
strFilter = "[Semester] = 'Fall 2010'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True
Case 7
strFilter = "[Semester] = 'Spring 2011'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True

Case 8
strFilter = "[Semester] = 'Summer 2011'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True

Case 9
strFilter = "[Semester] = 'Fall 2011'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True

Case 10
strFilter = "[Semester] = 'Spring 2012'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True

Case 11
strFilter = "[Semester] = 'Summer 2012'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True

Case 12
strFilter = "[Semester] = 'Fall 2012'"
Forms!Agency_University_Certificates.Form.Filter = strFilter
Forms!Agency_University_Certificates.Form.FilterOn = True
End Select
End Sub
 
Are you able to post a copy of your DB.

Your code will need to be a little more flexible that what you have currently presented if you want it to have any longevity.
 
Yes, I have attached a copy. It is the form and data copied over from an adp file to an accdb file.


The code works fine now actually. In the sense that it will filter by semester when an option value is clicked on. But yes, you are right, it certainly needs to be more flexible.


Hopefully you can give me some good advice for this form. I'm also having a bit of trouble with a cmdFind button.

After I click an option value to filter the form, i cannot seem to use the find button I put on the form. An error pops up saying "MS Access can't move the focus to the control "OptionValueName"". What's strange is this only happens if I click on the button immediately after I select the filter.

Thank you tremendously for your help JBB
 

Attachments

I'll check it out latter in the day when I have a compatible version of Access. Only '03 at present.
 
OK, I've had a look at the DB, and I think the very first thing that needs to be done before we even tackle this issue is to Normalise your Table structure. Having done that this should become a significantly easier task to tackle.
 
Sorry, in my haste, I forgot to include my two master tables and the relationships between primary and foreign keys.

I have updated it now... I assume it is normalized fine?
 

Attachments

Jeez sorry again, ignore that one. I didn't make the primary keys for the course tables.

Here it is.
 

Attachments

I'm sorry but I think that table structure still needs a lot of work. I'm not going to get a chance to have a real good look at it today, but I'll try and mock something up over the weekend.
 
Ok, well that is good to know. Appreciate your guidance and time JBB :D

I'm eager to see what you come up with.
 
You must log in or register to reply here.

Similar threads

A
Creating a Sort combo box
Replies
3
Views
467
The_Doc_Man
The_Doc_Man
G
Open Filter Dialog box
Replies
12
Views
600
LarryE
LarryE
M
Open Unbound form and filter subform
Replies
7
Views
286
Gasman
Gasman
O
Solved Returning to a Filtered Form
Replies
4
Views
259
Odeh Naber
O
A
Solved In a continuous form : Disable / lock a textbox on a specific record on clicking a button within that record. Any way it can be done ?
Replies
6
Views
193
alan2013
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom