Value not showing on Main Form (1 Viewer)

[MarionD]

I have a very strange problem.
Background: I have a main form on based on a table. I have an unbound combo search field on this form. After update I select the correct record from the table to display (recordset bookmark).
I have sub form on the main form containing transactions pertaining to the record on the main form. (Linked by ID to tbl_Mainform_ID) All this works perfectly.
In the footer of the subform I have a field called fld_endamount1 : = nz(sum(fld_endamount);0)

Problem1: I have a field on the main form displaying the endamount from the sub form : =forms!mainform!subform.form!endamount1
This works after selecting a record but as the form opens on a new record (and can be used to enter a new customer) this field display #error until there are records in the sub form.

Problem 2: I have tried to solve this in vba by placing a procedure on "after update" of the unbound search field.
(Restzahlung being an unbound form on the main form where I basically want to show the end amount of the customer as in the footer of the sub form.)

Dim x As Long
x = Me.frmUF_BrideDress.Form.RecordsetClone.RecordCount
If x > 0 Then
me.Restzahlung = [Forms]![frm_bride]![frmUF_BrideDress].[Form]![sumendpreis]
ElseIf x = 0 Then
Me.Restzahlung = 0
End If
Exit Sub

This works fine as long as I put a stop in the procedure and do it step by step. As soon as I remove the stop it just sets me.Restzahlung at 0.
I have NO IDEA why this would work with a stop in and not without.
Can anyone help me please?

Thanks
Marion

 

[arnelgp]

1. You can shorten the ControlSource of your unbound texbox to:

=[subform_name].[Form]![endamount1]

2. you can try to move your code on the Current event of the Subform.

 

[pbaldy]

I use this:

Forms: #Error when the Subform has no records

theaccessweb.com

 

[MarionD]

thanks so much for the answer. Unfortunately it does not seem to make a difference.
In the screenshot 1 is the Field with the control source set to =[subform_name].[Form]![endamount1] on the form before a customer is selected.
on the second screenshot is the field showing correctly when records are in the subform / the Field Restzahlung should show the same Amount as calculated in the code. (after updte of the search field for a customer) Putting it the current of the subform makes no diffs.

Thanks again

 

[MarionD]

I use this:

Forms: #Error when the Subform has no records

theaccessweb.com

thanks so much! This works well for the field in the main form.

I know I don't need 2 fields displaying the same but I am really puzzled as to why it doesn't work in VBA. (Unbound Field)
Thanks again

I use this:

Forms: #Error when the Subform has no records

theaccessweb.com

 

[pbaldy]

Happy to help!