Complex rules and working with MS Access: SOLVED

Here's some complex rules to demonstrate what can be attempted with success,
I hope they are useful to someone else:

public,rule_ttransaction,_kit,CREATE RULE _kit AS ON INSERT TO
rule_ttransaction DO INSTEAD NOTHING;--this is needed to convince postgres
we won't try to update the view directly with any of the following rules
(which would lead to an infinite loop).

public,rule_ttransaction,i_kit,CREATE RULE i_kit AS ON INSERT TO
rule_ttransaction WHERE (new.ttype = 'I'::character varying) DO INSTEAD
(UPDATE tjob SET "Issued" = (((new.quantity)::text)::integer +
((tjob."Issued")::text)::integer) WHERE (tjob."Jobnumber" =
new.disposition); INSERT INTO ttransaction (ttype, item, quantity,
disposition, cost) SELECT 'O'::character varying AS "varchar", new.item,
new.quantity, concat_text('BOM-'::text, (new.disposition)::text) AS
concat_text, tabl."AveCost" FROM (SELECT titemmaster."Item",
titemmaster."AveCost" FROM titemmaster) tabl WHERE (tabl."Item" = new.item);
UPDATE titemmaster SET "OnOrder" = (new.quantity + titemmaster."OnOrder")
WHERE (titemmaster."Item" = new.item); UPDATE titemmaster SET "Available" =
(titemmaster."Available" - (new.quantity * tble."ItemQty")), "Allocated" =
((new.quantity * tble."ItemQty") + titemmaster."Allocated") FROM (SELECT
tnewbom."KitPartNumber", tnewbom."Item", tnewbom."ItemQty" FROM tnewbom)
tble WHERE ((tble."KitPartNumber" = new.item) AND (titemmaster."Item" =
tble."Item")); INSERT INTO ttransaction (ttype, item, quantity, disposition,
cost) SELECT 'A'::character varying AS "varchar", tbl."Item", (new.quantity
* tbl."ItemQty"), concat_text('BOM-'::text, (new.disposition)::text) AS
concat_text, tbl."AveCost" FROM (SELECT tnewbom."KitPartNumber",
tnewbom."Item", tnewbom."ItemQty", titemmaster."AveCost" FROM (tnewbom JOIN
titemmaster ON ((tnewbom."Item" = titemmaster."Item")))) tbl WHERE
(tbl."KitPartNumber" = new.item); );

public,rule_ttransaction,invoice_part,CREATE RULE invoice_part AS ON INSERT
TO rule_ttransaction WHERE (new.ttype = 'V'::character varying) DO INSTEAD
(INSERT INTO ttransaction (ttype, item, quantity, disposition, cost) SELECT
'I'::character varying AS "varchar", new.item, new.quantity, ('PO-'::text ||
(new.disposition)::text) AS concat_text, new.cost; UPDATE titemmaster SET
"AveCost" = (((new.cost * (new.quantity)::numeric) + ((tbl.onhand)::numeric
* tbl."AveCost")) / ((new.quantity + tbl.onhand))::numeric) FROM (SELECT
titemmaster."Item", titemmaster."AveCost", (titemmaster."Available" +
titemmaster."Allocated") AS onhand FROM titemmaster) tbl WHERE
(titemmaster."Item" = new.item); UPDATE titemmaster SET "OnOrder" =
(titemmaster."OnOrder" - new.quantity), "Available" =
(titemmaster."Available" + new.quantity) WHERE (titemmaster."Item" =
new.item); );

