Complex rules and working with MS Access: SOLVED

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