Link to home
Start Free TrialLog in
Avatar of SparkyP
SparkyP

asked on

Correct code placement

I want to open a form, JobReports, from another form.

I have added a command button with the following code to filter records, but it does not seem to load efficiently.

I am putting the code in the correct place?


Private Sub OpenJobReports_Click()
DoCmd.OpenForm "JobReports"
Forms![JobReports].Filter = "[Status] = " & Chr(34) & "Completed" & Chr(34) & " OR  [Status] = " & Chr(34) & "In progress" & Chr(34)
Forms![JobReports].FilterOn = True
DoCmd.GoToRecord , , acLast
End Sub
ASKER CERTIFIED SOLUTION
Avatar of Helen Feddema
Helen Feddema
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Norie
Norie
When you open 'JobReports' code execution might be getting passed to it.

If that's happening the next line of code that filters the form won't be executed.

Where are the fields you want to filter on located?

If they are on the form the button is on you could try this.
strFilter = "[Status] = " & Chr(34) & "Completed" & Chr(34) & " OR  [Status] = " & Chr(34) & "In progress" & Chr(34)

DoCmd.OpenForm "JobReports,,,strFilter

Open in new window

Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image
If you open the JobReports form directly, does it load "better"?
Avatar of SparkyP
SparkyP

ASKER

Helen

Your filter suggestion works fine, and best of all I understand why!!

LMS
JobReports opens much more quickly when opened directly, with the code in place on the open event.

My issue is that I have other events that open specific records in JobReports, therfore putting it on the on open event isn't good.
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image
My issue is that I have other events that open specific records in JobReports, therfore putting it on the on open event isn't good.
I don't quite understand what you mean by this. You haven't mentioned any code in the Open event, so I'm not sure what you're talking about. The code you reference in your post is code that OPENS the form, but is not the Open event of the form.

So you'll have to explain to use a bit more what you mean, and what you're after.
Avatar of SparkyP
SparkyP

ASKER

LSM

Sorry.

Currently I have nothing in the open event of the form, but wanted to eliminate that that from the solution.

I want the command button on the first form to open the form, filter and go to the last record.


Noting More
Avatar of SparkyP
SparkyP

ASKER

My mistake

Got events in wrong order.

All part of my very steep learning curve!!

Thanks for your quick response
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image
I want the command button on the first form to open the form, filter and go to the last record.

The your code will certainly do that, but you also mentioned it was not "efficient".

So your question is "is this the most efficient way"? Given your restrictions of not using the Open event it's about the ONLY way. You could perhaps do this:

DoCmd.OpenForm "JobReports"
Forms![JobReports].Filter = "[Status] ='Completed' OR  [Status] ='In progress'"
Forms![JobReports].FilterOn = True
Forms("JobReports").Recordset.MoveLast

But I doubt that would be more efficient, since it accomplishes the same thing.

You might try the OrderBy feature:

DoCmd.OpenForm "JobReports"
Forms![JobReports].Filter = "[Status] ='Completed' OR  [Status] ='In progress'"
Forms![JobReports].FilterOn = True
Forms!JobReports.OrderBy = "SomeField DESC"
Forms![JobReports].OrderByOn  = True

Where 'SomeField" would be the field you'd use to determine the "last" record (perhaps a Date field?).