quick question regarding changing the value of a field

[mic907]

I just wanted to know the VB code for changing the value of a field in one table from another.

What I need to specifically do is implement code in an AfterUpdate to change the value of a field in another table if a certain threshold is exceeded (in this case, entering a dollar amount greater than what I have specified). Effectively, I need this to 'flag' a contract by changing the field in another table to read "VOID".

Thanks in advance for all suggestions!

 

[Scottyk537]

This is what I do. It's probably not the best way, but it helps me debug if issues arise.

Create a subform on your Mainform that is using the other table as it's recordsource. Link this form to your main form and hide it.
Now you can write code in the afterupdate event of your control.

- if Me!MyControl > MyThreshold then
- Forms!MainForm!SubForm!SubFormControl = "VOID"
- End if

 

[Jonathan Kok]

Two ways.

You could use the various RecordSet functions to find and change the value in the second table. Assuming you've got some way of finding the correct record in the second table:

dim db,rs
set db = CurrentDB()
set rs = db.OpenRecordset("table2")
with rs
	.FindFirst "[<recordIDfield>] = " & Me![<referenceID>]
	.edit
	!fieldname = "VOID"
	.update
	.close
end with

Or, you could create an Update Query that does the same. Just make sure to turn off the warnings prior to running the query, or the user will be prompted every time you run it. And turn them back on afterwards