Display Account balance on Main form from sub form transactions

MarionD

Registered User.
Local time
Today, 17:16
Joined
Oct 10, 2000
Messages
425
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
 
how do you select a client? combobox, textbox?
what is the ControlSource of the Textbox in the Main form?
 
I select the client from an unbound combo box in the main form
 
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
 
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!:unsure:
 
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!:unsure:
Might need it in the afterupdate event of the combo as well?
 
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!:unsure:
In an Unbound Textbox on the Main Form you can use the following:-

=[Forms]![NameofYourMainForm]![NameofYourSubform].[Form]![NameofYourControlwithTotal]
 
Putting the code on the unbound text box on the main form did the trick
 
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
 
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.:unsure:
 
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.
 
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
 
=[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
 
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
 
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? :(
 
Last edited:

Users who are viewing this thread

Back
Top Bottom