calculate sub-form field values in main form

[arifmasum]

Thanks in advance for reading my post.
I am working with an accounts database where there is journal voucher entry form.
Under the voucher number (Primary key) there are several Amount entries as CR (Credit) and DR (Debit).

I need to calculate Credit and Debit values seperately in 2 different text box of Journal voucher entry form and verify if they are same.. If they are not equal the entry will not be saved.

Can anybody advice me how I can make this happen?

Thanks and best regards.

Arif

 

[RuralGuy]

Are the Debit records in a different field from the Credit records? If not, have you anotated each record as to whether it is a Debit or Credit record? You know you can have positive and negative values in each type of record, right?

 

[arifmasum]

Picture here

Are the Debit records in a different field from the Credit records? If not, have you anotated each record as to whether it is a Debit or Credit record? You know you can have positive and negative values in each type of record, right?

Thanks for your valuable time and reply. Here I am sending a photo of the form where you can find those fields.

Thanks and best regards.
Arif

 

[RuralGuy]

You can do it in the underlying query by adding two new fields:
DebitRecord:=IIF(DR_CR = "DR",[Amount],0)
CreditRecord:=IIF(DR_CR = "CR",[Amount],0)
Then simply sum these fields for your TextBoxes.
Correct the field names if I got them wrong.

 

[arifmasum]

Calculate on main form.

Thanks a lot.
I got the value in Sub form.
How should I calculate sum on main form and check if both values are same?
Thanks again coz you lifted up from stuck point.

 

[RuralGuy]

If you already have the values in the SubForm now then simply set the ControlSource of you two TextBoxes on the main form to =SubFormControl.Form.TextBoxName replacing SubFormControl with your SubFormControl name and TextBoxName with one of the two TextBox Controls in the SubForm where you have the value you want.

 

[arifmasum]

Thanks....

If you already have the values in the SubForm now then simply set the ControlSource of you two TextBoxes on the main form to =SubFormControl.Form.TextBoxName replacing SubFormControl with your SubFormControl name and TextBoxName with one of the two TextBox Controls in the SubForm where you have the value you want.

Thanks. I've done it... Pls describe what does SubFormControl means?
Thanks in advance....

 

[RuralGuy]

Forms are displayed on other form by means of a SubFormControl. This control has a name of its own that need not be the same as the SubForm it is displaying. When referencing a control on a SubForm the syntax is as I described previously.
Here is a link that might explain it better: Refer to Form and Subform properties and controls

 

[arifmasum]

Thanks.. one more point..

Thans a lot. It helped me understand more.
I have another porblem.
I have credit table with two kinds of Voucher "C=Credit" & "CS=Credit Sales".
I need to open and enter data in two different forms "Credit Entry" & "Credit Sales Entry".
How I can apply filter, open and enter records in different forms. (NB: There are subforms from transaction table)
Please find the table design in attachment.
Thanks in advance for your valuable time and reply which helped me a lot.

Arif

 

[RuralGuy]

How are you opening the forms now? If you are using a CommandButton, what code is behind the button?

 

[arifmasum]

Button issue....

How are you opening the forms now? If you are using a CommandButton, what code is behind the button?

Thanks. Till now I am not using any command button. But I wish to use command button to open this form.
What should be the code then? Please give me some idea.
Thanks for your valuable time and reply..

Arif

 

[RuralGuy]

Start by letting the Command Button Wizard create the button for you. We can then modify the code to add a WhereCondition argument.

 

[arifmasum]

Command button code..

Start by letting the Command Button Wizard create the button for you. We can then modify the code to add a WhereCondition argument.

I am using command button to print and preview reports and print reports. I wonder where should I put the WhereCondition argument?

This forum and members I appritiate too much... Thanks

Arif

 

[RuralGuy]

Dim stDocName As String, stLinkCriteria As String
stDocName = "rptYourReport"
stLinkCriteria = "[VouType]= 'CS'"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria, acDialog

...using your Report name and field name of course.

 

[arifmasum]

Thanks. In journal voucher I need to check the debit and credit value is same before save. Using command button how can I do that? Please give me some advice.

Thanks in advance for your time and reply.
Arif

 

[RuralGuy]

Your save button will need something like the following:

If Me.txtCredits = Me.txtCredits Then
   DoCmd.RunCommand acCmdSaveRecord
Else
   MsgBox "You can not save while the system is out of ballance"
End If

...using your controls of course.

 

[arifmasum]

Thanks one last issue !!

Your save button will need something like the following:

If Me.txtCredits = Me.txtCredits Then
   DoCmd.RunCommand acCmdSaveRecord
Else
   MsgBox "You can not save while the system is out of ballance"
End If

...using your controls of course.

Thanks a lot dear..
You answer so easily and I can catch also easily from your answer. Great man!!
One last question: I need to get balance amount (Total Deposit [Credit] - Total Withdraw[Debit]) and show it in each days cash book report(Daily Transaction). How can I get that?

Thanks in advance and greatful for your help.

Arif Masum

 

[RuralGuy]

One last question: I need to get balance amount (Total Deposit [Credit] - Total Withdraw[Debit]) and show it in each days cash book report(Daily Transaction). How can I get that?
Arif Masum

I'm afraid that question is difficult to answer without a lot more details about your tables and report. Reports are capable of summing.

 

[arifmasum]

It's Done !!!

I'm afraid that question is difficult to answer without a lot more details about your tables and report. Reports are capable of summing.

I've solve my problem myself... Thanks for your help ...

 

[RuralGuy]

That's great. There is so much more you can learn when you solve issues yourself. Enjoy the rest of the project.