DLookup debacle

[Lost In Programming]

I am needing some code help to refresh a table before executing a Dlookup
command. I have data entry form where there is a field that verifies a “Mail
Code†is valid by comparing it to a table of values. If the value is not in
the table, the operator has the option of entering the value into the table
via command button on the form. The command button opens a second form
linked to the “Mail Code†table. After the operator enters the new mail code
into the table, the dlookup still does not see the new value unless you close
out of the main form and open it again. What code can I run before the
DLookup so I do not have to exit out of the main form each time?

 

[Piet Linden]

I am needing some code help to refresh a table before executing a Dlookup
command.  I have data entry form where there is a field that verifies a“Mail
Code” is valid by comparing it to a table of values.  If the value isnot in
the table, the operator has the option of entering the value into the table
via command button on the form.  The command button opens a second form
linked to the “Mail Code” table.  After the operator enters the newmail code
into the table, the dlookup still does not see the new value unless you close
out of the main form and open it again.  What code can I run before the
DLookup so I do not have to exit out of the main form each time?

why not just use a combobox with the Limit To List property set to
True, and then use the OnNotInList event? There's an example in the
VB help....

 

[Lost In Programming]

There are over 15000 records in the "Mail code" table. It is just cleaner,
to have "Lost Focus" code to verify if the code exists. If it does, the
focus moves to the next field. If it does not, a message box opens that
states the "Mail Code" is invalid. The operator then has to decide if the
mail code is valid or just typed wrong. If it is valid, they click on an
"Add Mail Code" button and a second form opens for them to add all of the
necessary information about the mail code. There are at least 5 fields that
they must enter for each "Mail Code". After they close the second form, the
focus is still on the "Mail Code" field in the primary form. I would like
for the operator to just be able to hit tab or enter and execute the Dlookup
code that is programmed in the lost focus. The problem is that the DLookup
does not see the new table entry unless you exit the main form and open it up
again. Is there code that I can put into the "Lost Focus" area before it
executes the DLookup to refresh the tblMailCodes?

 

[Damon Heron]

On the click event of the command button:

DoCmd.OpenForm "Your Second FormName", acNormal, , , acFormEdit, acDialog
Me.Requery

or, if that doesn't work, just repeat the lostfocus code for the dlookup
instead of me.requery

Damon

 

[Lost In Programming]

Thanks Damon. The Me.Requery worked great.

On the click event of the command button:

DoCmd.OpenForm "Your Second FormName", acNormal, , , acFormEdit, acDialog
Me.Requery

or, if that doesn't work, just repeat the lostfocus code for the dlookup
instead of me.requery

Damon