Solved Theoretical question

[so10070]

It is possible to make a link between child and master through VBA, like this

        .RecordSource = sqlOffertesWijzigenBasis
        .subfrmOfferteProductenWijzigen.Form.RecordSource = sqlProductOffertesWijzigen
        .subfrmOfferteProductenWijzigen.LinkChildFields = "OFID"
        .subfrmOfferteProductenWijzigen.LinkMasterFields = "OFID"

It is also possible to make the link between child and master through the properties. But even every form (child and master) has its own recordsource defined in the "Form_Load" it is obliged to fill in explicitly the recordsource of the master in the properties.

Why?

 

[Gasman]

Flexibility?

it is obliged to fill in explicitly the recordsource of the master in the properties.

Are you sure about that?

 

[The_Doc_Man]

I am not sure I understand your question.

Your displayed code accurately shows how you would dynamically link parent and child forms and the .Recordsource for the parent and for the child. You are correct that you could define these properties statically in design view using the properties grid. In fact, that is the preferred way.

I don't understand your reference to the Form_Load event. Unless the latest version of Access 365 has a requirement that I haven't seen before, there is absolutely no obligation to load ANY of those properties in code for ANY event. They should all work perfectly by statically defining them from design view. You are not obliged to make any such definitions - UNLESS the creator of the database is doing something extremely unusual.

 

[CJ_London]

If you have already defined the relationship the link properties are populated automatically - but you can delete or use different fields.

you can also use two or more fields, separated by a comma

e.g. you might have a main form based on customers and a subform based on invoices either the link based on customer Id. But you could add an unbound control on the main form to specify say invoice type (e.g. invoice/credit) and add this control name to the link master property (e.g. customerpk, cboinvtype) and the link child property Customerfk, invtype)

 

[XPS35]

Crossposted here https://www.helpmij.nl/forum/showthread.php/965826-Theoretische-vraag#post6338680

 

[Pat Hartman]

Why not stand back and get out of the weeds. Tell us what business process you are trying to implement. What you are asking to do implies that you are not bothering with any data validation if you are changing the RecordSource of the subform. What do you think you are saving by overloading a form this way?

 

[so10070]

Thanks all for the very useful information. As I understand it, one have to choose what method he or she will use.

 

[gemma-the-husky]

I don't think you would normally do this at run time, unless you have some dynamic process where you changed the subform sourceobject programmatically. Generally it's easier to assert the master-subform relationship in the form design

 

[GPGeorge]

I am not sure I understand your question.

Your displayed code accurately shows how you would dynamically link parent and child forms and the .Recordsource for the parent and for the child. You are correct that you could define these properties statically in design view using the properties grid. In fact, that is the preferred way.

I don't understand your reference to the Form_Load event. Unless the latest version of Access 365 has a requirement that I haven't seen before, there is absolutely no obligation to load ANY of those properties in code for ANY event. They should all work perfectly by statically defining them from design view. You are not obliged to make any such definitions - UNLESS the creator of the database is doing something extremely unusual.

In one very obscure situation--the details of which are lost in history--I had to resort to setting the Master and Child linking fields in a main form subform design during runtime. What I do remember is that the predecessor who'd created the original application had done something that made it hard to accomplish what I needed to so--refresh data after some update process ran--which made this the path of least resistance short of a whole new set of forms. I wish I could remember the specific problem that led to this. All I recall is that it was the first and last time I did it, or wanted to do it.

 

[The_Doc_Man]

Thanks all for the very useful information. As I understand it, one have to choose what method he or she will use.

True enough - but unless you are doing something to extensively alter a LOT of things at run-time, that choice is HEAVILY weighted to defining all of those factors during design time so that your DB comes up with them already loaded so that you don't have to do anything else. They are just there, in-place and ready to go. Dynamic redefinition at run-time also incurs a slight performance penalty for each using that method each time the form is loaded. If the form is simple enough, the hesitation won't be obvious, but changing the .RecordSource DOES require a second .Requery on any form after the one implied by a Form_Load event.

 

[Uncle Gizmo]

It is possible to make a link between child and master through VBA, like this?

Yes, and I did a very nice example when I created the Stephen Covey Time Management Matrix in MS Access.


See this video:-

Subform - Nifty Access - Intro​

Where I give the brief introduction to my MS Access version of the Time Management Matrix. I also give an explanation of the VBA code.

You will see that the Time Management Matrix appears to consist of one main form which houses four subforms. However in reality, the main form has four special controls called Subform/Subreport Controls positioned on it. (I recommend that you think of this Subform/Subreport Control as a window displaying a single form)...

