asked on

Microsoft Access 2013 Subform frmemployeehistory makes form slow to open

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?

PatHartman

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

marlind605

ASKER

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.

marlind605

ASKER

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.

PatHartman

Seeing the query might help.

marlind605

ASKER

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?

marlind605

ASKER

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.

marlind605

ASKER

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.

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

marlind605

ASKER

Thanks Pathartman. Let me try that.

marlind605

ASKER

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.

PatHartman

Excellent.