Searching for a record causes Access to Not Respond

[SpartanAOD]

Hello all,

I have a form that displays individual employee records, with a handful of subform pages containing a couple of DLOOKUP functions in text boxes.

Ever since I added a new DLOOKUP function, when I try to search for a record by an employee's name, Access fades to white and says "(Not Responding)." It will stay that way for several minutes. I can hit ESC and back out of the search.

The DLOOKUP is rather simple. It displays the sum of an employees hours worked from a query if the Person ID number in the query matches the Person ID on the form record. There's no nested functions within it.

I've tried deleting other less useful DLOOKUPs, but it doesn't seem to affect the speed.

There's only 458 employees, and only 123 lines in the query.

 

[ranman256]

Instead of Dlookup (slow) ,how about a filter.
In a continuous form for of all records, type the name into the search box (in the form header) , then it will filter instantly....
Paste the code into the text box AFTERUPDATE event...

sub txtBox_Afterupdate()
If IsNull(txtBox) Then
   Me.FilterOn = False
Else
   Me.Filter = "[LastN]='" & me.txtBox & "'"
   Me.FilterOn = False
End If
end sub

 

[xenou]

There's only 458 employees, and only 123 lines in the query.

This sounds like a massive query, actually. Might be a cause of the delay. Hard to say. Also since you have several subforms one change may require all the subform to re-evaluate so that can slow things down.

Note: you might want to provide more detail about how this is setup up with DLookup:

Ever since I added a new DLOOKUP function, when I try to search for a record by an employee's name, Access fades to white and says "(Not Responding)." It will stay that way for several minutes. I can hit ESC and back out of the search.

 

[Micron]

Running such a function against a query is just a bad idea IMHO. The function has to run for every record, and worse, if the function uses the query itself I believe you can expect the query to run again for every record. It would be interesting to know, if that is the case here, what gets returned if it runs long enough. Sounds like the query has calculated fields and may even reference subform controls. Yikes! Perhaps there is something here that relates to your design http://allenbrowne.com/QueryPerfIssue.html

 

[SpartanAOD]

Thank you everyone, but I decided to keep it simple by doing the summing of hours in a linked excel pivot table. Works like a charm.

 

[SpartanAOD]

OK, so I have a new problem: The Excel table has 120 employees, and the form has 458 employee, but when I drop the Hours field from the linked excel table into the form, the form only has the 120 employees from the table. Is there a way where I could have all 458 records, and just have the field be blank if there is no corresponding record in the table?

 

[xenou]

That would normally done with a left join, generally speaking. The table with *all* the records is on the left side of the join, and the table with *some* of the records is on the right side.

(note, as a rule, you cannot have anything in the where clause about the right table, except in specific cases where you want to find the unmatched records. This is because the possible nulls on the right side make it so that any where criteria is always and by definition false where the records don't exist).

Example:

select * 
from Table1 
left join Table2 
on Table1.ID = Table2.ID

And for the record, to find the unmatched records:

select * 
from Table1 
left join Table2 
on Table1.ID = Table2.ID
where Table2.ID is null

 

[SpartanAOD]

How would I apply this? I typically don't hand type SQL. Do I create a new column in the primary table?

That would normally done with a left join, generally speaking. The table with *all* the records is on the left side of the join, and the table with *some* of the records is on the right side.

(note, as a rule, you cannot have anything in the where clause about the right table, except in specific cases where you want to find the unmatched records. This is because the possible nulls on the right side make it so that any where criteria is always and by definition false where the records don't exist).

Example:

select * 
from Table1 
left join Table2 
on Table1.ID = Table2.ID

And for the record, to find the unmatched records:

select * 
from Table1 
left join Table2 
on Table1.ID = Table2.ID
where Table2.ID is null

 

[xenou]

SQL is the basis for all queries, forms, and reports in Access. Here is one of many pages explaining how (this one is pretty detailed, goes into left joins vs inner joins, and uses the query builder):
http://www.makeuseof.com/tag/write-microsoft-access-sql-queries-scratch/

Just google "How to write an Access Query" or "How to write an MSAccess Query" for more information. It's would be redundant for me to try to go into too much detail when there are already so many web pages dedicated to presenting Access basics, and frankly I would probably do a pretty poor job with no graphics to assist you. You will find YouTube videos in the results also, if you prefer videos.

Note that just because I use the column ID you don't have to use a column called ID. There is no reason to create new columns - that was an example to give you the gist of how left join queries look.