When a form is housed within a Subform/Subreport Control it is referred to as a subform but it isn't a subform, there is no such animal, it's just a normal form. There is no such thing as a "subform". It is the combination of an ordinary form and the Subform/Subreport Control that creates the illusion of a subform.

It's important to understand the symbiotic relationship between these two controls {(Subform/Subreport Control & Form) = (subForm)} because if you don't understand the relationship you can be totally misled by MS Access!

The Time Management Matrix in MS Access consists of only one table storing the tasks. Then there is a main form which holds the four Subform/Subreport Controls. There is only one other form. This form is placed within each of the four Subform/Subreport Controls with VBA code when the main form loads. Each of the "subForms" is giving a different record source. You end up with a main form with what looks like four "subForms" placed on it. But it's 4 "Instances" of the same form.

In answer to your question, if you have a situation where you want to display multiple queries from the same table on a single form then not only possible but it is desirable as you can reduce the number of forms in your database which is always a good thing!

If you would like a copy of the Stephen Covey Time Management Matrix in MS Access then you can download it from Gumroad here:-

Time Management Matrix - Nifty Access

This is a sample database which accompanies my video playlist for demonstrating how to use subforms in MS Access. However it is also a stand-alone time management Matrix, albeit in an early development stage. The full list of sub-form videos can be found on my website here:-...

niftyaccess.gumroad.com

Use the Coupon code:- qnprjep to get a Free Copy...

There are 10 free copies available. Once the free copies are used up, then contact me via the Access World Forums private messaging system and I will send you a personal coupon code.

More Info HERE:- https://www.niftyaccess.com/sub-forms/

Form Code Here:-

Option Compare Database
Option Explicit

Private Sub Form_Load()
DoCmd.Restore
End Sub      'Form_Load

Private Sub Form_Open(Cancel As Integer)
'SELECT MatrixID, MatrixIn, MatrixPri, MatrixTxt, MatrixDateAdd, MatrixDateDue, MatrixComplete
'FROM tblMatrix
'WHERE (((MatrixIn)=1));

Dim strSQL0 As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String

strSQL1 = "SELECT MatrixID, MatrixIn, MatrixPri, MatrixTxt, MatrixDateAdd, MatrixDateDue, MatrixComplete "
strSQL2 = "FROM tblMatrix "
strSQL3 = "WHERE (((MatrixIn)="
'1
strSQL4 = "))"

Dim intSubUI As Integer
Dim intSubNUI As Integer
Dim intSubUNI As Integer
Dim intSubNUNI As Integer

intSubUI = 1    '(UI) - Urgent Important
intSubNUI = 2   '(NUI) - Not Urgent Important
intSubUNI = 3   '(UNI) - Urgent Not Important
intSubNUNI = 4  '(NUNI) - Not Urgent Not Important

    With Me.subUrgentUrgent
        .SourceObject = "frmI"
        .Form.RecordSource = strSQL1 & strSQL2 & strSQL3 & intSubUI & strSQL4
        .Form.txtMatrixIn.DefaultValue = intSubUI
    End With
 
    With Me.subNotUrgentImportant
        .SourceObject = "frmI"
        .Form.RecordSource = strSQL1 & strSQL2 & strSQL3 & intSubNUI & strSQL4
        .Form.txtMatrixIn.DefaultValue = intSubNUI
    End With

    With Me.subUrgentNotImportant
        .SourceObject = "frmI"
        .Form.RecordSource = strSQL1 & strSQL2 & strSQL3 & intSubUNI & strSQL4
        .Form.txtMatrixIn.DefaultValue = intSubUNI
    End With
 
 
    With Me.subNotUrgentNotImportant
        .SourceObject = "frmI"
        .Form.RecordSource = strSQL1 & strSQL2 & strSQL3 & intSubNUNI & strSQL4
        .Form.txtMatrixIn.DefaultValue = intSubNUNI
    End With
 
        Me.Caption = "                                    " & conAppName
     
End Sub      ' Form_Open

 

[Uncle Gizmo]

It is possible to make a link between child and master through VBA, like this

.RecordSource = sqlOffertesWijzigenBasis
.subfrmOfferteProductenWijzigen.Form.RecordSource = sqlProductOffertesWijzigen
.subfrmOfferteProductenWijzigen.LinkChildFields = "OFID"
.subfrmOfferteProductenWijzigen.LinkMasterFields = "OFID"

I just remembered something that happened a while back. I had to set the child master links with VBA because the manual method of adding the child master link to the subform would not work. See YouTube video for more details:-

Subform/Subreport Control Child/Master Links Issue - Nifty Access​