none
Adding CR/DR to accounting transactions (Pending Question) RRS feed

  • Question

  • I have 7 tables of COA as show in the picture attached. what I need is whenever opening balance goto - (negative) , the type must automatically show CR sign and whenever that account goto positive + , it must change to DR automatically. Please remember that the account will change to DR or CR as per entries . suppose I am doing entry in cash payment and if cash in hand account is Debit and utilities are CR in Chart of account. and if I pass an entry then and make utilities debit then in chart of accounts it must change from CR to DR in type column.

    also my accountant do not want to see (-) sign with negative he need type should change as per entries

    Let me explain more

    suppose I have 2 accounts (cash in hand ) and ( Utility Bills )

    the Opening balance of both accounts in Chart of Accounts is

    Cash in Hand = 5000 DR

    Utility Bills = 2000 CR

    now if I do an entry in cash payment so it will be

    utility bills (DR) = 8000

    cash in hand (CR) = 8000

    now after i do this entry the Chart of accounts must look like

    Cash in hand = 3000 CR ( because it is in negative due to i had only 5000) (now here the balance must show like -3000) but instead of (-) it showing 3000 and DR change to CR

    it must also update in ms sql database 

    This is what I need to do

    Tuesday, July 9, 2019 7:15 AM

All replies

  • In general, what kind of experience do you have in working  Windows form control, ADO.NET and MS SQL Server with using T-SQL and VB.NET?
    Tuesday, July 9, 2019 10:39 AM
  • Hi

    Although I suspect that what you are asking for is maybe a straightforward thing, you are failing to explain rhings in a way that others can help.

    For example, showing a DGV with some numbers means absolutely nothing.  You talk about cash in hand - where is that altered/edited/used. How does the User interact with the data? Are the cells in the DGV calculated and/or User entered?

    There are so many plain common unexpected things that you just fail to explain and/or assume the readers are mind rreaders.

    If you take your time, give an explanation of User data entry, calculations made from those entries and all other User interactions, maybe someone can help.


    Regards Les, Livingston, Scotland

    Tuesday, July 9, 2019 12:04 PM
  • my friend i have 5 accounting modules 

    cash payments 

    cash receipts

    bank payments and receipts 

    journal entries

    now all these modules are based on chart of accounts . in cash payment mostly cash in hand i credit and the account to whom we pay is debit . 

    now in chart of accounts cash in hand type or nature remains DR but other account to whom we pay cash is credit and if we pay him and his account type is DR in chart of account, then his account must show CR after we do entry in cash payment. and same like if we do entry in cash receipt, then his account will be DR if in chart of account its CR, becz we are receiving payment from him. 

    now i am not able to change the type of status from DR to CR or CR to DR after every transactions . 

    Wednesday, July 10, 2019 7:36 AM
  • ok guys i got an idea but need your some help 

    is it possible that we can make a code something like

    if datagridview.row.column"opening_balance" <0 then

    datagridview.row.column"type" change to CR 

    or 

    if > 0 

    then DR

    can anyone help me with this idea that will solve my problem .i will place this idea in button event and whenever user press button  it will refresh the datagridview and update the coa accordingly. 

    Wednesday, July 10, 2019 10:05 AM
  • Hi

    Simple stand alone example

    ' BLANK Form1
    Option Strict On
    Option Explicit On
    Public Class Form1
        Dim dt As New DataTable
        Dim WithEvents dgv As New DataGridView
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            With dt
                .Columns.Add("One")
                .Columns.Add("opening_balance")
                .Columns.Add("Three")
                .Columns.Add("Four")
                For i As Integer = 22 To 41
                    .Rows.Add(i, i * 2, i / 3)
                Next
            End With
            With dgv
                .Location = New Point(10, 10)
                .Size = New Size(ClientSize.Width - 20, ClientSize.Height - 60)
                .Anchor = AnchorStyles.Bottom Or AnchorStyles.Left Or AnchorStyles.Right Or AnchorStyles.Top
                .DataSource = dt
            End With
            Controls.AddRange({dgv})
        End Sub
        Private Sub dgv_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles dgv.CellValueChanged
            If e.ColumnIndex = dgv.Columns("opening_balance").Index Then
                dgv("Four", e.RowIndex).Value = IIf(CDbl(dgv("opening_balance", e.RowIndex).Value) >= 0, "CR", "DR")
            End If
        End Sub
    End Class
    


    Regards Les, Livingston, Scotland

    Wednesday, July 10, 2019 10:59 AM
  • your code give me this without DR or CR

    what i ask is if i can make a class or module that whenever any entry done or if i press button in coa, then it refresh and check if any account is in minus so it will change type of that minus into CR or if the amount is in Plus so it will change it to DR 

    Wednesday, July 10, 2019 11:32 AM
  • Hi

    Of course it did - that is by design.

    Try changing the values in the "opening_balance" colum.

    This example is just that - an example. I would assume the initial data (including the CR/DR column) is loaded from you database, and so the column would have the initial CR/DR values.


    Regards Les, Livingston, Scotland


    • Edited by leshay Wednesday, July 10, 2019 11:36 AM
    Wednesday, July 10, 2019 11:36 AM
  • ok i tried your code on live data like this 

     Private Sub COADataGridView_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles COADataGridView.CellValueChanged
            If e.ColumnIndex = dgv.Columns("opening_balance").Index Then


                dgv("Type", e.RowIndex).Value = IIf(CDbl(dgv("opening_balance", e.RowIndex).Value) >= 0, "CR", "DR")

            End If

        End Sub

    and got error 

    Object reference not set to an instance of an object. 

      If e.ColumnIndex = dgv.Columns("opening_balance").Index Then

    Wednesday, July 10, 2019 12:29 PM
  • Hi

    First of all - did you try the code I posted as a stand alone example?


    Regards Les, Livingston, Scotland

    Wednesday, July 10, 2019 12:31 PM
  • ok i tried your code on live data like this 

     Private Sub COADataGridView_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles COADataGridView.CellValueChanged
            If e.ColumnIndex = dgv.Columns("opening_balance").Index Then


                dgv("Type", e.RowIndex).Value = IIf(CDbl(dgv("opening_balance", e.RowIndex).Value) >= 0, "CR", "DR")

            End If

        End Sub

    and got error 

    Object reference not set to an instance of an object. 

      If e.ColumnIndex = dgv.Columns("opening_balance").Index Then

    Hi

    See the emboldened 'dgv' items and correct them.


    Regards Les, Livingston, Scotland

    Wednesday, July 10, 2019 12:34 PM
  •   If e.ColumnIndex = COADataGridView.Columns("opening_balance").Index Then


                COADataGridView("Type", e.RowIndex).Value = IIf(CDbl(COADataGridView("opening_balance", e.RowIndex).Value) >= 0, "CR", "DR")

            End If

    same error 

    Wednesday, July 10, 2019 12:55 PM
  • Hi

    When posting code USE THE CODE BLOCK TOOL please.

    Is there actually a column called "opening_balance", and one called "Type"?

    And, please always indicate the line causing the error.

    *

    BTW: using the exact same code you just posted, and my changing the column name to Type, everthing works OK here.


    Regards Les, Livingston, Scotland



    • Edited by leshay Wednesday, July 10, 2019 1:05 PM
    Wednesday, July 10, 2019 12:57 PM
  • yes there is see the attached image when i posted this question and error is in below line 

     If e.ColumnIndex = COADataGridView.Columns("opening_balance").Index Then


    • Edited by Omi4u Wednesday, July 10, 2019 1:08 PM
    Wednesday, July 10, 2019 1:06 PM
  • yes there is see the attached image when i posted this question and error is in below line 

     If e.ColumnIndex = COADataGridView.Columns("opening_balance").Index Then

    Hi

    Put a BREAK POINT on that line and when reached, hover the mouse pointer over each part and examine the pop up details.

    What is the value for e.ColumnIndex?

    What is the value for COADataGridView.Columns("opening_balance").Index?


    Regards Les, Livingston, Scotland


    • Edited by leshay Wednesday, July 10, 2019 1:12 PM
    Wednesday, July 10, 2019 1:09 PM
  • 
    Wednesday, July 10, 2019 1:30 PM
  • Hi

    Well at this stage all I can say is that there is no more I can help with as it seems the datagridview you are using is not actually there!

    Please check and double check the names you use and that they are there correctly.


    Regards Les, Livingston, Scotland

    Wednesday, July 10, 2019 1:51 PM
  • Hi

    Another question: you show the column header in the first image of this thread as "Opening Balance" - recheck that the datacolumn name is indeed "opening_balance" and not "Opening Balance"


    Regards Les, Livingston, Scotland

    Wednesday, July 10, 2019 2:14 PM
  • opening_balance is same Leshay

    ok leave it i found the method but i am only facing one issue how can i disable or remove (-) sign from number 

    like if i am searching for an ID =30161 and the balance of that account is -123456 so i want it to be

    123456 insead of -123456 in textbox 

    i know i can do it with math.abs but i tried below 

    dim res as integer

    textbox5.text=math.abs(res)

    but this method not working 
    • Edited by Omi4u Friday, July 12, 2019 7:44 AM
    Friday, July 12, 2019 7:43 AM
  • Leshay here is the solution of my problem ..........

       Using cn As New SqlConnection(appx.myconnection)
                            cn.Open()
                            Using com As New SqlCommand("", cn)
                            com.CommandText = "Update COA set opening_balance = opening_balance - @dr,type=@type where AccountID=@id"
                            com.Parameters.AddWithValue("@id", row.Cells(3).Value).ToString()
                            com.Parameters.AddWithValue("@dr", row.Cells(8).Value).ToString()
                            com.Parameters.AddWithValue("@type", "CR")
                                com.ExecuteNonQuery()
                            com.CommandText = "Update COA set opening_balance = opening_balance + @dr,type=@typee where AccountID=@id"
                            com.Parameters("@id").Value = row.Cells(5).Value.ToString()
                            com.Parameters("@dr").Value = row.Cells(7).Value.ToString()
                            com.Parameters.AddWithValue("@typee", "DR")

                                com.ExecuteNonQuery()
                            End Using
                            ' cn.Close is not required
                        End Using
                    Next

    bold font is the line which i was not mentioning while writing code.......

    now final solution i need is how can i remove the minus sign in COA in COA Module and in ms SQl. is there any way to write any code in update statement above ? or anyother way

    Friday, July 12, 2019 11:19 AM
  • Hi

    USE THE CODE BLOCK TOOL TO POST CODE!


    Regards Les, Livingston, Scotland

    Friday, July 12, 2019 12:21 PM
  • ok sorry 

     Using cn As New SqlConnection(appx.myconnection)
                            cn.Open()
                            Using com As New SqlCommand("", cn)
                            com.CommandText = "Update COA set opening_balance = opening_balance - @dr,type=@type where AccountID=@id"
                            com.Parameters.AddWithValue("@id", row.Cells(3).Value).ToString()
                            com.Parameters.AddWithValue("@dr", row.Cells(8).Value).ToString()
                            com.Parameters.AddWithValue("@type", "CR")
                                com.ExecuteNonQuery()
                            com.CommandText = "Update COA set opening_balance = opening_balance + @dr,type=@typee where AccountID=@id"
                            com.Parameters("@id").Value = row.Cells(5).Value.ToString()
                            com.Parameters("@dr").Value = row.Cells(7).Value.ToString()
                            com.Parameters.AddWithValue("@typee", "DR")
    
                                com.ExecuteNonQuery()
                            End Using
                            ' cn.Close is not required
                        End Using
                    Next

    Friday, July 12, 2019 1:00 PM
  • im only stuck on this point that how i can remove (-) sign... 
    Friday, July 12, 2019 1:16 PM
  • im only stuck on this point that how i can remove (-) sign... 

    Hi

    ' change -234567 to 234567 (as String)
    Dim st As String = Math.Abs(-234567).ToString
    


    Regards Les, Livingston, Scotland

    Friday, July 12, 2019 1:27 PM
  • i tried this method as i mentioned above. but this method is not removing (-) in datagridview and in ms sql
    Friday, July 12, 2019 1:29 PM
  • Hi

    Why does the database even have a set of values you don't want - you should revisit the means of editting/saving of your data and eliminate the negative values either during User input or prior to saving the data.

    You could look at a Format for the column to eliminate the displayed "-" sign but keeping the negative calue (for calculations maybe?)

    EDIT: it just occurred to me. You need the negative value to decide if it is a CR or a DR accout - don't you?  Also, I seem to remember that proper accounting practice was to show negatives in parenthasis    -23456   =   (23456) is that not the case?


    Regards Les, Livingston, Scotland



    • Edited by leshay Friday, July 12, 2019 1:52 PM
    Friday, July 12, 2019 1:37 PM
  • yes agree proper accounting practice is to show negative in parenthesis . i am acca qualified and i totally agree with you on this but my company's accounts department want it to be like a number without minus sign only..
    Saturday, July 13, 2019 6:20 AM
  • Hi

    OK. Here is an example of one way to do this.

    This example provides a toggle function via a Button1 - this Button is only for the example and wouldnt be in a realworld application.

    The idea is that for the column in question you set up 2 columns. One column holds the actual value (named TrueValue) positive or negative and would be used in calculations etc, The second column (named DisplayValue) shows the ABS value (always positive) and is for display only. Only the DisplayValue column is visible with the TrueValue column being set to .Visible= False.

    Only in this example, you can switch between showing TrueValue column or Display Value column.

    The example has a blank DataGridView1 and a Button1 added via the Designer. The CR/DR column reflects the TrueValue (positive/negative).

    Option Strict On
    Option Explicit On
    Public Class Form1
        Dim dt As New DataTable("Freddy")
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            With dt
                .Columns.Add("One", GetType(String))
                .Columns.Add("TrueValue", GetType(Decimal))
                .Columns.Add("DisplayValue", GetType(Decimal), "IIf(TrueValue < 0,TrueValue*-1, TrueValue)")
                .Columns.Add("Type", GetType(String), "IIf(TrueValue < 0,'DR', 'CR')")

                .Rows.Add("1", -2345)
                .Rows.Add("2", 12345)
                .Rows.Add("3", -3456)
                .Rows.Add("4", -45678)
            End With
            With DataGridView1
                .DataSource = dt
                .Columns("DisplayValue").Visible = False
            End With
        End Sub

        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            With DataGridView1
                If .Columns("DisplayValue").Visible Then
                    .Columns("DisplayValue").Visible = False
                    .Columns("TrueValue").Visible = True
                Else
                    .Columns("DisplayValue").Visible = True
                    .Columns("TrueValue").Visible = False
                End If
            End With
        End Sub
    End Class


    Regards Les, Livingston, Scotland



    • Edited by leshay Saturday, July 13, 2019 1:27 PM
    Saturday, July 13, 2019 12:53 PM