Assigning Accounts Codes with VBA

Hi

Background

The management want special general ledger accounts to be attached to the sales invoice automatically so that no manual selection of GL accounts can be allowed to account for the following:

(1) Sales
(2) VAT
(3) Discounts
(4) Excise Duty

Now on the main form I have created un bound controls to summaries the content of ( Sales , VAT, Discounts & Excise Duty) as you can see above , and below are the controls created on the main form to do the required summaries:

(1) txtInvoicesSales
(2) txtInvoicesVat
(3) txtSalesDuties
(4) txtsalesInviiceDiscounts

The mentioned controls above does a good job as you can see from the picture below.

Now I want help to assign both general ledger codes and the value content in the controls above , I have tried the code below it does not work , any help here will be highly appreciated

Private Sub Form_BeforeInsert(Cancel As Integer)
If (Me.Parent!txtInvoiceSales <> "") Then
Me.AccountID = 120 - 15 - 6660 And (Me.Credit = Me.Parent!txtInvoiceSales)
ElseIf (Me.Parent!txtsalesinvoiceDiscounts <> "") Then
Me.AccountID = 110 - 16 - 1004 And (Me.Debit = Me.Parent!txtsalesinvoiceDiscounts)
End If
End Sub

How do I do it?

Regards

Chris

Gustav Brock

If those numbers are the actual account numbers, I guess they are supposed to be strings, thus (as I would check for Null):

Private Sub Form_BeforeInsert(Cancel As Integer)

    If IsNull(Me.Parent!txtInvoiceSales.Value) Then
        Me.AccountID.Value = "120-15-6660"
        Me.Credit.Value = Null
    ElseIf IsNull(Me.Parent!txtsalesinvoiceDiscounts.Value) Then
        Me.AccountID.Value = "110-16-1004"
        Me.Debit.Value = Null
    End If

End Sub

Open in new window

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

ASKER

Thank you Gustav Brock!

We are almost there, I have only changed the data to look like below:

Private Sub Form_BeforeInsert(Cancel As Integer)
If (Me.Parent!txtInvoiceSales.Value <> "") Then
Me.AccountID.Value = "69"
Me.Credit.Value = Me.Parent!txtInvoiceSales.Value
ElseIf (Me.Parent!txtsalesinvoiceDiscounts.Value <> "") Then
Me.AccountID.Value = "73"
Me.Debit.Value = Me.Parent!txtsalesinvoiceDiscounts.Value
End If
End Sub

The only challenge here is that it is repeating the same code it does not go to the next line which is "73" , if this can be done then all is okay.
See picture below:

Gustav Brock

Not sure what "repeating" means - but if you wish to check both conditions, you could use:

Private Sub Form_BeforeInsert(Cancel As Integer)

    If (Me.Parent!txtInvoiceSales.Value <> "") Then
        Me.AccountID.Value = "69"
        Me.Credit.Value = Me.Parent!txtInvoiceSales.Value
    End If
    If (Me.Parent!txtsalesinvoiceDiscounts.Value <> "") Then
        Me.AccountID.Value = "73"
        Me.Debit.Value = Me.Parent!txtsalesinvoiceDiscounts.Value
    End If

End Sub

Open in new window

Of course, if the second condition is met, it will override the AccountID that was set if the first condition was met.

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

ASKER

Okay Sorry I did not explain properly what is required, the above code should work like below:

(1) The code below should loop only once and end its business with one entry only which is on a credit side

If (Me.Parent!txtInvoiceSales.Value <> "") Then
Me.AccountID.Value = "69"
Me.Credit.Value = Me.Parent!txtInvoiceSales.Value
End If

The second code should also loop like above once and end its business with one entry on a debit side , but if it finds that the credit side has a value more than zero then it should return or insert a ZERO in the debit side . If it does not find a value greater than 0 on the credit side then it should insert whatever is in txtsalesinvoiceDiscounts.Value.

If (Me.Parent!txtsalesinvoiceDiscounts.Value <> "") Then
Me.AccountID.Value = "73"
Me.Debit.Value = Me.Parent!txtsalesinvoiceDiscounts.Value
End If

That is what is required.

Regards

Chris

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial