What about a bit of code inserted in the AFTERUPDATE property of the texbox? The code below creates a recordset based on the table that needs editing and then finds the right record, then does the work. This is used commonly for accessing any table, or query from a form that may not be the form's recordsource.
Dim MyDb As database,MySet As Recordset
Set MDb=DBEngine.WorkSpaces(0).Databases(0)
Set MySet=MyDb.OpenRecordset("MyTable")
MySet.MoveFirst
Do Until MySet.EOF
If MySet![MyField] =Me![MycorrespondingFormField] Then
MySet.Edit
MySet![MyOtherField]=MySet![MyOtherField]-Me![MyValue]
MySet.Update
MySet.MoveNext
Loop
MyDb.Close
MySet.Close
The names used above are -
MyTable = the tablename where the records are stored.
MyField = the fieldname of the field to work on in the talbe.
MyCorrespondingFormField= the matching field on the form.
MyOtherField = the table field to be edited.
MyValue = the subtracted amount or the fieldname of the amount to be subtracted.
You could, to save processing time, exit the routine when the matching field has been edited by adding the lines -
MySet.Close
Exit Sub
after the MySet.Update line
You could also check to see if no matching records were found by creating a flag in the routine which is set if the record is found. When the loop finishes and the flag is not set then you could advise that the matching wasn't found.
I hope this is what you're after.
Best of luck,
Dave Eyley
