UPDATE table from button

[aceavl via AccessMonster.com]

hi!

i need to Update a field in a table from a cmd button.
i know how to do it with a simple WHERE:
DoCmd.RunSQL "UPDATE tblEmp SET tblEmp.EmpRecords = -1 WHERE " &
strFilterNoCancelados & ""

but this one i have no clue, hope someone can help me.

the form is a serch form from Allen Browne so i need to update only the
filtered records.

i have tblEmp (employees) , tblCo (company) and tblPay (payments made).

The form source is a query: (qryAllEmpCo)
SELECT tblEmp.EmpIDPK, tblCo.CoIDPK, tblCo.CoNombre, tblEmp.CoIDFK, tblEmp.
EmpNombre, tblEmp.EmpMonto, tblEmp.EmpRecords FROM tblCo INNER JOIN tblEmp ON
tblCo.CoIDPK = tblEmp.CoIDFK;

i want to UPDATE the field (tblEmp.EmpRecords) but only the employees that
haven't finished paying or haven't paid anything

the field to update is a yes/no field and i want to update it to -1

any help is appreciated, thank you!

 

[Chris O'C via AccessMonster.com]

You need to create an update query that joins tblEmp with tblPay. It's not
clear what your structure is for tblPay, and by your description, the field
EmpRecords probably doesn't belong in the tblEmp table, but in a separate
table that includes the primary key of tblEmp as a foreign key. It's also
not clear how you would determine if an employee has a balance owed.

Anyway, assuming EmpRecords is in the right table and comparing two fields in
the same record will determine whether the employee owes money, the structure
of your update query with the join on the two tables and the where clause
would look like this:

UPDATE tblEmp INNER JOIN tblPay on tblEmp.EmpIDPK = tblPay.EmpIDFK
SET EmpRecords = -1
WHERE (AmountPaid < AmountOwed)

Make sure you back up your db before experimenting with update queries.

Chris
Microsoft MVP

 

[Chris O'C via AccessMonster.com]

And the where clause would also include your filter.

Chris
Microsoft MVP

 

[Jan Baird]

Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.

 

[Jan Baird]

Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.