Updating two tables with one form

[jendendav]

Have been following several threads on this subject. Lack of coding experience is hampering me. I have:

Table tstPdt
ProductID
ProductDescription
ReorderLevel
LeadTime
SOH (stock on hand)

Table tstInvTra
TransactionID
TransactionDate
ProductID
Units
TransactionDescription
BinLocation
TransType
SubType

Form tstAddStock (made with wizard from tstInvTra)
TransactionDate
ProductID
Units
TransactionDescription
BinLocation
TransType
SubType

Code to make form update Table tstPdt

Units on Exit
Private Sub Units_Exit(Cancel As Integer)
Update

![tstPdt]
Set [tstPdt]![SOH] = [SOH] + [Forms]![tstAddStock]![Units]
where

![tstPdt]![ProductID] = [Forms]![tstAddStock]![ProductID]
End Sub

I get compile errors. Am assuming that something has to be defined but not really sure what I'm doing. Would really appreciate some input. Also, wondered if there was a problem with SOH kind of looping back on itself.

Thank you

 

[PaulF]

DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE tstPdt SET tstPdt.SOH = [soh]+[forms]![tstaddstock].[units]" _
& " WHERE (tstPdt.ProductID)=[forms]![tstaddstock].[productid]&quot
DoCmd.SetWarnings True


PaulF

 

[jendendav]

Way cool. If you check on this again I want to check my understanding of what happened here.

Does setting DoCmd.SetWarnings False tell tstPdt that the value for SOH is no longer true? Then after update DoCmd.SetWarnings True tells tstPdt that the new value is true?
Udate tells it which table (or even a query?) to go to, set tells it which field to work on, everything after = to quote marks is the applicable math and where tells it what the relationship is between the tables?
I assume from this that there must be a relationship or you would confuse the program.

This of course worked perfectly the minute I pasted and tested. Thank you very much.

 

[PaulF]

SetWarnings turns on and off the messages that Access provides whenever you attempt to add, delete or append data.

The rest is somewhat correct. You can always see what a SQL string looks like by making a query using the Wizard, then select SQL View.

PaulF

 

[jendendav]

Well shoot, fire.

Now that I have, with your help, successfully done this what you are saying makes so much sense. I knew SQL refered to query but never used SQL view because I didn't know what to do with it.

I hadn't built the update query because I didn't think it would update both tables and I wanted the form to do the math so that we would not have input errors from people doing their own math. Maybe backward but after using Access for a few years to do only simple wizard stuff, suddenly we are wanting to get into the real meat and potatoes stuff and I was finding it confusing.

Again, thank you very much.