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