VBA to Turn Filtering Off for Subform

I'm using MS Access 2003.

I have a form containing a subform control sub1 whose Source Object is frm1. I also have (on the same form) another subform control sub2 whose Source Object is frm2. Both subforms display in Continuous view.

Subform sub2 offers the user the ability to filter its list based on the record clicked in sub1. But when the user clicks on a different record in sub1, I want filtering removed from sub2.

So I'm trying to figure out the syntax to turn filtering off for frm2 from within the OnCurrent event of frm1.

Can anyone help with this?

Thanks for any help you can give.


Post your answer or comment

comments powered by Disqus

I have a multi-user database sat on a shared drive. It has upto 10 concurrent users.


both forms use the same underlying query for their data

The Registration form always opens in add mode expecting you to enter a new student. It also has a combo box on it that will allow you to call up any previously entered student.

This form records basic information only.

Student_number (primary key), Name, DOB, Course, Address, telephone number, reason for appointment, and Archive

The archive field is a yes/no check box

If a student has had no contact for 12 months
then archive = true

Form 2 -- SPECIAL_NEEDS is much larger (6 tabbed pages) but also shows through the data entered on form 1

Usually form 2 opens without any filter. the client is selected from a combo box and all works well.

The combo box on form 2 is populated from a sub query that only shows current students .ie.. archived = false
This keeps the number of current/active records to a minimum and speeds up the whole process.

However. if the student looked up is not in the list then it automatically opens the REGISTRATION where you can look up from the full list and uncheck the archived box or enter a new record.

What I am trying to do and am having difficulty with is in returning to form 2 and having it open with the currently selected record from form 1

NO that is not quite true --- I can open form 2 with the current record but then the form is locked with that record... the filter button on the toolbar is highlighted and you cannot now select another record from the combo box on form 2.

If you click the filter button ei. turn the filter off then you can select from the combo box.

What I want to do is edit the current record and then turn the filter off with code rather than manually.

I assumed that the "on focus" event of the combo box was the logical place to put some code but I this is where I come unstuck.

I've tried everthing I can think of. I am sure I'm nearly there but getting something wrong. I tried using the DoCmd.ShowAllRrecords action which does free up the main form but blanks out all values from the combo box at the same time.

How can I turn of the form filter with code and in which event do I put it?

Hope this all makes sense to you

Thanks Sprocket

I would like to have a module with function that would turn on the Caps Lock if it is turned off. Have anyone an idea how to do that?

Thans for any help!

Hi all ... I posted a detailed description last night under the General topic (thinking more folks would see it there, but no luck).

Anyway... for reasons explained in the General Topics note, I want to use a macro to copy a MasterNewEntry table for each user on a server that can be passed on to other Access dbs for processing (similar in concept to the Excel-process I am basing this application on).

For example the field sales reps filled out an Excel workbook, emailed it to the sales admin asst at headquarters, she made changes that the workbook didnt/couldn't store (e.g., historical costs needed for the contribution margin calculations), she prints out the hardcopy for review, routes it for mgmt approval etc.

So the idea is that when a field rep submits a new spending proposal in Acess2K, the MasterTable is copied and renamed based on a unique ID based on the Dealer name, and unique logon/autonumber and date of submission.

Can this be done via a macro? Or do I need to dig into VBA?

Thanks in advance .... TGH

I've got a report and I don't want the Page Header to displayed on the page the group header is printed on. (My group header is about the whole first page of a particular group.)
I've seen where you can turn off page headers for report header pages. What's the best way to turn them off for group headers? I'd like to use VBA to turn them off on the on format event for the group header and then back on using the on format for the detail. Does that sound about right?



I've been searching around the forum and the internet for ideas and/or solutions to this problem. Can't find any that work with my particular situation.

I have an append query. It is pulling data from a linked table through ODBC.
I unforunately can not have a local table joined to this linked table, or using another table/form as a control for the criteria.

