DLookup text box on form shows error

[Abacus1234]

The DLookup is on a form in which I am creating a record. I then open another form from the first form, creating its record for the first time. When I close out of that form, the field I am looking for with the dlookup has been created, but the first form I opened does not create its record until I close it and reopen it. Until I have done that the field contains #Error and not the value I am looking up. Any ideas on how to suppress the #error in the field. Thanks


=Format(DLookUp("KAddLines","QPage1AmendTotals","P age3addid=" &Nz([Forms]![FSettlementPage1]![page1ID],0)),"Currency","$0.00")

[pbaldy]

You've got the format argument twice in the Format() function. Use one or the other.

[Abacus1234]

Thanks. I now have the format argument correctly and no error in the DLookup.

However, I still have Error in a field that I am trying to add KAddlines to. Same circumstances, I am entering the form to create a record. I then open another form from the first form, creating its record for the first time. When I close out of that form, the field I am looking for with the dlookup has been created, but the first form I opened does not create its record until I close it and reopen it. Until I have done that the field that I am trying to add KAddlines to says #Error and not the sum of some fields plus KAddLines. Any ideas on how to suppress the #error in the field. Thanks

[Abacus1234]

I have finally made it work. When I go into the first form, I open and close it twice, this creating the record. When I go into the second form (from the first form), add data, close it, close and open the first form, it shows me the data just entered into the second form. This seems a bit contorted but it works.

[pbaldy]

Glad you got it working. You should be able to force a record save with:

If Me.Dirty Then Me.Dirty = False

and you can use Requery on the first form to get it to show the data from the second.

[Abacus1234]

Thanks for the response. It is appreciated.

I have changed the code so that when I return to the first form from either the second or third form, I do a requery, and that seems to be working fine.

Would you put the If Me.Dirty Then Me.Dirty = False on the After Insert or After Update property of the first form, or both.

I'm assuming it is a better method that the open close, open close.

[pbaldy]

I would think it's already saved at those points, but wherever you're doing the close/open.

[Abacus1234]

Thanks for your help.

[pbaldy]

No problem.