VBA to Open Form first and then apply filter once it has opened (1 Viewer)

kevnaff

Member
Local time
Today, 17:53
Joined
Mar 25, 2021
Messages
125
Hello All.

I have a form called AssignmentLedgerRead2, that has around 110 thousand records in its record source. Therefore I have a filter on the form's On Open event, which filters only by the staff's non-completed assignments.

I currently have the following code to open the Assignments form and apply a filter:

Code:
On Error GoTo Err_CommandJobs_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "AssignmentLedgerRead2"
    If IsNull(TextJobNumber) Then GoTo NoJobNo
        
    stLinkCriteria = "[Job No]=" & Me![Job Number]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Exit Sub
        
NoJobNo:
    DoCmd.OpenForm stDocName, , "NextJob"
    

Exit_CommandJobs_Click:
    Exit Sub

Err_CommandJobs_Click:
    MsgBox Err.Description
    Resume Exit_CommandJobs_Click


When this command button is clicked it opens the AssignmentLedgerRead2 form, and applies the form's On Open event. Once the form is open if I then click on the command button again, it filters by the [Job Number] which is what I need.

However is there a way to open the form, and then apply the filter once the form is open?

This would allow the filter to be applied correctly first time around.

Thanks for your help.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:53
Joined
Feb 19, 2013
Messages
14,686
filtering after the form is opened is going to be the slow way of doing things because in order to filter all 110,000 records need to be loaded. Yes you may see the filtered records first, but you won't be able to do anything until all records have been loaded.

I don't understand your code - what is textjobnumber? shouldn't that be me.[job number]?

recommend you do not use spaces in field or table names - can cause issues down the line.

I suggest better to apply a criteria then only the relevant records will be loaded. This can be achieved by:

1. applying your stLinkCriteria to the openform openargs parameter rather the the where parameter
2. modify your form recordsource to return no records (i.e. instead of a recordsource of 'myTable', use 'SELECT * FROM myTable WHERE False')
3. in the form open event have the code
me.recordsource=replace(me.recordsource,"False",me.openargs)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:53
Joined
May 7, 2009
Messages
16,779
you can also check this.
bring your form (AssignmentLedgerRead2) in Design view.
on it's Property Sheet->Data->Filter On Load should be set to Yes.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:53
Joined
Feb 19, 2002
Messages
36,837
Filter always loads the entire recordset and then filters.

It is far more efficient to use a query with criteria. That way only the selected records get loaded.

Most of the time, the user knows what he wants to look at when he opens a form. In that case, I use a textbox or a filtered combo (if there are a lot of records) on the form. The recordsource query of the form uses the textbox or combo as criteria and so always opens "empty". Then the user either types into the textbox or uses the filtered combo (the user has to type 3/4 characters before the combo's RowSource loads to reduce the number of records in the RowSource.

In the cases where the user is searching for something, I use a search form and the code behind the form builds a Where clause. It starts with a dCount to determine how many records are selected. If the answer is 1, then the single record form is opened to the selected record. If the answer is "too many", I ask the user for different criteria or to override and expect slowness. Then I open a list form. The user can filter it more if he wants and when he double clicks on a row, the single view edit form opens.
 

Users who are viewing this thread

Top Bottom