Need VBA Codes to link Child and Master Fields

[FA]

Hi Friends,
I want to write a VBA Code to Link MasterFields and ChildFields. I have
a form called frmNewFinding which has a subform frmFinding.
frmNewFinding is based on table "STD_Finding" which is providing the
default data to frmFinding based on table "Finding".
frmNewFinding only has one combo box "Finding Name" and based on that
combo box, the subform fields should be prepopulated with the data
coming from STD_Finding table. All the fields in both STD_Finding and
Finding tables are identical, except STD_Finding has default data which
will be used to store new findings in Finding Table.
Since the there are too many link fields, the property box is not
allowing me to link all those feilds. I need to write codes for that i
believe.

Pleaseeeeeeeeeeee help


Moe

 

[John Welch]

I don't think you are understanding link master child correctly. Linking
master and child is used for a one to many situation where you have a
subform showing many records for each record in the main form, like many
orders for each customer. In this case you would link the master and child
forms based on customerID, so the child knows to show only orders from that
customer.
If I am understanding you correctly, you are wanting to choose a named
finding type in a combo box and then have an indivivual finding receive
default values specific to that particular finding type. Is that right? Will
you want to be able to change those default values later, or will they be
static? If they are static, then you could create a field in your Findings
table called Finding_Type which links to the values in the STD_findings
table. If they are just default values that need to be changeable, then I
would recommend:
Put your combobox in the form's header and have it's rowsource be
SELECT FindingNameID, FindingName (assuming each FindingName has an ID
primary key) from STD_Finding
I'd put the Findings records in the main form and in the afterupdate event
of the combobox, and set the values of the textboxes like this:

me.txtCost = DLookup("[Cost]", "STD_Findings", "[FindingNameID] = " &
me.cmbFindingName)
me.txtDuration = DLookup("[duration]", "STD_Findings", "[FindingNameID] = "
& me.cmbFindingName)
....etc.

here txtCost is the name of a textbox on the main form, Cost and
FindingName are fields in the STD_findings table, and cmbFindingname is the
name of your combobox in the header. I'm assuming the combobox is bound to
the ID field of the findingNames table.

OR

you could do it all at once by running an update query. To do this, you
would again create a field in the findings table called findingType or
findingName, that would link this table to the STD_findings table. Then
create a query that joins these two tables on this field. Make it an update
query and set each field in the findings table to be updated to the
corresponding field in the STD_findings table. In the criteria box under
FindingID, put: forms!YourFormName.TextBoxThatContainsFindingID so that
only the record that is currently displayed on the form will get updated.
Then run this query from the afterupdate event of your combobox. Then
refresh the form.

Hope this helps, good luck
-John

 

[George Nicholson]

Me.sctlSubform.LinkChildFields = "[FieldOne];[FieldTwo]"
Me.sctlSubform.LinkMasterFields = "[FieldOne];[FieldTwo]"

Where Me is the Parent form and sctlSubform is the name of the control on
the Parent that contains the subform. (the control name may be the same as
the subform name, but if it isn't, you need to be sure and use the control
name).

HTH,