pavaneesh_kumar
asked on
Update Query
I have prepared following update query:
UPDATE Reception LEFT JOIN Finance ON Reception.[Invoice Login No] = Finance.[Invoice Login No] SET Finance.[Invoice No] = Reception![Invoice No], Finance.Amount = Reception![Invoice Value]
WHERE ((([Forms]![Finance1]![Inv
This query is executed through a macro which runs after one has exited from <Invoice Login No> field of Finance form. This query gets executed only when I right click on the field and select "Remove Filter/ Sort"
If option available after right click is not used then following error message comes and the subsequent fields are not updated:
"Microsoft Office Access can't update all the records in the update query
Microsoft Office Access didn't update 0 field(s) due to a type conversion failure, 1 record(s) due to key violations, 0 record(s) due to lock violations and 0 record(s) due to validation rule violations."
UPDATE Reception LEFT JOIN Finance ON Reception.[Invoice Login No] = Finance.[Invoice Login No] SET Finance.[Invoice No] = Reception![Invoice No], Finance.Amount = Reception![Invoice Value]
WHERE ((([Forms]![Finance1]
why the left join, do u want to populate if null?
why dont u create a new Reception record if it does not have a invoice login no that dont exist
or simply update the amount if it does
why dont u create a new Reception record if it does not have a invoice login no that dont exist
or simply update the amount if it does
ASKER
The Database deals with invoice records.
First the invoices are entered in "Reception" Table where Invoice Login No is generated automatically and acts as a primary key for the table. Then it comes to finance table where another set of users enter other details. But for reference purposes three fields have same data as Reception table. In "Invoice Login No" field of "Finance" table the same invoice no is entered as was enetered in "Reception" Table. The update query picks up the "Invoice No" and "Invoice Amount" from "Reception" table.
First the invoices are entered in "Reception" Table where Invoice Login No is generated automatically and acts as a primary key for the table. Then it comes to finance table where another set of users enter other details. But for reference purposes three fields have same data as Reception table. In "Invoice Login No" field of "Finance" table the same invoice no is entered as was enetered in "Reception" Table. The update query picks up the "Invoice No" and "Invoice Amount" from "Reception" table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If there is no record in the Finance table with a matching Invoice No, then a new Finance record should be created, which does have that Invoice No.
AW
AW
Your update is probably causing a field(s) which is unique, to be duplicated
check the data and what fields your updating