As of right now, I just need to update the criteria on one field in the append query: Account Numbers.
The source for these account numbers is on a local table: AccountsForForms. This is populated by user selections in a seperate form.

I'm getting errors on both of my attempts at using a command button and VBA to udpate the criteria for account numbers in the append query.

Here is my first attempt:

It will generate the error "Object required" on the highlighted section.

Code: Private Sub Command0_Click() Dim strSQLQ As String Dim strACNMBR As String DoCmd.RunSQL "DELETE * FROM AllData" DoCmd.RunSQL "DELETE * FROM SummaryType1" DoCmd.RunSQL "DELETE * FROM SummaryType2" 'AllData is the main table where the append query is sending the data 'SummaryType1 & SummaryType2 are tables being populated based on queries summarized types of data stored in the table: AllData strACNMBR = Table!AccountsForForms!ACCOUNTS strSQLQ = "INSERT INTO MyAppendQuery (AC_NR) SELECT [MyLinkedTable]![AC_NR] FROM [MyLinkedTable] WHERE ([MyLinkedTable]![AC_NR]= " & "'" & strACNMBR & "'" & ")" CurrentProject.Connection.Execute strSQL DoCmd.OpenQuery "MyAppendQuery" Beep DoCmd.OpenQuery "SummaryType1Query" DoCmd.OpenQuery "SummaryType2Query" Beep [Forms]![DataSelection]![Type1SummarizedData].Requery [Forms]![DataSelection]![Type2SummarizedData].Requery Beep MsgBox "Process Finished ", vbInformation End Sub

After doing some researching on here and google, I read up on the QueryDefs function and tried to get an understanding of this works.

This is what I came up:

It will generate the error "Object required" on the highlighted section.

Code: Private Sub Command0_Click() Dim db As DAO.Database Dim AccountTable As String Set db = CurrentDb Dim QueryAll As DAO.QueryDef Set AccountTable = db.OpenRecordset("SELECT [ACCOUNTS] FROM AccountsForForms") Set QueryAll = db.QueryDefs("MyAppendQuery") QueryAll.Parameters![AC_NR] = AccountTable QueryAll.Execute dbFailOnError End Sub Is this due to in proper reference for AccountTable as a string?

Is there a cleaner or easier way to insert data from a field in a table into a queries criteria? The property fields on both the table, and the linked table match.

Thanks for any suggestions or advice. If this has already been answered, then I apologize. I tried searching and couldn't find exactly what I was looking for.

i am playing with audit trails and am wondering what to hang code off for when the database closes down. i dont want to have to hang it off a form if i can help it as i like to open and close forms with code as users jump back and forth so you dont see one form under a smaller one. also i would like it to work regardless of how the database is shut down (altF4,x boxes,buttons etc)

incidently, is there a similar one for opening the database. nominating a startup form and using the onopen works ok, but i would like to be able to run code even if the old left shift trick is applied.

another one while we are at it with weird questions, i know you can deactivate the shift key on the startup advanced options, but what if i want to get into the design side again?? how do i get in?

yours in rapid anticipation...

Mike C

I am trying to send e-mails to customers in a batch with my Access database. I have created a recordset with the desired document id's and I have put a sendobject statement in a loop of this recordset.

	DoCmd.SendObject acSendReport, "rptInvoice", acFormatPDF, email, , , subject, msgText, False

The problem is, the report, wich is the invoice, needs to be filtered separately for each e-mail / pass of the loop. The only way I can think of to do this in code is to open the report, apply a filter, and save the report. Not sure if this will work or not but if it does it will cause the report to flash on the screen each pass of the loop. Can anyone direct me as to how to e-mail invoice reports in a batch, each one filtered for it's intended recipient?

