Change Link Child & Master Fields in VB

[MPat]

Hi!! I've been trying to figure this out..

I have a form with a subform and I need to display 2 different things in this subform, that's why I came with the idea of changing the Link Child Field and the Link Master Field.
This change should happen when clicking a button, so it would be helpful if somebody has an idea of how to change these fields in Visual Basic!!


The links between the forms actually work if I change the fields manually, but I want to use a button to do the same.

Name of the form: BaseForm
Name of subform: BaseSubform
Name of the table in use = Members
Name of the Field of the table I want to use = Assign

 ...
 
Private Sub ViewAllButton_Click()
 
    Forms!BaseForm.MasterRecordset = Members.Assign
    Forms!BaseForm.ChildRecordset = Members.Assign
 
End Sub
 
...

I appreciate your help!

[ADezii]

Hi!! I've been trying to figure this out..

I have a form with a subform and I need to display 2 different things in this subform, that's why I came with the idea of changing the Link Child Field and the Link Master Field.
This change should happen when clicking a button, so it would be helpful if somebody has an idea of how to change these fields in Visual Basic!!


The links between the forms actually work if I change the fields manually, but I want to use a button to do the same.

Name of the form: BaseForm
Name of subform: BaseSubform
Name of the table in use = Members
Name of the Field of the table I want to use = Assign

 ...
 
Private Sub ViewAllButton_Click()
 
    Forms!BaseForm.MasterRecordset = Members.Assign
    Forms!BaseForm.ChildRecordset = Members.Assign
 
End Sub
 
...

I appreciate your help!

You can use the LinkChildFields and LinkMasterFields properties together to specify how Microsoft Access links records in a form to records in a subform. If these properties are set, Microsoft Access automatically updates the related record in the subform when you change to a new record in a main form.

'Assuming [Assign] is the Linking Field Name in both Parent and Child
Forms!BaseForm!BaseSubform.LinkMasterFields = "Assign"
Forms!BaseForm!BaseSubform.LinkChildFields = "Assign"

[MPat]

You can use the LinkChildFields and LinkMasterFields properties together to specify how Microsoft Access links records in a form to records in a subform. If these properties are set, Microsoft Access automatically updates the related record in the subform when you change to a new record in a main form.

'Assuming [Assign] is the Linking Field Name in both Parent and Child
Forms!BaseForm!BaseSubform.LinkMasterFields = "Assign"
Forms!BaseForm!BaseSubform.LinkChildFields = "Assign"

Thank you very much ADezii !!

it was really helpful and worked perfectly!

[ADezii]

Thank you very much ADezii !!

it was really helpful and worked perfectly!

Glad we can help you here at TheScripts.

[sierra7]

Hi

I have come across this thread because I am doing a similar sort of thing and was trying to resolve the error message, "You must use the same number of fields when you set the LinkChildFields and LinkMasterFields properties", even though both are being set the same.

I have a 'control panel' form with a sub-form window into which I am switching in and out six different 'detail' forms but some are linked by one field and others on two or three.

I have been changing the 'windows' (sub-forms) dynamically via command buttons in the control panel; running code to reassign the Master/Child links pretty much as described above.I don't always get the error; it depends upon what is being swapped and when. I had tried changing the object before the links, after the links; then Child bedfore Master etc. to no avail. In one instance the error even occurs when the two forms being swapped have the same three link fields!

I have finally found the fix is to first set the link fields to blank, then change the source object, finally reassign the links as appropriate, thus;-

'de-assign link fields 
  Me.DetailWindow.LinkChildFields = ""
  Me.DetailWindow.LinkMasterFields = ""
 
'replace sub-form with new source
  Me.DetailWindow.SourceObject = "NewFormName"
 
'reassign links as appropriate for NewFormName
  Me.DetailWindow.LinkChildFields = "FieldName1, FieldName2, FieldName3"
  Me.DetailWindow.LinkMasterFields = "FieldName1, FieldName2, FieldName3"

Which like all good puzzles if obvious when you know the solution! I hope someone finds this usefull