DLookup -- Delayed Calculations

[Guest]

Hello. I have a form that uses a combo box for the entry of insurance claims status code. The combo box drop down shows the possible codes for selection along with the description of those codes both pieces of data coming from a separate "master file" table listing. When the user selects a code from the drop down, the code is saved as the new value for the combo box field and it is also saved as the value in the associated table field that stores the code. Under the code is another text box that performs a lookup in order to display the insurance claim status code description from what was selected in the combo box. This way, the user can see both the status code and the description on the screen without having to open up the combo box list for the description

The problem I have is that the text box does not immediately update with the accurate description when the combo box selection is changed by the user. Instead, the old description from the previous status code continues to be displayed. I can page forward to the next record and back again and the text box is still not updated. However, if I page forward several times to other records, after a moment the bottom left of my form view screen will show "Calculating..." and then when I page back to the record in question I will see that the text box is now updated with the correct description

Is there a way to force that calculation activity to occur immediately when the user makes a change to the combo box? It is misleading to the user to see a description that does not agree with the code that was just selected

The DLookup that I used for the text box reads as follows

=DLookup("[Status Description]","ATB Status Codes","Claim Status and Description] = [Status Code]"

The items in the DLookup statement are defined as follows

Status Description --- This is the description I want to use from the ATB Status Codes table

ATB Status Codes --- This is the table where the description I want is residing

Claim Status and Description --- This is the combo box field on the form that the user sees and uses to make the selection

Status Code --- This is the status code that is associated with the description in the ATB Status Codes table. These codes are the same as what is used in the combo box. The combo box values are limited to this list

If this is a known problem... is there some other way for me to accomplish the same thing so that I can get around the delayed calculation issue

Thanks
David

 

[Allen Browne]

You can force Access to update the calculated fields with:
Me.Recalc

An alternative approach to DLookup() is to base the form on a query that
includes the lookup table, and include the lookup field in the form's
RecordSource. You may want an outer join (i.e. double-click the line joining
the 2 tables in query design), and you probably want to make the text box
read-only (set its Locked property to Yes).

This 2nd approach is the only way to handle continuous forms/datasheets
efficiently, but is also useful for single forms as well. For an example,
see the Orders form in the Northwind sample database.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello. I have a form that uses a combo box for the entry of insurance

claims status code. The combo box drop down shows the possible codes for
selection along with the description of those codes both pieces of data
coming from a separate "master file" table listing. When the user selects a
code from the drop down, the code is saved as the new value for the combo
box field and it is also saved as the value in the associated table field
that stores the code. Under the code is another text box that performs a
lookup in order to display the insurance claim status code description from
what was selected in the combo box. This way, the user can see both the
status code and the description on the screen without having to open up the
combo box list for the description.

The problem I have is that the text box does not immediately update with

the accurate description when the combo box selection is changed by the
user. Instead, the old description from the previous status code continues
to be displayed. I can page forward to the next record and back again and
the text box is still not updated. However, if I page forward several times
to other records, after a moment the bottom left of my form view screen will
show "Calculating..." and then when I page back to the record in question I
will see that the text box is now updated with the correct description.

Is there a way to force that calculation activity to occur immediately

when the user makes a change to the combo box? It is misleading to the user
to see a description that does not agree with the code that was just
selected.

The DLookup that I used for the text box reads as follows:

=DLookup("[Status Description]","ATB Status Codes","Claim Status and Description] = [Status Code]")

The items in the DLookup statement are defined as follows:

Status Description --- This is the description I want to use from the ATB Status Codes table.

ATB Status Codes --- This is the table where the description I want is residing.

Claim Status and Description --- This is the combo box field on the form

that the user sees and uses to make the selection.

Status Code --- This is the status code that is associated with the

description in the ATB Status Codes table. These codes are the same as what
is used in the combo box. The combo box values are limited to this list.

If this is a known problem... is there some other way for me to accomplish

the same thing so that I can get around the delayed calculation issue?

 

[Guest]

Thanks! This worked perfectly.