Display Account balance on Main form from sub form transactions (1 Viewer)

[MarionD]

Hi there,
I have a sub form listing various transaction for a client. It displays in Datasheet form.
I have put an unbound field in the foot of the sub form (nz(summe(endbetrag);0)

Now I want to display this on the main form when the client is chosen, (forms are linked by id to clientID)
It works fine as soon as I select a client, but when I open the form (before selecting a client) the field shows "Error"
How can I make this field just empty until a client is selected?

Any help much appreciated!
Marion

 

[arnelgp]

how do you select a client? combobox, textbox?
what is the ControlSource of the Textbox in the Main form?

 

[MarionD]

I select the client from an unbound combo box in the main form

 

[arnelgp]

what i was thinking is to have your Textbox unbound first.
you need to remove the "expression" from the textbox controlsource.

then you Set the Value of this Textbox from the subForm's Current event:

Private Sub Form_Current()
On Error Resume Next
Me.Parent!theTextboxNameOnMainForm = Me!yourTotalTextboxOnThisSubform
End Sub

 

[MarionD]

wow now this is the wierdest thing!

If I just select a client it doesn't show the amount
BUT
If I put a stop in the code and run it line by line it's perfect.

How can that be!

 

[Gasman]

wow now this is the wierdest thing!

If I just select a client it doesn't show the amount
BUT
If I put a stop in the code and run it line by line it's perfect.

How can that be!

Might need it in the afterupdate event of the combo as well?

 

[mike60smart]

wow now this is the wierdest thing!

If I just select a client it doesn't show the amount
BUT
If I put a stop in the code and run it line by line it's perfect.

How can that be!

In an Unbound Textbox on the Main Form you can use the following:-

=[Forms]![NameofYourMainForm]![NameofYourSubform].[Form]![NameofYourControlwithTotal]

 

[MarionD]

BINGO!

Thank you sooo much. Have a good day!

 

[Gasman]

BINGO!

Thank you sooo much. Have a good day!

Ok, so which piece of advice fixed the problem?

 

[MarionD]

Putting the code on the unbound text box on the main form did the trick

 

[mike60smart]

Glad to help

 

[MarionD]

Just to add to this:
I have to first check that there are records in the recordset subform. If not an error occurs.
So I ended up with this which works fine
If Me.frmsubform.RecordsetClone.RecordCount = 0 Then
Me.Restzahlung = 0
Else
Me.Restzahlung = Nz(Me.frmsubform!sumendpreis, 0)
End If
Thanks again for the help

 

[Gasman]

Why recordsetclone???

 

[MarionD]

Basically I just want to know if there are records in the subform.

If I select a new client, before I have entered any items in the subform an error occured, if I reference the field on the subform.
This way I thought I could just count the records in the subform (for the client in the main form) and if no records are there I just put a 0 in the field.
It works anyway.

 

[mike60smart]

Hi Marion
If you have created the Parent/Child Links correctly then by default when you select a New Client there would NOT be any records in the Subform.

 

[mabino79]

In an Unbound Textbox on the Main Form you can use the following:-

=[Forms]![NameofYourMainForm]![NameofYourSubform].[Form]![NameofYourControlwithTotal]

=[Forms]![frmMain]![tblInventoryTransactionSubForm].[Form]![txtTotalQuantityinStock] - getting error

 

[Gasman]

=[Forms]![frmMain]![tblInventoryTransactionSubForm].[Form]![txtTotalQuantityinStock] - getting error

it is NOT the name of the subform that you use, but the subform control.
Are they named the same?, I always prefix mine with something like ctrl

 

[mabino79]

it is NOT the name of the subform that you use, but the subform control.
Are they named the same?, I always prefix mine with something like ctrl

Main form : frmMain
Sub form : tblInventoryTransactionSubForm
unbound box name : txtTotalQuantityinStock

 

[mabino79]

In an Unbound Textbox on the Main Form you can use the following:-

=[Forms]![NameofYourMainForm]![NameofYourSubform].[Form]![NameofYourControlwithTotal]

not worked with me. getting error.

 

[Gasman]

I did say it is the subform control name and NOT THE SUBFORM NAME ????
However as you say it previously worked ???? it appears they are one and the same?

Edit: And you cannot say what the error is?