VBA to Open Form first and then apply filter once it has opened

[kevnaff]

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:

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]

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]

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.