expression field cannot be edited

[uoghk]

I want to create a Continuous Form which shows outstanding invoices with fields: InvoiceNo, InvoiceAmt & SettleAmt.
The InvoiceNo & InvoiceAmt is locked, user only enter the SettleAmt.
I set the Record Source = SELECT InvoiceNo, InvoiceAmt, 0 AS SettleAmt FROM Invoice.
When I view the form, I cannot enter values in SettleAmt.
It shows that SettleAmt is based on expression and cannot edit.


What is the proper setting or procedure to implement it?
Thanks.

[June7]

That's because it is a calculated field. There is no actual table field. If you want textbox to show 0 by default then include actual SettleAmt field in RecordSource and bind textbox to SettleAmt field. Set textbox DefaultValue property to 0.

[uoghk]

I add the SettleAmt in actual table and include in the RecordSource.
Now it can edit in the form, and it saves what i entered.
how to set SettleAmt to 0 whenever the form is open?
I cannot find the DefaultValue property of the textbox.
thanks June7.

[June7]

DefaultValue property is on Data tab of Property Sheet.

[uoghk]

Thanks June7.
I overlook it.
After set the DefaultValue=0, I view the form, it still shows the current SettleAmt values in the table, not shows 0.
is there any thing else?

[June7]

That's how it's supposed to work. Default Value is only applied when new record is initiated.

Why would you not want to show saved data?

[uoghk]

Since it allows partial settlement of invoice, so i keep the SettleAmt to 0 everytime, then run some codes to calculate.
Actually, i want to show in the form is InvoiceNo, OutstandingAmt, SettleAmt.
The problem is when user want to edit a settle record, how to find the actual OutstandingAmt?
For example, an invoice $100, first settle $20, second settle $25.
When user want to edit the first settle record, the OutstandingAmt should be 100-25=75, the entered SettleAmt cannot greater than OutstandingAmt.
The formula for OutstandingAmt should be InvoiceAmt - sum of SettleAmt of other settlement records.
How can i implement this?
Thanks.

[Gicu]

You need to go back to the drawing board. To implement what you describe you will need a new table to store each SettleAmount (InvoiceNo_FK,SettleAmount, SettleDate) then use a subform to enter those details.
Now on the Main form you can add an OutstandingAmt = InvoiceAmount-dSum("SettleAmount","tblInvSettle","[InvoiceNo_FK=" & [InvoiceNo]). Or instead of using dSum you could sum it up in the footer of the subform then make a reference to it:OutstandingAmt = InvoiceAmount-Forms!frmMain!sFrmSettleInv.Form.txtTotalSettle


Cheers,

[uoghk]

Thanks Gicu.
Understand.