public,rule_ttransaction,o_kit,CREATE RULE o_kit AS ON INSERT TO
rule_ttransaction WHERE (new.ttype = 'O'::character varying) DO INSTEAD
(INSERT INTO tjob ("Jobnumber", "KitItem", "Issued", "Completed") VALUES
(new.disposition, new.item, new.quantity, 0); INSERT INTO ttransaction
(ttype, item, quantity, disposition, cost) SELECT new.ttype, new.item,
new.quantity, concat_text('BOM-'::text, (new.disposition)::text) AS
concat_text, tabl."AveCost" FROM (SELECT titemmaster."Item",
titemmaster."AveCost" FROM titemmaster) tabl WHERE (tabl."Item" = new.item);
UPDATE titemmaster SET "OnOrder" = (new.quantity + titemmaster."OnOrder")
WHERE (titemmaster."Item" = new.item); UPDATE titemmaster SET "Available" =
(titemmaster."Available" - (new.quantity * tble."ItemQty")), "Allocated" =
((new.quantity * tble."ItemQty") + titemmaster."Allocated") FROM (SELECT
tnewbom."KitPartNumber", tnewbom."Item", tnewbom."ItemQty" FROM tnewbom)
tble WHERE ((tble."KitPartNumber" = new.item) AND (titemmaster."Item" =
tble."Item")); INSERT INTO ttransaction (ttype, item, quantity, disposition,
cost) SELECT 'A'::character varying AS "varchar", tbl."Item", (new.quantity
* tbl."ItemQty"), concat_text('BOM-'::text, (new.disposition)::text) AS
concat_text, tbl."AveCost" FROM (SELECT tnewbom."KitPartNumber",
tnewbom."Item", tnewbom."ItemQty", titemmaster."AveCost" FROM (tnewbom JOIN
titemmaster ON ((tnewbom."Item" = titemmaster."Item")))) tbl WHERE
(tbl."KitPartNumber" = new.item); );

public,rule_ttransaction,order_part,CREATE RULE order_part AS ON INSERT TO
rule_ttransaction WHERE (new.ttype = 'E'::character varying) DO INSTEAD
(INSERT INTO ttransaction (ttype, item, quantity, disposition, cost) SELECT
'O'::character varying AS "varchar", new.item, new.quantity, ('PO-'::text ||
(new.disposition)::text) AS concat_text, new.cost; UPDATE titemmaster SET
"OnOrder" = (titemmaster."OnOrder" + new.quantity) WHERE (titemmaster."Item"
= new.item); );

public,rule_ttransaction,r_kit,CREATE RULE r_kit AS ON INSERT TO
rule_ttransaction WHERE (new.ttype = 'R'::character varying) DO INSTEAD
(UPDATE tjob SET "Completed" = (((new.quantity)::text)::integer +
((tjob."Completed")::text)::integer) WHERE (tjob."Jobnumber" =
new.disposition); INSERT INTO ttransaction (ttype, item, quantity,
disposition, cost) SELECT new.ttype, new.item, new.quantity,
concat_text('BOM-'::text, (new.disposition)::text) AS concat_text,
tabl."AveCost" FROM (SELECT titemmaster."Item", titemmaster."AveCost" FROM
titemmaster) tabl WHERE (tabl."Item" = new.item); UPDATE titemmaster SET
"OnOrder" = (titemmaster."OnOrder" - new.quantity), "Available" =
(titemmaster."Available" + new.quantity) WHERE (titemmaster."Item" =
new.item); UPDATE titemmaster SET "Available" = (titemmaster."Available" -
(new.quantity * tble."ItemQty")), "Allocated" = (titemmaster."Allocated" -
(new.quantity * tble."ItemQty")) FROM (SELECT tnewbom."KitPartNumber",
tnewbom."Item", tnewbom."ItemQty" FROM tnewbom) tble WHERE
((tble."KitPartNumber" = new.item) AND (titemmaster."Item" = tble."Item"));

--the next insert updates many records by referencing a BOM table (bill of
materials). This works great. Note the concat_text function could be changed
to double pipe (thanks Steve).

INSERT INTO ttransaction (ttype, item, quantity, disposition, cost) SELECT
'F'::character varying AS "varchar", tbl."Item", (new.quantity *
tbl."ItemQty"), concat_text('BOM-'::text, (new.disposition)::text) AS
concat_text, tbl."AveCost" FROM (SELECT tnewbom."KitPartNumber",
tnewbom."Item", tnewbom."ItemQty", titemmaster."AveCost" FROM (tnewbom JOIN
titemmaster ON ((tnewbom."Item" = titemmaster."Item")))) tbl WHERE
(tbl."KitPartNumber" = new.item); );

