Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Microsoft Access 2013 Subform frmemployeehistory makes form slow to open

Posted on 2015-01-08
11
Medium Priority
?
275 Views
Last Modified: 2015-01-09
I have a form to track time. Client wants to see the history of the users data when they open frmtimesheet. I am not sure if there is any answer to this question but I am trying to make it faster. frmemployeehistory has a query queryqrysubrequesttime behind it. The subform must allow the user to be able to delete records from it. When I take this form out I can load the form in 2 seconds but with the history it takes 10 seconds even if there are no records. Any thoughts on how I can speed this form up?
0
Comment
Question by:marlind605
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 
LVL 39

Expert Comment

by:PatHartman
ID: 40538926
you can try changing the RecordSource query for the subform to reference the main form to select the Employee ID and then remove the master/child links.

Where EmpID = Forms!frmEmployee!txtEmpID
0
 

Author Comment

by:marlind605
ID: 40538974
I already do this. Even if there are no records it takes 10 seconds to display the form. In practice I am not sure how often an employee has no records. I had thought of checking for the records and opening a different form up if there are no records but that would only help if they didn't have any records.
0
 

Author Comment

by:marlind605
ID: 40539014
Interesting  I can pull up all the records in 3.94 seconds. The owner of the database wanted the employees to see all their activity for that day. At the same time if the employee needs to add time for a previous time the database shows the record for that previous time until the employee exits the form so I created a subform to show the previously used date. Maybe I need to show the SQL. Let me work on that.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 39

Expert Comment

by:PatHartman
ID: 40539018
Seeing the query might help.
0
 

Author Comment

by:marlind605
ID: 40539263
SELECT qryhourmin.[The Minutes], qryhourmin.[Added by], DatePart("m",[Starttime]) & "/" & DatePart("d",[StartTime]) & "/" & DatePart("yyyy",[Starttime]) AS Dateselect, qryhourmin.RequestNumber, qryhourmin.Starttime, qryhourmin.EventID, qryhourmin.EmpName, qryhourmin.dateindbtl, qryhourmin.Requester, qryhourmin.VisReqNum, Format([DateReq],"mm/dd/yy") AS Mydate, qryhourmin.Stoptime, qryhourmin.ActualTime, qryhourmin.Minutes, qryhourmin.DBrequestersdetails, qryhourmin.worktype, qryhourmin.WorkCodeID, qryhourmin.DescriptionDBID, qryhourmin.Description, qryhourmin.WorkCode, tblsystemnotes.SystemText, qryhourmin.systemnotesid, qryhourmin.Sel, qryhourmin.Dateofentry, qryhourmin.eod, qryhourmin.ActualTime AS Xtime
FROM tblsystemnotes RIGHT JOIN qryhourmin ON tblsystemnotes.systemnotesid = qryhourmin.systemnotesid
WHERE (((DatePart("m",[Starttime]) & "/" & DatePart("d",[StartTime]) & "/" & DatePart("yyyy",[Starttime]))=[Forms]![frmtimesheet]![txtselstartdate]) AND ((qryhourmin.EmpName)=networkusername())) OR (((qryhourmin.EmpName)=networkusername()) AND ((qryhourmin.Dateofentry)=[Forms]![frmtimesheet]![txtselstartdate]))
ORDER BY qryhourmin.EventID, qryhourmin.Starttime;

Open in new window

The only way I got my date to work was to construct it the way I did. Any suggestions?
0
 

Author Comment

by:marlind605
ID: 40540090
SELECT Last(tblemployeeactivity.Dateofactivity) AS LastOfDateofactivity, Last(tblemployeeactivity.DateHistory) AS MYDate
FROM tblemployeeactivity
GROUP BY tblemployeeactivity.EmpName
HAVING (((tblemployeeactivity.EmpName)=networkusername()));

Open in new window


I create a log for employees when they go to the time sheet log. MYDATE is where [Forms]![frmtimesheet]![txtselstartdate] comes from. LIke I say the it takes 10 seconds for the records to pull up. Trying to shorten that time.  Will Dlookup work faster I could never get that to work.
0
 

Author Comment

by:marlind605
ID: 40540191
I am testing Dlookup with this in the control field =DLookUp([MYDate],[qrynetworklast]). I get the #Name? displayed in the box. Can someone help me correct this. I would like to see if this is faster then my subform.
0
 
LVL 39

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 40540214
Use left joins rather than DLookup() wherever possible.
The problem is that the control used to be bound and its name was "mydate".  Now that it is unbound, it cannot have the name of a bound field.  Change the Name property of the control to be different from the name of any field in the form's RecordSource.

Sounds like StartTime has both date and time.  Breaking it apart as you are doing in the Where clause could prevent the query engine from using an index.  A different way to accomplish this is with:

WHERE (([Starttime] >= [Forms]![frmtimesheet]![txtselstartdate]  AND < [Forms]![frmtimesheet]![txtselstartdate] + 1) OR qryhourmin.Dateofentry  = [Forms]![frmtimesheet]![txtselstartdate])
AND qryhourmin.EmpName = networkusername();

I also see that you have a function in the where clause.  Get rid of it.  Look up the user name on the form and put it in a form field so the query engine doesn't have to resolve it.
0
 

Author Comment

by:marlind605
ID: 40540245
Thanks Pathartman. Let me try that.
0
 

Author Closing Comment

by:marlind605
ID: 40541283
Thank you very much. Your solution reduced the time for the form to open from 11 seconds to 3 seconds. I really appreciate the help.
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 40541330
Excellent.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question