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
Solved

Updating Label from textbox comparison but executing long query time

Posted on 2014-07-24
4
363 Views
Last Modified: 2014-07-30
Access 2010
Userform  textboxes
Labels
sql 2012 backend linked tables

What I have:
Trying to update a Label after comparing to textbox values.

Running a dlookup in the textbox 86...but the dlookup is from a query linked into the Sql server 2012 tables.
Taking approx 3-sec to run.


If Trim(Me.Text1.Value) = Trim(Me.Text86.Value) Then
Me.Label88.Caption = "Compliant"
Else
Me.Label88.Caption = "Not Compliant"
End If

I have put this in many property events.

Combo5.AfterUpdate
Form Afterdate.
etc..

I finally had to put it in the GotFocus Event of the Texbox 86.

Problem.
The query running is taking so long the comparison of the 2 textbox values are not updaqting the Label correctly.

Trying to get away from a user having to actually click in the textbox to get the correct calculation ?


Thanks
fordraiders
0
Comment
Question by:fordraiders
  • 3
4 Comments
 
LVL 35

Expert Comment

by:PatHartman
ID: 40217581
The code to compare the two values needs to run from three places:
1. The Form's Current event - this will display the message as you scroll to a new record.  Only run the compare if you are NOT on a new record.
If Me.NewRecord = True Then
    run compare
End If
2. The AfterUpdate event of the first field.
3. The AfterUpdate event of the second field.

When putting code into a control level event that depends on a second field, you have to consider what you want to happen if the second field is null.  Usually, that means that the user simply hasn't gotten to the second field yet and so you don't want to raise an error.  If this were validation code rather than a warning message, it would go into the FORM's BeforeUpdate event rather than the FORM's Current event because you would want to be sure that the code ran BEFORE the record was saved so you could prevent the save in the case of an error.

The Got/Loose Focus events are always the wrong events to use for this type of requirement primarily, because you don't want the user to actually have to tab/click into the control to trigger the code.

If the DLookup() is taking 3 seconds, something is wrong.  Make sure you have indexes defined on appropriate columns in the tables and make sure the query is not joining to unnecessary tables or doing anything else irrelevant.   Please post the query and give us some idea of the schema if you want advice on speeding it up.

Choosing the correct event is critical to accomplishing the task at hand.  Certain things could work in more than one event but there is ALWAYS a preferred event so your task is to learn what triggers each event and that will help you to understand what code goes where.  The most critical event of all is the FORM's BeforeUpdate event.  Without using it correctly, you can never effectively trap errors and prevent bad data from being saved.
0
 
LVL 3

Accepted Solution

by:
fordraiders earned 0 total points
ID: 40217732
Thanks...its just a reporting userform.  Not a database form. I'am sure indexes are playing a role.

I will keep trying.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 40222460
thanks
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 40228650
Thanks
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

837 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