Master, and Child, and Strings (Oh My!)

[Daryl]

I have a form consisting of a few sub-forms. My problem is to get one sub-form to be linked to the master as follows:

Form_num (master) to Form_RefID (sub-form)

Easy enough, but here’s the catch. The Form_RefID field is supposed to be the Form_num field minus its last 4 or 5 characters. Examples: If Form_num = CG0010 0600; Form_RefID = CG0010; If Form_num =WVMS-2 0785 the Form_RefID = WVMS-2; If Form_num =L3704 1098 the Form_RefID = L3704.

As I see it, I need to be able to read the string in Form_num and then take the left portion of the field (or string?). Here’s another catch: The data lenghth in the Form_num field varies and I need to ignore the last 4 or 5 digits. How do I get the field data from Form_num except for the last 4 or 5 digits? And then how do I go about linking the form to sub-form?

If it’s any help, the sub-form uses the following query.

SELECT tbl_FormRules.Form_RefID, tbl_FormRules.RuleLoc, tbl_FormRules.TerrImpact, tbl_FormRules.AttachRule
FROM tbl_FormRules
ORDER BY tbl_FormRules.Form_RefID, tbl_FormRules.TerrImpact;

I’m a bit perplexed and would appreciate any help that you can provide.

 

[Pat Hartman]

You will have a much easier time with this database if you separate the key data in the table referenced by the subform into two columns. As it stands now, you can't enforce referential integrity or specify cascade update. It is ALWAYS poor practice to lump separate pieces of data into a single field.