Update subform (Access 2003)

[Guest]

Hi.

I have a mainform containing a subform.

In the subform,I have hidden fields that do some DLookup from the articles
table.
After filling in the article number, it fills some fields from those DLoopup
fields in to other visible fields in the subform.
This works great when I do it maually.

But when I import a .txt file i cant get it to work.
I can import the data and all, and I do this by klicking a cmd button in the
main form, but the hidden DLoopkup fields does not want to pick up the data
without me manually updating the article number field in the subform.

Becuse of this, I can not update the data to other fields and I´m back at
manually doing everything.
Not so funny, since it is several hundreds of articles each time, and it has
to be done up to 12 times per day.
I still need to do it manually also from time to time, but mostly I want to
use the imported data.
Refresh or requery doesnt do the job, or maybe I´m doing it wrong!!!

Anyone have any tips on this?
Grateful for any help.

BR
/Claes

 

[Guest]

Hi Claes,

It sounds like you have some unbound, hidden, calculated controls on the
subform. According to Help, a calculated control is:

"A control that is used on a form, report, or data access page to display
the result of an expression. The result is recalculated each time there is a
change in any of the values on which the expression is based."

So after you manually enter the article number, the FORM (in this case it is
a sub form) recalculates the controls with the DLookUp(), then (I'm guessing)
there is code in the After Update event of the article number control that
updates the visible controls with the data from the hidden controls.

When you import the text file, the data is entered directly into the
tables(s)... NOT the form. (The form is the way you view the data in the
tables.) Since the form is bypassed, the form is not recalculated and the
after update code doesn't run. Thus some fields are not filled in.

Think of the endless loop that would occur if importing a file caused event
code to run. (Which would then cause the code to run again because the data
changed again....)

Remember, importing a (text) file inserts the data directly into a table and
bypasses the interface (form). Calculated controls do not get recalculated
and Form/Control events aren't run.

Somehow you need to be able to determine which records were imported. Then
,in code, you could open a recordset and update each of the fields would have
been updated on the form when you manually entered data.

I hope I remember this right...... but I know that importing data directly
into a table won't cause calculated controls on a form to "fill in" fields in
a table.


HTH