Help with query value replacement through Event Procedure

[dgreatjc]

I'm trying to replace a value in a query from an event procedure in a form (afterupdate). I enter a deposit amount, then I select the invoice this is for via a form. I then open up a query on all unpaid invoices. This query shows the total bill, the amount paid to date, and a check box for when the invoice is paid. I then search for the invoice I selected from the drop down box, read in the total bill, then compare the payment to see if more money is still owing, if it's paid, or if they have overpaid. Then, depending on the results, I replace the Amount Paid.

I can't get it to replace the amount in the query from the event procedure. Any help will be greatly appreciated. There is the code I have to date. The black text is what I am trying to do, and the blue text directly below is the working code. The red is where I don't know what to do.





'In form [Transactions – Deposits Subform] enter amount paid in box '[AmountSubform]
'Select the invoice number from [InvoiceNumber] dropdown box
'Execute “afterupdate” Event Procedure
'Read [AmountSubform] into variable [nowpaid]
Dim nowpaid As Currency
Dim searchrecord As Integer
nowpaid = Me!AmountSubform
'Read [InvoiceNumber] into variable [searchrecord]
searchrecord = Me!InvoiceNumber
'Open Query [Transactrions – Deposit Query]
DoCmd.OpenQuery ("Transactions – Deposit Query")
'Goto invoice number by using variable [searchrecord]
DoCmd.FindRecord searchrecord, acAnywhere, True, acSearchAll
'Read data from column [Total$] into variable [PaidTotoal]
PaidTotal = DLookup("Total$", "Transactions – Deposit Query", "QuoteID = " & Me!InvoiceNumber)
'Read data from column [AmtPaid] into variable [alreadypaid]
alreadypaid = DLookup("AmtPaid", "Transactions – Deposit Query", "QuoteID = " & Me!InvoiceNumber)
'Do check - If [alreadypaid] + [nowpaid] > [PaidTotal]
If (alreadypaid + nowpaid) > PaidTotal Then
'Return an Error saying an overpayment occurred
MsgBox "There is an error. Overpayment!"
'Close Query
DoCmd.Close
'Return to [AmountSubform] box in TransactionsDepositsSubform] Form
Me![AmountSubform].SetFocus
'End Statement
End If
'If [alreadypaid] + [nowpaid] = [PaidTotal]
If (alreadypaid + nowpaid) = PaidTotal Then
'Replace [AmtPaid] column of query with [PaidTotal] from [Total] column
THIS IS THE LINE I CAN"T FIGURE OUT
'Goto [Closed] column which is default of unchecked box 'and make it a check
THIS IS THE LINE I CAN"T FIGURE OUT Either
'End Statement
End If
If [alreadypaid] + [nowpaid] < [PaidTotal]
If (alreadypaid + nowpaid) < PaidTotal Then
Replace [AmtPaid] with the sum of [alreadypaid] + [nowpaid]
Endif
End If
Close Query

 

[Pat Hartman]

There is some conceptual problem with what you are trying to do that I can't quite put my finger on. For one thing - queries don't store data. Tables store data. For another, the recordset you are opening up is the same one you are using in the DLookup() and for the same record so there is no purpose to the DLookup(). Anything you retrieve via the DLookup() is already available just by referencing the field in the open recordset.

And last but not least, calculated values should not be stored. You should be using DSum() to summarize the data to get the total balance and that will allow you to display whatever message you need.

 

[dgreatjc]

Thank you for your reply. I know it's usually not best to modify a table as such, but I feel it is the best way for what I need. I'll give what you said some thought and see if there is another way to do it. The reason I'm not using the dsum and such, is that I'm creating a database for my business. When I make a deposit, I want to select what invoice the deposit is for. Some people pay all at once, and others 4 or 5 + payments. I need to track this efficiently and without making duplicate entries. So I felt the best way was to have 2 columns in my main table. One for total price, and another for amount paid. As they make payments, I add to the amount paid. When it is paid, I check a check box that shows it's completed, and it won't come back in the query.I have been able to do this, but the programming to do it is very poor, as I'm not very fluent with VB. If you get a chance, here is a copy of part of my database.
I go to Accounting, then Transactions. Then under the Accound column, I select Deposit. I then type a payment, and select the invoice number (I have to later modify this to show the name, not just number). This is where I then want to do my check.
Thank you for any help. Rename the Zip file to Rar

 

[Pat Hartman]

Applications that handle money especially should not work this way. Each payment should be entered as a separate transaction and if a single payment can be split among several invoices, another table is required to handle individual split records. You then sum the records in the split table by invoice to determine what is paid and what is owing. Doing it your way, you have no audit trail. If you have a question, you'll never be able to resolve it.

 

[dgreatjc]

Thanks again for your reply. I already have a table set up of all my individual transactions. I don't need to worry about this. I will try to work something out to sum these records up. However, I still would like to be able to change the check box in my query from my event procedure. I run multiple queries, and more than one is based on the job having a closed status. Can you tell me the best way to, from an event procedure, to change the status of the query. Right now, I have a check box in the footer of the form that doesn't show. I then change it's state, do an accopy, then open query, find record, and then acpaste. Just wondering if there was a better way for this.

 

[DCrake]

When I have been involved with this type of accounting I have used the reducing balance method. Whereby I would bring up a list of invoices that have an outstanding amount against them. Sorted Oldest first. I would then enter a value into a box of the amount received from the client. It would then trawl through each invoice and pay off the outstanding balance until the money ran out.

Some of my customers simply sent a cheque each month for say £2k and said take this amount off my outstanding balance. This could be say £10k spread over 4 invoices so I would pay off the oldest ones first until the £2k ran out.

A seperate table would hold each individual transaction detaling the invoice number, the amount paid, the cheque number, etc. to give an audit trail.

Sage does the same thing.

David

 

[Rabbie]

Thank you for your reply. I know it's usually not best to modify a table as such, but I feel it is the best way for what I need. I'll give what you said some thought and see if there is another way to do it.

There is always the possibilty of using a query that picks up the entire table.and if display this in a form in Datasheet mode it looks like a table but it is easier to modify later.

 

[dgreatjc]

Again, thanks for all the replies. It has been very helpful and I am almost done. I have taken the suggestions and am using another table for the paper trail. But I do wish to still modify the checkbox in my query from my event procedure. It is necessary for what I am trying to accomplish because of everything being tied to this main table. Proper or not, this is what I need to do in my particular occasion. I am now using a cut and paste method to a dummie box in my form footer, and then cut and paste to my query. Is there a simple command that would let me change a check box in query from an event procedure in my form? Any help would be appreciated.