I use the Access 2007 runtime to run my Access database on a few client machines. How do I turn off the confirmation of Action Queries? On clients that are also running MS Access, I can simply open Access, go to Access Options and uncheck the box. When I make this change in Access, the same settings apply for the Runtime on that computer. Which makes me think perhaps there is a registry entry I can modify someplace? If anyone has an idea as to how to turn this off for the runtime please let me know.


Hello there..
Is there a VBA for on click modifying all the filtered data in a subform ?

Me!Variable = False --- ( Alll the variables filtered in subform convert them to false? ).

Thanks in advance.


I have a form where I have users enter text into a text box then press a button that filters the form based on the text entered into the box. When the user presses the button, the filter is applied as expected but the filter is also being applied to the subform which is not intended. To "fix" this I use the following code to turn off the filter on the subform.

Private Sub TFilterBtn_Click()
Me.Filter = "[DocumentTitle] LIKE" & Chr(34) & "*" & TitleFilterbx.Value & "*" & Chr(34)
Me.FilterOn = True
With RevisionSubF.Form
'.Filter = vbNullString
.FilterOn = False
.OrderBy = "[RevisionSubF].[Superseeded] DESC"
.OrderByOn = True
End With


End Sub

Is there a way to only have the FilterOn = True apply to the main form and not the subform so that I don't have to use code to clear the filter for every subform and redefine the OrderBy which gets cleared during the process?

Thanks in advance.

I would like to know if there is a VBA to apply a value on all the fields that are filtered in a subform..
For a better understanding I took a screenshot and made a detailed explanation on it.

Okay so 2 problems, first I want to apply a filter to a bound form that shows the dates that lie within a specific range. That range is the current year, current month and any day. I can't figure out the correct VBA for it, below is what I have.

DoCmd.ApplyFilter , "[DateEnd] = Between DateSerial(Year(Now()),Month(Now()),1 And DateSerial(Year(Now()),Month(Now()),31"
My second problem is also with filters, I have a button on my form that asks the user to enter a customer name to filter by. A customer name is entered and the filter works fine. But after I toggle to filter off and want to enter a different customer name access seems to keep what I entered first in its memory and will just filter on what value only. I get no prompts to enter a different value, it just holds onto the first value until I close and reopen the form.

How do I fix these 2 things?!?! Any help is very appreciated. Thanks!

Dear fellows,

I'm working with a project using Access 2002 and VBA and I'm pretty new on it.

Each time I open up a table and close it, it will auto commit the update I made on it no matter it's what's I want or by accident.

Do you guys have any advices how to turn off the auto commit feature? any utility can do that? Or I need to refer to a procedure? Thanks for your help.


I am trying to build a reusable subform to contain the field controls needed for the "Last Edited By / On" capabilities for my application.

Since I pass a reference to the form (Me) to the class which populates the forms, I end up populating the fields with very different syntax than I have seen suggested for SubForm access.

My working form populate code looks like the following sample:

	  MePointer.fldprojecttitle.Value = ObjProductsTbl.projecttitle
  MePointer.fldtitle.Value = ObjProductsTbl.title
  MePointer.fldproductnumber.Value = ObjProductsTbl.productnumber
  MePointer.fldbomcad.Value = uiutils_TruncateBOMData(ObjProductsTbl.bomcad)

MePointer is a pointer reference to the form object. I passed in (Me) and received it via a ByRef variable. Works slick!

So since the UserName / LastSave fields have moved to a subform, that seems to throw a wrench into populating the fields.

How would I get to a consistently named subform ("subform_lastsaved") on that form to populate two known name controls?

The code below works erratically to display filtered data. Combo57 is a dropdown list to display the projects assigned to a staff person. When you click on the Combo57, it correctly shows the assignments in List1. It also correctly highlights the first record in List1. However the other text boxes on the form do not display the data for the first record from List1. Now if I click on Combo57 a second time - the correct data is displayed. Also the correct filtered data is displayed if I click on Combo57 immediately after clicking on Command59. So the correct data will display in certain situations.

	Private Sub Combo57_Click()
    Rem Select Filter
    Me.List1.RowSource = "SELECT [mpidnum], [projectname], [received], [permcoord] FROM Majorpermitsqry WHERE [permcoord]=" &
