I'm having a bit of a problem figuring out how to do this...
I have a form (single form) and a subform (datasheet which is based on a query which includes the data elements [via outer join] in the single form). What I'm trying to do is have the data sheet form include (view only) all records from the query and have the single form display selected fields for edit/entry (will update a table that is the outer join in the query).
Example:
Table A has "Policy Number", "Line of Coverage", and "Insured ID".
Table B has "Policy Number", "Insured ID", and "Insured Name".
Query 1:
SELECT A."Policy Number", A."Line of Coverage", A."Insured ID", B."Insured Name"
FROM "Table A" A, "Table B" B
WHERE A."Policy Number" = B."Policy Number" (+)
AND A."Insured ID" = B."Insured ID" (+)
(+) means outer join (sorry i'm used to using oracle).
So in the form I have a single form (FORM X) bound to Table B and a datasheet subform (FORM Y) bound to Query 1. I would like the user to be able to edit (or add if it doesn't exist) the "Insured Name" in FORM X based on the record currently selected in FORM Y.
Other things that may monkey with the process are the fact that even though Table B will have a unique key based on "Policy Number" and "Insured ID" Query 1 (and Table A) may have multiple "Line of Coverage" based on a single "Policy Number" and "Insured ID" combination.
Alternatively not relating the two forms, but simply populating the FORM X based on the record selected in FORM Y would be sufficient. Would this involve populating FORM X using the "on focus" function and how do I use vb to reference to a specific record in my subform?
Please let me know if I am not making any sense and thank you for your help.
-Eric
I have a form (single form) and a subform (datasheet which is based on a query which includes the data elements [via outer join] in the single form). What I'm trying to do is have the data sheet form include (view only) all records from the query and have the single form display selected fields for edit/entry (will update a table that is the outer join in the query).
Example:
Table A has "Policy Number", "Line of Coverage", and "Insured ID".
Table B has "Policy Number", "Insured ID", and "Insured Name".
Query 1:
SELECT A."Policy Number", A."Line of Coverage", A."Insured ID", B."Insured Name"
FROM "Table A" A, "Table B" B
WHERE A."Policy Number" = B."Policy Number" (+)
AND A."Insured ID" = B."Insured ID" (+)
(+) means outer join (sorry i'm used to using oracle).
So in the form I have a single form (FORM X) bound to Table B and a datasheet subform (FORM Y) bound to Query 1. I would like the user to be able to edit (or add if it doesn't exist) the "Insured Name" in FORM X based on the record currently selected in FORM Y.
Other things that may monkey with the process are the fact that even though Table B will have a unique key based on "Policy Number" and "Insured ID" Query 1 (and Table A) may have multiple "Line of Coverage" based on a single "Policy Number" and "Insured ID" combination.
Alternatively not relating the two forms, but simply populating the FORM X based on the record selected in FORM Y would be sufficient. Would this involve populating FORM X using the "on focus" function and how do I use vb to reference to a specific record in my subform?
Please let me know if I am not making any sense and thank you for your help.
-Eric
Last edited:
