Subsubsubform in Base not updating MySQL DB

asked 2012-07-14 06:33:46 +0200

This post is a wiki. Anyone with karma >750 is welcome to improve it.

updated 2012-07-14 06:33:46 +0200

Anonymous

I am running LO 3.5 with JDBC as a front end for a MySQL 5.5 DB on a Win 7 box.

I have three tables (amongst others) as such:

CREATE TABLE Beneficiary
(BenID          MEDIUMINT(5) AUTO_INCREMENT,
 BenFName       VARCHAR(25),
 BenSName           VARCHAR(25),
 Phone              MEDIUMINT(5),
 Mobile         MEDIUMINT(5),
 Fax            MEDIUMINT(5),
 Email          VARCHAR(25),
 Village        VARCHAR(25),
 P_Address      VARCHAR(25),
 DOB            DATE,
 Gender         CHAR(1),
 CONSTRAINT BenID_NonNeg_CK CHECK (BenID > 0),
 CONSTRAINT Beneficiary_PK PRIMARY KEY (BenID));

CREATE TABLE Apply
(AppID              MEDIUMINT(10) AUTO_INCREMENT = 100,
 DateReceived       DATE,
 ReceivedBy     MEDIUMINT(3),
 SignedDate     DATE,
 ObDate         DATE,
 ObWitness      MEDIUMINT(3),
 ApprovedBy     MEDIUMINT(3),
 DirCommSigned      MEDIUMINT(3),
 DirSignDate        DATE,
 ProcBy         MEDIUMINT(3),
 ProcDate       DATE,
 Comments       VARCHAR(2000),
 Beneficiary        MEDIUMINT(5),
 Applicant      MEDIUMINT(4),
 AVoucher       MEDIUMINT(8) ZEROFILL,
 Benefit        VARCHAR(25),
 CONSTRAINT Emp_Received_FK FOREIGN KEY (ReceivedBy)
                              REFERENCES Employee (EmpID),
 CONSTRAINT Emp_Approved_FK FOREIGN KEY (ApprovedBy)
                              REFERENCES Employee (EmpID),
 CONSTRAINT Emp_Witness_FK FOREIGN KEY (ObWitness)
                              REFERENCES Employee (EmpID),
 CONSTRAINT Emp_DirSign_FK FOREIGN KEY (DirCommSigned)
                              REFERENCES Employee (EmpID),
 CONSTRAINT Emp_Proc_FK FOREIGN KEY (ProcBy)
                              REFERENCES Employee (EmpID),
 CONSTRAINT Rep_Apply_FK FOREIGN KEY (Applicant)
                              REFERENCES Representative (RepID),
 CONSTRAINT Ben_Apply_FK FOREIGN KEY (Beneficiary)
                              REFERENCES Beneficiary (BenID),
 CONSTRAINT AVouch_Apply_FK FOREIGN KEY (AVoucher)
                              REFERENCES AddVoucher (AVoucher),
 CONSTRAINT BenType_Apply_FK FOREIGN KEY (Benefit)
                              REFERENCES BenefitType (BenType),
 CONSTRAINT Apply_AppID_PK PRIMARY KEY (AppID));

CREATE TABLE Representative
(RepID              MEDIUMINT(4) AUTO_INCREMENT,
 RepFName       VARCHAR(25),
 RepSName       VARCHAR(25),
 Contact        MEDIUMINT(5),
 Application        MEDIUMINT(10),
 CONSTRAINT RepID_NonNeg_CK CHECK (RepID > 0),
 CONSTRAINT  Rep_PK PRIMARY KEY (RepID));

I have form to input data into these tables as: Main Form = Beneficiary info with Apply as a subform to automatically update the Beneficiary field in that table and Representative as a subform in Apply to update the Application field in that table. I then moved the Applicant field in Apply to another subform of the Representative subform to automatically update that field to match the RepID PK.

This all seems to work, and everything goes into the MySQL DB just fine EXCEPT for the Applicant Field. This field is updated in Base and matches the RepID field, but the field stays NULL in MySQL. Is this a bug? Is something structured incorrectly? Is it just too many subforms?

Cheers