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?

Sales-accounting.png

Regards

Chris
LVL 3
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
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.ScDirectorAuthor Commented:
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:


Sales-accounting-Two.png
Gustav BrockCIOCommented:
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.ScDirectorAuthor Commented:
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
Gustav BrockCIOCommented:
That could be something like this:

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"
        If Nz(Me!Credit.Value, 0) = 0 Then
            Me.Debit.Value = Me.Parent!txtsalesinvoiceDiscounts.Value
        Else
            Me.Debit.Value = 0
        End If
    End If

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.