DLookup - Doesn't update in form until 1st scrolling to other reco

G

Guest

Hi everyone,

I have very little experience and have been slowly working my way through
setting up a fairly simple database for our office. I have run into a
problem with DLookup that I just can't seem to figure out.

Here is the deal...

I have a Combo Box in a form where the user sees a drop down list of what we
call "root causes" along with the description of the code. When the user
selects one of these codes the field on the form just shows the selected code
and not the description part. We really want to display both the code and
the description on the form since not everyone will know what each code
represents.

My solution was to add another field that does a DLookup. The new field
looks up the "root cause" value that was saved in the Combo Box by finding
that code in the root cause code table and returning the description that is
associated with the code.

This works... but only sort of.

The problem is that when the user selects a "root cause" the description
doesn't immediately populate. In fact, if you select a "root cause" and do
nothing else for any length of time... the description never populates. If
you scroll to the next record and back again... the description still doesn't
populate. However, if you scroll several records away and then scroll back
to the record you had updated, now suddenly the description has populated
with the appropriate data. The same problem occurs if you change the
previously selected "root cause".

Okay... the next part I may not be stating quite right but hopefully you'll
understand what I am trying to say...

The database acts as if so long as the record is retained in a buffer the
description doesn't get updated. BUT... if you access a bunch of other
records it is as if the record you updated has been "dropped out" of the
buffer and when you come back to that record it has to be "pulled" up again
rather than just presented straight from the data that was in the buffer.
When it gets pulled up in that way it seems that only then does the record
update. Maybe this is way off base but that is just how it seems to be
behaving.

My DLookup statements is written as follows:

=DLookUp("[Root Cause Description]","[Root Causes]","[AX Account
Records]![Root Cause] = [Root Causes]![Root Cause]")

Root Causes is the table from which the Combo Box allows the user to select
the Root Cause code. It is also the table in which the Root Cause code is
looked up and then the Root Cause Description is supposed to be returned to
the form.

Ideas?
 
G

Guest

Hi David,

Sounds like you are working directly with your tables... in a word - DON'T.
Tables are there to store data, end of story. To present your data to your
users in a readable format you want to use a form to display the data. Use a
combo box to display real descriptions instead of ID fields, and if you want
both, create a composite field to display in the query behind the combo box.

Hope that helps.

Damian.

David Newbold said:
Hi everyone,

I have very little experience and have been slowly working my way through
setting up a fairly simple database for our office. I have run into a
problem with DLookup that I just can't seem to figure out.

Here is the deal...

I have a Combo Box in a form where the user sees a drop down list of what we
call "root causes" along with the description of the code. When the user
selects one of these codes the field on the form just shows the selected code
and not the description part. We really want to display both the code and
the description on the form since not everyone will know what each code
represents.

My solution was to add another field that does a DLookup. The new field
looks up the "root cause" value that was saved in the Combo Box by finding
that code in the root cause code table and returning the description that is
associated with the code.

This works... but only sort of.

The problem is that when the user selects a "root cause" the description
doesn't immediately populate. In fact, if you select a "root cause" and do
nothing else for any length of time... the description never populates. If
you scroll to the next record and back again... the description still doesn't
populate. However, if you scroll several records away and then scroll back
to the record you had updated, now suddenly the description has populated
with the appropriate data. The same problem occurs if you change the
previously selected "root cause".

Okay... the next part I may not be stating quite right but hopefully you'll
understand what I am trying to say...

The database acts as if so long as the record is retained in a buffer the
description doesn't get updated. BUT... if you access a bunch of other
records it is as if the record you updated has been "dropped out" of the
buffer and when you come back to that record it has to be "pulled" up again
rather than just presented straight from the data that was in the buffer.
When it gets pulled up in that way it seems that only then does the record
update. Maybe this is way off base but that is just how it seems to be
behaving.

My DLookup statements is written as follows:

=DLookUp("[Root Cause Description]","[Root Causes]","[AX Account
Records]![Root Cause] = [Root Causes]![Root Cause]")

Root Causes is the table from which the Combo Box allows the user to select
the Root Cause code. It is also the table in which the Root Cause code is
looked up and then the Root Cause Description is supposed to be returned to
the form.

Ideas?
 
G

Guest

Don't use another DlookUp to retrieve the description.

If the RowSource of the combo include both, code and description,

Select Code, Description From TableName

Then, create another text box in the form, in the text box ControlSource
property refer to the right column from the combo

=[ComboName].Column(1)

***Note: The column numbers start with 0, so to get the description I used 1
***

This field will be updated automatically without need for any code, every
time there is a value selected in the combo

--
Good Luck
BS"D


David Newbold said:
Hi everyone,

I have very little experience and have been slowly working my way through
setting up a fairly simple database for our office. I have run into a
problem with DLookup that I just can't seem to figure out.

Here is the deal...

I have a Combo Box in a form where the user sees a drop down list of what we
call "root causes" along with the description of the code. When the user
selects one of these codes the field on the form just shows the selected code
and not the description part. We really want to display both the code and
the description on the form since not everyone will know what each code
represents.

My solution was to add another field that does a DLookup. The new field
looks up the "root cause" value that was saved in the Combo Box by finding
that code in the root cause code table and returning the description that is
associated with the code.

This works... but only sort of.

The problem is that when the user selects a "root cause" the description
doesn't immediately populate. In fact, if you select a "root cause" and do
nothing else for any length of time... the description never populates. If
you scroll to the next record and back again... the description still doesn't
populate. However, if you scroll several records away and then scroll back
to the record you had updated, now suddenly the description has populated
with the appropriate data. The same problem occurs if you change the
previously selected "root cause".

Okay... the next part I may not be stating quite right but hopefully you'll
understand what I am trying to say...

The database acts as if so long as the record is retained in a buffer the
description doesn't get updated. BUT... if you access a bunch of other
records it is as if the record you updated has been "dropped out" of the
buffer and when you come back to that record it has to be "pulled" up again
rather than just presented straight from the data that was in the buffer.
When it gets pulled up in that way it seems that only then does the record
update. Maybe this is way off base but that is just how it seems to be
behaving.

My DLookup statements is written as follows:

=DLookUp("[Root Cause Description]","[Root Causes]","[AX Account
Records]![Root Cause] = [Root Causes]![Root Cause]")

Root Causes is the table from which the Combo Box allows the user to select
the Root Cause code. It is also the table in which the Root Cause code is
looked up and then the Root Cause Description is supposed to be returned to
the form.

Ideas?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top