public,rule_ttransaction,receive_part,CREATE RULE receive_part AS ON INSERT
TO rule_ttransaction WHERE (new.ttype = 'D'::character varying) DO INSTEAD
(INSERT INTO ttransaction (ttype, item, quantity, disposition, cost) SELECT
'R'::character varying AS "varchar", new.item, new.quantity, ('PO-'::text ||
(new.disposition)::text) AS concat_text, new.cost; UPDATE titemmaster SET
"OnOrder" = (titemmaster."OnOrder" - new.quantity), "Available" =
(titemmaster."Available" + new.quantity) WHERE (titemmaster."Item" =
new.item); );

public,rule_ttransaction,s_kit,CREATE RULE s_kit AS ON INSERT TO
rule_ttransaction WHERE (new.ttype = 'S'::character varying) DO INSTEAD
(INSERT INTO ttransaction (ttype, item, quantity, disposition, cost) SELECT
new.ttype, new.item, new.quantity, concat_text('SALE-'::text,
(new.disposition)::text) AS concat_text, tabl."AveCost" FROM (SELECT
titemmaster."Item", titemmaster."AveCost" FROM titemmaster) tabl WHERE
(tabl."Item" = new.item); UPDATE titemmaster SET "Available" =
(titemmaster."Available" - new.quantity) WHERE (titemmaster."Item" =
new.item); );

Note all the rules are DO INSTEAD. That little detail hung me up for two
days, don't let it happen to you.

As far as working with MS Access as the front end for this, I use this code
here (thanks Microsoft website) to update the rule_ttransaction view and one
of the rules above fires depending on the letter in the ttype field, e.g. O
is a kit order, S is a kit sale. Note you'll want your form to be connected
to something other than the view with the rules on it, Access didn't like
that in my case. So I hooked the form up to the table directly, and once
I've updated it I simply '.requery' the page and the data refreshes. Slick.

You can save a lot of code by creating a module function out of this (thanks
again Steve) but I haven't debuged that yet.

Private Sub new_job_Click()

On Error GoTo Err_new_job_Click

Dim dbs As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim strConnect As String

Dim ordertype, Kit, disposition As String
Dim quantity As Integer

If IsNull(Me!it) Or IsNull(Me!di) Or IsNull(Me!qt) Then
MsgBox "Please fill in all boxes"
DoCmd.Close
Exit Sub
End If

ordertype = "I" 'This changes do to which rule I want to fire
Kit = Me!it
disposition = Me!di
quantity = Me!qt

Set dbs = CurrentDb
strConnect = "ODBC;DSN=Postgres
Access;DATABASE=backend;SERVER=10.0.0.20;PORT=5432;UID=Admin;PWD=swing747;A6
=;A7=100;A8=4096;B0=254;B1=8190;BI=0;C2=dd_;;CX=188503ab"
Set qdf = dbs.CreateQueryDef("")
qdf.Connect = strConnect

strSQL = "INSERT INTO rule_ttransaction (ttype, item, quantity,
disposition) VALUES ('" & ordertype & "', '" & Kit & "'," & quantity & ",'"
& disposition & "')"
qdf.ReturnsRecords = False

qdf.SQL = strSQL
qdf.Execute

Forms!frmJobs.Requery
DoCmd.Close
Exit Sub
Err_new_job_Click:
MsgBox Err.Description
MsgBox "Order not performed, please notify IT you saw this error."
DoCmd.Close
Exit Sub

End Sub