sum the values of list box

[mmahmod]

Dear All

i have a list box based on query for invoice no & invoice amount
how can i do the following
1- sum all the values of the list box (sum of invoices amount) in unbound text field
2- sum the selected invoices amount (if i select two or more invoices from the list) in unbound text field
3-how can i append the selected invoices from the list to append a table with the same fields by VBA code

Thanks in advance

 

[Beetle]

1- sum all the values of the list box (sum of invoices amount) in unbound text field

If you want to sum all of the values then just use DSum to sum the Amount field from the table or query the list box is based on. You could do this in the Control Source of an unbound text box. Example;

=DSum("[AmountField]", "[YourTable]")

2- sum the selected invoices amount (if i select two or more invoices from the list) in unbound text field

The following code example assumes a list box named lstInvoices and an unbound text box named txtTotal. You could place this code behind a command button or in the After Update event of the list box (this example is for the latter). This also assumes that the Amount is in the second column - Column(1) - of the list box;

Private Sub lstInvoices_AfterUpdate()

    Dim curTotal As Currency
    Dim varItem As Variant
    
    For Each varItem In Me!lstInvoices.ItemsSelected
        curTotal = curTotal + Me!lstInvoices.Column(1, varItem)
    Next
    
    Me!txtTotal = curTotal
    
End Sub

3-how can i append the selected invoices from the list to append a table with the same fields by VBA code

My advice here would be not to redundantly store the same data in two different tables.

 

[mmahmod]

thanks Beetle

1- its is worked perfect
2- always get zero , i don't know why
3- i need it as you see in attachment to update junction table "transaction"
after the user select the invoices then he click on apply to go to junction table
4- i don't know how to chose multi record from the list , i tried ctrl & shift and its not working

i hope you answer me this and thanks for your support

 

[mmahmod]

sorry i wasn't activate the multiselect in list box
and no 2 & 4 are solved
still no 3
to append the selected invioces to the transaction table when i hit the command button "Apply"


thanks

 

[Beetle]

So, just so I'm clear, you want to append the ReceiptID form the main form and the selected invoice numbers from the list box to the Transactions table and then refresh the transactions sub form to display those new entries?

 

[mmahmod]

yes thats completly right

i want append three field to append in transcation sub form :
1- receipt id from mani form to receipt id in transaction table
2- invioce no from list box to invoice no in transaction table
3-the remaining field from list box to paid field in transaction table

then refresh the list box
and refresh the subform

thanks

 

[Beetle]

Before answering your main question I do have a few (unsolicited) comments.

First, you've got some normalization issues that you might want to address;

• In your Invoices table you're storing both the sales_man_id and sales_man_name. The only value you should need to store here is sales_man_id. The name can be retrieved from the sales_men table whenever you need it. If you want some type of concatenated value including both the id and the name (which is what you have in the sales_man_name field in the Invoices table), then you would do that in a query, not directly in the table.
  
• Also in your invoices table, you have fields for customer_no and customer_name, yet you have no Customers table (at least not in the example db you posted). Are you requiring your users to manually enter this information for each record? If so, you shouldn't be. It's unnecessary work for the users and leads to inconsistent and/or invalid data in your application.
  
• You also have an Autonumber ID field in your receipts table but you're using the receipt_no field as the Primary Key, so the Autonumber ID field is basically pointless.

Second, I'm not sure I understand the logic of the way your form is set up. For example, let's take receipt_no 6001. This receipt has an amount of 30,000.00 and the sales man is ID RD1-4033. The list box on your form displays all the invoices for this particular sales man. One of those invoices (IN0027600371) is for the amount of 51,178.40. Now let's suppose that a user applies that invoice to the transaction table against receipt 6001. You now have an invoice of 51,178.40 related to a receipt of 30,000 and you appear to have no way of accounting for (or preventing) the discrepancy. Was that your intent in designing this form?

Also, you don't have a compound index on the receipt_id and invoice_id fields in the Transactions table so you have no current way of preventing the same invoice being applied to the same receipt multiple times

Circling back to your original question, you would need to put some code in the Click event of the Apply button to append (insert) the correct values to the Transactions table. My advice would be to write the receipt number and the invoice number, but not the invoice amount. The invoice amount can be retrieved from the Invoice table whenever needed. Additionally, if someone enters the incorrect amount for a given invoice and it is not discovered until sometime later, you should only need to correct it in the Invoice table (not the Invoice table and the Transaction table.

The code for your command button would look something like;

Private Sub apply_Click()

    Dim varItem As Variant
    Dim strSQL As String
    
    For Each varItem In Me!List_remain.ItemsSelected
        strSQL = "Insert Into transactions (recept_id, invoice_id) Values(" _
                & Me.receipt_no & ",""" & Me.List_remain & """)"
        CurrentDb.Execute strSQL, dbFailOnError
    Next
    
    Me.Requery
    
End Sub

(note that the recept_id field is mis-spelled in the above, that's because it's mis-spelled in your table).

Before you try to implement a procedure like this, I would suggest that you try to correct some of your other issues first.

 

[mmahmod]

Dear Beetle

thanks for you helpful notes

let me explain some pionts

the table of invoices
in fact i get all the invoices from application from work , its for piont of sales called SFO , so i get the invoices export from it and paste im my table in access and thats way i design the table with the same fields and i can't change it
i use MS Access becuse i need to made a file tracker for the paid/unpaid/remaning invoices & track sales men transactions
becuse i cant do that in SFO (no support from IT)

table of transaction
you are right that i need compound primary key to aviid the same receipt pay the same invoice again , i didn't think in that before

receipt table
you are right , no use of ID auto number

append transaction
I face 3 condition for payment
1 One payment against one invoice
2 One payment against multiply invoices
3 Partial payment against one invoice

And regarding to point 3 I will explain more , sometime the sales men give me the payment for the invioce in two different ways
Ex : I have invoice with 150,000 , the sales man submitted to me 50,000 and 100,000 direct deposit in bank
So I have to do Partial payment to invoice with 50,000 and another with 100,000 and I can’t make it in one payment
Because the cash & bank different accounts and I take entries on our financial system by banks and cash


So that’s way I make the relationship many to many between invoices and payment
And I need to know if I query for any invoice , show me how it paid


and I see that case is handle in some system like oracle with apply the payment to the invoices

So also i need to add in the command button "Apply " to finalize the three condition of payments

i mean if the invoice is less than the payment then it should pay
and also if the invoices selected equal to receipt then it should pay
and if the payment less than the invoice then it should paid partial

i hoped that i cleared some pionts to you

and thanks for your suuport
i hope to hear from you soon for my issue

 

[mmahmod]

the code of apply didn't work with me and there is an error msg

and also how i finalize the 3 condition of payment as i explain in the last post of me
i attached the file after writing the code in it

kindly your support