Access ADP Strategy needed.
Hi Experts,
I am used to do the following in an Access MDB.
when needed to change multiple subforms record source based on what is filtered in main form.
Have all the subforms be based on a query and change the queries SQL property in code when user selects all the filter options and clicks the filter button.
Now in ADP I am wondering how can this be accomplished?
Just to give an example what I am looking to accomplish.
I have a form that includes 2 sub forms regarding totals of employees records, one is grouped by City and the other by Month hired, and I would like the main form should have the options to filter what kind of employees they are looking for, and the filter should reflect both subforms.
P.S. Perhaps there is a way to change a view's sql by code?
However I thought that could affect other users trying to filter at the same time.
(Waiting to hear from users if its intended to be used by more then one person at the same time..)
So first lets hear from you guys what's the right strategy here..
I am used to do the following in an Access MDB.
when needed to change multiple subforms record source based on what is filtered in main form.
Have all the subforms be based on a query and change the queries SQL property in code when user selects all the filter options and clicks the filter button.
Now in ADP I am wondering how can this be accomplished?
Just to give an example what I am looking to accomplish.
I have a form that includes 2 sub forms regarding totals of employees records, one is grouped by City and the other by Month hired, and I would like the main form should have the options to filter what kind of employees they are looking for, and the filter should reflect both subforms.
P.S. Perhaps there is a way to change a view's sql by code?
However I thought that could affect other users trying to filter at the same time.
(Waiting to hear from users if its intended to be used by more then one person at the same time..)
So first lets hear from you guys what's the right strategy here..
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Experts,
@Scott,
The story here is as follows, I am only planning to use this main form as a container for
1- all the subforms.
2- place all the filter options there, including the filter button.
Now whatever is being filtered on main form should reflect all sub forms.
Being that the case, I don't see why I should use Master/Child linking here, as this will require I include all those filter options as fields in the subforms recordsets, and for the examples above, I dont think they can even be included (lets take for example a total of employees braked down by cities, nothing besides of count(EmployeeID) and City belongs there).
Re your option to use a temp table, I was thinking about that, however wondering if that would not cause a slow in performance, as data would need to be deleted and reloaded for every filter?
@Pat,
Not sure how the fact that everyone has their own copy of FE can help with this case in ADP projects, as there are no local tables or views?
Re ADP being deprecated, see my next comment..
@Bit,
I am glad to see there are other users sharing the same experience I do, that nothing beats ADP (at least in terms of performance, which is the most important here in our company).
I know that solution with events sounds like something that would be suitable in this case, however since I am not so familiar with classes, perhaps this might be challenging for me to step into..
Just wonder if the reason I mentioned above is not relevant, meaning user claims that no more than one person will be using that screen at a time, would you think its okay for me to change the view's sql every time a user filters something (the way I do in Access MDB's for queries/pass-through queries), or from what I understand from your writing, they may be more reasons why that its not advisable?
Re SQL injection, if this is an in-house program, not accessible outside our network, do you think I have to be concerned about that? and why is that worse of what I am currently doing, changing local MDE's queries SQL property in code?
Thanks,
Ben
@Scott,
The story here is as follows, I am only planning to use this main form as a container for
1- all the subforms.
2- place all the filter options there, including the filter button.
Now whatever is being filtered on main form should reflect all sub forms.
Being that the case, I don't see why I should use Master/Child linking here, as this will require I include all those filter options as fields in the subforms recordsets, and for the examples above, I dont think they can even be included (lets take for example a total of employees braked down by cities, nothing besides of count(EmployeeID) and City belongs there).
Re your option to use a temp table, I was thinking about that, however wondering if that would not cause a slow in performance, as data would need to be deleted and reloaded for every filter?
@Pat,
Not sure how the fact that everyone has their own copy of FE can help with this case in ADP projects, as there are no local tables or views?
Re ADP being deprecated, see my next comment..
@Bit,
I am glad to see there are other users sharing the same experience I do, that nothing beats ADP (at least in terms of performance, which is the most important here in our company).
I know that solution with events sounds like something that would be suitable in this case, however since I am not so familiar with classes, perhaps this might be challenging for me to step into..
Just wonder if the reason I mentioned above is not relevant, meaning user claims that no more than one person will be using that screen at a time, would you think its okay for me to change the view's sql every time a user filters something (the way I do in Access MDB's for queries/pass-through queries), or from what I understand from your writing, they may be more reasons why that its not advisable?
Re SQL injection, if this is an in-house program, not accessible outside our network, do you think I have to be concerned about that? and why is that worse of what I am currently doing, changing local MDE's queries SQL property in code?
Thanks,
Ben
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Bit,
First I am attaching a screenshot of the screen in question, so that may give you an idea what I'm talking about.
I am using the top section for placing all filtering options, then I will place in the tab control on the bottom all the subforms that are needed.
just to list a few examples
1) totals of employees by Source
Source
Career Builder 5
Monster 7
2) Totals of Employees by Hire Month
Jan-15 10
Feb-15 20
Mar-15 30
3) Totals of employees by Employee status
Applicant 10
Prospect 11
Now for each of those listed above I would need to design a subform that will have 2 columns in datasheet view, one for the category and the other for the total#.
(I assume if they all have the same amount of columns, its possible to have a technique that handles it by code, that only one form should be used and each instance of form should display different set of data, but that would leave for another post..)
All those subforms should be based on Employees table, and only totaling records that matches the filter criteria selected on top, so if for example users selects hire date range 1/1/15 - 1/31/15 status = Applicant, all totals should be from employees of status applicant hired on Jan 2015.
(Perhaps once I finish the design, will upload the form so you can modify that like we'd done it successful in the past).
@Scott,
1-If I would have to upload each time the data, can this have any affect on the database size or impact users in any way?
2-That approach also limits to only one user at a time, correct?
@Pat,
The way you've described would involve I guess more programming as each subform needs to be separately changed their record source sql property after users applies a filter.
@Bit, Pat
I am more than happy to have you elaborate all the pro's and con's of using ADP, as I have only to gain from your level of expertise in this field, please feel free to continue and I guess many people would benefit from it..
Thanks,
Ben
Untitled.png
Or you create different main forms which are hard linked to their subforms where Access do the work for youIt looks like its missing some explanation on my part of what I am trying to accomplish.
First I am attaching a screenshot of the screen in question, so that may give you an idea what I'm talking about.
I am using the top section for placing all filtering options, then I will place in the tab control on the bottom all the subforms that are needed.
just to list a few examples
1) totals of employees by Source
Source
Career Builder 5
Monster 7
2) Totals of Employees by Hire Month
Jan-15 10
Feb-15 20
Mar-15 30
3) Totals of employees by Employee status
Applicant 10
Prospect 11
Now for each of those listed above I would need to design a subform that will have 2 columns in datasheet view, one for the category and the other for the total#.
(I assume if they all have the same amount of columns, its possible to have a technique that handles it by code, that only one form should be used and each instance of form should display different set of data, but that would leave for another post..)
All those subforms should be based on Employees table, and only totaling records that matches the filter criteria selected on top, so if for example users selects hire date range 1/1/15 - 1/31/15 status = Applicant, all totals should be from employees of status applicant hired on Jan 2015.
(Perhaps once I finish the design, will upload the form so you can modify that like we'd done it successful in the past).
@Scott,
1-If I would have to upload each time the data, can this have any affect on the database size or impact users in any way?
2-That approach also limits to only one user at a time, correct?
@Pat,
The way you've described would involve I guess more programming as each subform needs to be separately changed their record source sql property after users applies a filter.
@Bit, Pat
I am more than happy to have you elaborate all the pro's and con's of using ADP, as I have only to gain from your level of expertise in this field, please feel free to continue and I guess many people would benefit from it..
Thanks,
Ben
Untitled.png
There is no advantage to reusing objects in this manner. Create separate subforms or separate search forms.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Bit,
I would definitely like to work with you in completing this project, however I feel its not fair to encapsulate all this into one post, especially that this topic of reusing the same form is not really part of the original question, therefore I am first awarding all participants in this thread and opening a new one focusing more on this topic and perhaps some gaps I'm still having in applying your latest solution to my project.
Thanks again,
Ben
I would definitely like to work with you in completing this project, however I feel its not fair to encapsulate all this into one post, especially that this topic of reusing the same form is not really part of the original question, therefore I am first awarding all participants in this thread and opening a new one focusing more on this topic and perhaps some gaps I'm still having in applying your latest solution to my project.
Thanks again,
Ben
ASKER
ASKER
@All Experts,
When you have a chance please take a look at the following, thanks.
https://www.experts-exchange.com/questions/28665967/getting-error-while-trying-to-send-input-parameter-to-a-store-proc-in-ADP.html
When you have a chance please take a look at the following, thanks.
https://www.experts-exchange.com/questions/28665967/getting-error-while-trying-to-send-input-parameter-to-a-store-proc-in-ADP.html
BTW, ADP's have been deprecated. It might be time to think about a conversion path.