Me.Combo57.Column(0) & " AND [statusnum]

I am sure this can be answered by searching but i do not know what to search for.

Imagine I have two columns. I want to first filter according to one column and then show in the combobox the filtered result from the second.

But I want to do this with VBA code.

Could you please point me in the right direction, even if it is the right words to search.

I have a cmd button on my form that has this code attached to it:

	Forms!frmClients.Form.Filter = "[Date Last Accessed]


I have a simple Invoice database that I have converted to open in runtime. When creating invoices, I run an append query, then a form for printing options opens up. Except in the runtime the form doesn't open, instead warnings regarding the append query appear, even though I have turned them off in my Access before creating the runtime version. And after those warnings, no form opens up.

Anyone know about this? Help!!



I apologize for the caffeine not kicking in...

I have a form based on a query that filters a series of vacation records. There's a separate field that tells the database what year to charge the vacation to (just in case people use 2011 vacation hours in 2012 or vice versa). What I want to do is use a check box to let people filter only the records charged to the current year vs all records from past years. As you might suspect, it isn't working. The check box, when on, seems to work. But when it's unchecked, no records show at all. The criteria is...

	IIf([Forms]![frmEnterEmployeeVacationTime]![filteron]=True,DatePart("yyyy",Date()),([tblVACATION LOG].[Charged to What
Year]) Like "*")

Any suggestions? Thanks

Ok guys what im after is if this is even possible and how would be the best i should go about doing the following.

What i am trying to do is I have a query that will have a datediff field as well as a up to 4 total values

so its qryCOMPARISON: DateDiff, Amount1, Amount2, Amount3, Amount4

i know so far it doesnt sound like a form question but the part i need help with is how is the best way for me to design a form where the user can choose up to 4 different filters off a subform. I dont particularly want 4 different subforms for the same option as it looks ugly. lol

any ideas would be great


Hi all, I have created a form which searches and displays results in a subform. The search works perfectly and I can search on any combination of fields. However, on opening the form or pressing the clear button, all records are shown in the subform. I would like to change this so the subform is empty when the form is opened or the clear button is pressed. I gather I need to change the code under 'btnClear_Click()' as this also clears the search boxes on form opening, but I'm not sure how.

Can anyone help? Here's the code:-

	Option Compare Database
Option Explicit

Private Sub btnClear_Click()

    Dim intIndex As Integer
    ' Clear all search items
    Me.txtFirstName_Initial = ""
    Me.txtSurname = ""
    Me.txtBusinessName = ""
    Me.txtEmailAddress = ""
    Me.txtHouse_FlatNumber = ""
    Me.cmbStreet = ""
    Me.cmbArea = ""
    Me.cmbTown_City = ""
    Me.cmbCounty = ""
    Me.txtPostCode = ""
    Me.cmbStatus = ""
    Me.cmbSalesperson = ""
    Me.fsubCustomerSearchDetails.Form.RecordSource = "SELECT * FROM qselCustomerSearchDetails;"


End Sub

Private Sub btnSearch_Click()
    ' Update the record source
    Me.fsubCustomerSearchDetails.Form.RecordSource = "SELECT * FROM qselCustomerSearchDetails " & BuildFilter
    ' Requery the subform
End Sub

Private Sub Form_Load()
    ' Clear the search form
End Sub

Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim intIndex As Integer

    varWhere = Null  ' Main filter
    ' Check for First Name/Initial
    If Me.txtFirstName_Initial > "" Then
        varWhere = varWhere & "[FirstName_Initial] LIKE """ & Me.txtFirstName_Initial & "*"" AND "
    End If
    ' Check for Surname
    If Me.txtSurname > "" Then
        varWhere = varWhere & "[Surname] LIKE """ & Me.txtSurname & "*"" AND "
    End If
    ' Check for Business Name
    If Me.txtBusinessName > "" Then
        varWhere = varWhere & "[BusinessName] LIKE """ & Me.txtBusinessName & "*"" AND "
    End If
    ' Check for Email Address
    If Me.txtEmailAddress > "" Then
        varWhere = varWhere & "[EmailAddress] LIKE """ & Me.txtEmailAddress & "*"" AND "
    End If
    ' Check for House/Flat Number
    If Me.txtHouse_FlatNumber > "" Then
        varWhere = varWhere & "[House_FlatNumber] LIKE """ & Me.txtHouse_FlatNumber & "*"" AND "
    End If
    ' Check for Street
    If Me.cmbStreet > "" Then
        varWhere = varWhere & "[Street] LIKE """ & Me.cmbStreet & "*"" AND "
    End If
    ' Check for Area
    If Me.cmbArea > "" Then
        varWhere = varWhere & "[Area] LIKE """ & Me.cmbArea & "*"" AND "
    End If
    ' Check for Town/City
    If Me.cmbTown_City > "" Then
        varWhere = varWhere & "[Town_City] LIKE """ & Me.cmbTown_City & "*"" AND "
    End If
    ' Check for County
    If Me.cmbCounty > "" Then
        varWhere = varWhere & "[County] LIKE """ & Me.cmbCounty & "*"" AND "
    End If
    ' Check for Post Code
    If Me.txtPostCode > "" Then
        varWhere = varWhere & "[PostCode] LIKE """ & Me.txtPostCode & "*"" AND "
    End If
    ' Check for Status
    If Me.cmbStatus > "" Then
        varWhere = varWhere & "[Status] LIKE """ & Me.cmbStatus & "*"" AND "
    End If
    ' Check for Salesperson
    If Me.cmbSalesperson > "" Then
        varWhere = varWhere & "[Salesperson] LIKE """ & Me.cmbSalesperson & "*"" AND "
    End If
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
        varWhere = "WHERE " & varWhere
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
    BuildFilter = varWhere
End Function

I have a macro that runs a series of updates. Now that everything is working correctly, I want to turn off all the warnings that the update queries pop up, so that it will run straight through. I can't find the command to use in the macro. Thanks!!

I have a problem filtering records in my subform using a ComboBox in the Main Form. I have tried options proferred in other forums and have still not git the desired result. I know I must be missing something as this looks quite simple.

I have attached a screenshot to help.

What I need to for the records to be filtered by the Section Field using either of the three options in the ComboBox. The ComboBox looksup data from a Sections table (SectionID, Section).

Main Table Name: Orders (Master Link: Order ID)
SubForm Table Name: Order Details (Child Link: Order ID)
ComboBox Object Name: CboFilter

I will appreciate any help. Attached Thumbnails   Reply With Quote 05-24-2009, 05:40 AM #2 RuralGuy Administrator Windows 7 64bit Access 2010 32bit Join Date Mar 2007 Location 8300' in the Colorado Rocky Mountains Posts 9,484 Have you included the CboFilter ComboBox in the RecordSource query for the SubForm as a Criteria?

Hello there..
Is it possible to send values from a button on a form to a subform data?
For example, If I mark data from a checkbox inside the subform, then I click on button on the form.. it will check for True values on checkboxes I just marked, if it is true, then it will write a number from a value in fieldbox on form.
Form: Products
Subform: ExpiredProducts
Field on "Products" Form, Number of expiration.(It will automatically generate).
Then I mark on cheboxes inside "ExpiredProducts" subform.
Then click Update Button on "Products" form, that will check for True values (Data I just marked). If there are true values, then it will automatically add the "Number of expiration" to a column from the subform. and Mark other checkboxes.

I hope I made myself clear.
Thanks in advance.

Not finding an answer? Try a Google search.