Automatically changing data in subform from control in main form

[metrodub]

Bear with me on this one.

I have an invoice form that links data from three databases. The main form is bound to a table in an Invoice DB (the main DB for the form). I have an unbound combobox (cboInvoiceVendor) used to select a Vendor from the Partner DB. The AfterUpdate event of this combobox requeries another combobox (cboPONumber) so that it shows the related PO Numbers for the Vendors. This data comes from a table linked from a PO DB. I also have a subform that displays the current amount (txtPOAmount) of the PO chosen from cboPONumber.

There are also a number of textboxes to input invoice amount (travel, consultation, air, etc). An unbound textbox (txtInvoiceTotal) keeps a summation of the line items for the invoice.

What I'd like to do is have the PO Amount (txtPOAmount) decrease by the amount shown in txtInvoiceTotal.

How would I go about doing that? I've tried a simple AfterInput event in txtInvoiceTotal that didn't work.

[Forms]![frmInvoice]![sfrmPOAmount].Form![txtPOAmount] = [Forms]![frmInvoice]![sfrmPOAmount].Form![txtPOAmount] - Me.txtInvoiceTotal

Here are the table layouts/relationships:

tblInvoices
InvoiceID
POID
InvoiceNumber
InvoiceDate
A few more fields

tblPO linked from PO_be.mdb
POID
PONumber
POAmount
CompanyID

tblCompany linked from partner_be.mdb
CompanyID
CompanyName
A few more fields

Any suggestions as to how I would go about getting this to work? Is it possible to change records in another database?

Thanks in advance.

 

[Ashank4vba]

Hello,

Dont know if u've tried this yet..but what about OnChange event for the text box?

 

[metrodub]

Thanks for the suggestion, but that didn't work either.

 

[Ashank4vba]

I tried it too. If I give a default value to one of the text boxes and then when the value changes, it seems to work using the OnChange event

 

[metrodub]

For some reason it isn't working on this end. The txtPOAmount field changes when I choose a different PO from cboPONumber, but when I plug in line items into the form and txtInvoiceTotal changes, txtPOAmount remains the same.

 

[Ashank4vba]

Did you try putting in a default value for txtInvoiceTotal and include you subtraction code in the OnChange event of the txtInvoiceTotal?
Then, when the txtInvoiceTotal value changes on summation, it might work?
Dont know if u've tried this yet - it should work.

 

[metrodub]

That's exactly what I have. Default value of txtInvoiceTotal is 0. The subtraction code (which I presume to be correct in referencing the control in the subform) is in the OnChange event of txtInvoiceTotal.

I don't think the fact that txtPOAmount's control source is from a different database should hinder an attempt to change its data.

 

[PHV]

Have you checked (with a MsgBox call for example) that the Change event is triggered ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[metrodub]

We might be on to something. I added

MsgBox "How come you aren't subtracting", vbOKOnly

and when I changed the amount in the hotel cost field (causing txtInvoiceTotal to change), the message box did not appear.

Now, why would you suppose an event trigger wouldn't fire?

 

[Ashank4vba]

I tried it. It triggers with manual change, you know using keyboard. Automatic change using buttons for instance, doesnt work

 

[metrodub]

Unfortunately, I cannot manually change txtInvoiceTotal. Despite being unbound (at least to a table), its controlsource is the sum of the invoice line items (air, hotel, delivery, materials, miscellaneous, etc).

 

[PHV]

Try the AfterUpdate event procedure of the subform.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ashank4vba]

A simple button to do the subtraction should do the trick

 

[metrodub]

PHV,

What code would I put in the AfterUpdate event of the subform? If a user chooses a PO (updating the subform), they still haven't filled in any of the line items of the invoice to use to subtract.

Ashank4vba,

Thanks. I wanted to stay away from using a button, but you're right...it did the trick.

I will work on trying to get the subtraction to work without using a button.

 

[metrodub]

Here's a reason why I wanted this action to be automatic...

Say the PO started at $10,000. The end user inserts the line items for the invoice (say $2500 for delivery, $2000 for air). The txtInvoiceTotal field calculates to $4500. The user presses the subtract button and $4500 is subtracted from the $10,000, leaving $5500.

The user then realizes that the air should have been $1000 instead of $2000. They change the field and the txtInvoiceTotal field increases to $6500. If the user presses the subtract button, the $5500 will become -$1000.

How would I change the code in the click event so that any change/added debit would subtract the correct amount?

 

[metrodub]

Just realized my math was off...change from $2000 to $1000 should have changed the invoice total to $3500 and not $6500. The PO total would then be $2000.

 

[Ashank4vba]

You could have a global variable in the form module into which you can copy the PO value when it is first assigned in the relevant combobox afterupdate event.

Then in the subtract code, instead of using the existing PO amount value, you could use the global variable which remains the same for each PO amount assignment.

You could also try the 'Oldvalue' Field of the PO textbox but I dont know if it will work if the user makes more than 2 subtractions.

 

[metrodub]

I'm not that well versed in VBA.

In the Form module, I would create say Dim POOriginal as Currency?

Then in the afterupdate event of cboPONumber I would state Set POOriginal as POAmount?

Then subtract the invoice total from the POOriginal?

I'm unsure as to how any changes in the line items would return the amount back to the original.

 

[Ashank4vba]

Well, either the user can click 'subtract' button again after changing some line item.
(Or)
You could use the Onchange event for each lineitem to invoke the subtract button_click() again