DLookup update problems

[sherrera]

I am trying to use DLookup to update a field in a form with the
contents of a foreign table, setting the control's ControlSource to:

IIf([Forms]![Order Headers].[NewRecord];"";DLookup("Name";"Providers";"Id
= " & [Forms]![Order Headers]![Provider Id]))

(the iif is to deal with null values in new records)

I have a button that opens up a Providers form. When I unload that
form, I set the order's Provider Id like this (in the Form_unload
event):

If (SysCmd(acSysCmdGetObjectState, acForm, "Order Headers") And
acObjStateOpen) Then
Forms![Order Headers]![Provider Id] = Me.[Id]
End If

This works well when updating existing records. However, when I create
a new record, the DLookup field fails to update, until I move out of
the form and in again.

I have tried using requery / recalc on the form to retrieve the
correct values, but unfortunately there's other required fields in the
form which cannot be left empty, so I receive a data validation error.
I have also tried using the control's requery method, to no avail.
Also, before I came up with the DLookup approach I tried to use the
form's datasource to retrieve the values with similar results.

Currently I am thinking of using the Form_Current and Form_Unload
events of the forms to set up manually the correct values, but it
feels like a hack, so can anyone help me? Any suggestions?

I am using Access XP and the database is an ADP.