Hello,
As most of you know I'm working on a a project that involves 3 different tables: "Family," "Applicant" and "Charges." From my previous thread I managed to make the "Edit page" work but when attempting to use the same code(provided below) for the "Add page" it didn't work. The SQL code is supposed to copy the "GROUP_ID" field from the group table to the "FAMILY_ID" field in the "Charges" table, then add all entries that share the same "FAMILY_ID" and finally update the "TOTAL" field in the "Group" table.
//INHERIT FAMILY_ID
string strFamilyID = "UPDATE C SET C.FAMILY_ID = (SELECT FAMILY_ID FROM APPLICANT A WHERE A.APPLICANT_ID = '" + values["APPLICANT_ID"].ToString() + "') FROM APPLICANT A, CHARGES C WHERE C.APPLICANT_ID = '" + values["APPLICANT_ID"].ToString() + "'";
CommonFunctions.db_exec(strFamilyID , null);
//CALCULATE "TOTAL" FOR CURRENT FAMILY BASED ON LATEST ENTERED CHARGE
string strTotalUsed = "UPDATE F set F.TOTAL_USED = (SELECT SUM(PASS_PAID) CHARGESUM FROM CHARGES C WHERE C.FAMILY_ID ='" + values["FAMILY_ID"].ToString() + "')FROM FAMILY F, CHARGES C WHERE F.FAMILY_ID = '" + values["FAMILY_ID"].ToString() + "'";
CommonFunctions.db_exec(strTotalUsed, null);
Unfortunately, only my "FAMILY_ID" updates and my "TOTAL" field remains blank. I believe its because both parts of the code are executing at the same time, the second half doesn't have a "FAMILY_ID" to compare it to. I tried putting the first half in the "Before record added" event but not joy. I modified the code to set the value for the "FAMILY_ID" field but my syntax must be wrong because I get an error.
values["FAMILY_ID"] = "SELECT FAMILY_ID FROM APPLICANT A WHERE A.APPLICANT_ID ='"+data["APPLICANT_ID"].ToString()+"'";