Get value of Link Master Field

[y_wally]

Hi,

I've got a main form with 2 times the same subform on it.
Only the Link Master Fields is different for these subforms.
I have an add button on this subform, but I need the ID that is given in the Link Master Field to store my data.
How can I achieve this?

 

[arnelgp]

it is automatically saved when you have link.

 

[Cronk]

The link fields are properties of the sub form object ie
me.sfYourSubform.linkChildFields
me.sfYourSubform.linkMasterFields

where sfYourSubform is the object name of the sub form in the parent form

 

[y_wally]

@Cronk, this code works when you are on the mainform.
But when I'm on the subform, how can I know these values?

Or how can I get the name of my subform like it's named on the mainform?

 

[arnelgp]

to get the LinkMasterFields (the control name) from subform:

Forms(Me.Parent.Name)(Me.Name).LinkMasterFields

to get the LinkMasterFields' Value:

Me.Parent.Controls(Forms(Me.Parent.Name)(Me.Name).LinkMasterFields).Value

 

[isladogs]

For more info on referencing subform controls, see these links

http://allenbrowne.com/casu-04.html

http://access.mvps.org/access/forms/frm0031.htm

 

[y_wally]

@arnelgp, something like your code I'm looking for.
But the problem is that the I have changed the name of the subform on the mainform. Because I have this subform twice on the mainform.
Therefore I need to know what is the name of the subform.
Because with your mentioned code, me.name gives the name of the subform as I have created it, but not the name as I have named it on the mainform.
Thanks for your help.

 

[arnelgp]

you should'nt do that.
it is referencing the same Subform, whether you rename it.
you should instead create a copy (copy paste) the subform in Navigation Pane.
rename the copy and used that copy as your main form.
therefore you willl have unique names of subform.

 

[Uncle Gizmo]

I have changed the name of the subform on the mainform.

I don't think so. I think it's more likely you have changed the name of the subform/subreport control (which I referred to as a subform window) this is a hidden control which actually houses the form you refer to as a subform.

Here is the first video of a playlist I intend to create on subforms. Might provide you with a bit more information on how they work...

Sub-Forms

 

[Uncle Gizmo]

create a copy (copy paste) the subform in Navigation Pane.

I'm not sure that is the solution because I don't think you have to worry about the name of the subform. You can find the subform by referring to it as the form enclosed in a particular subform/subreport control.

Regarding multiple copies of the same form, I do understand this method of building databases, that is creating duplicates of the same form with minor alterations to serve a slightly different functions. And it is probably an acceptable process when you are designing, however you should endeavour to use the same form many times. Instead of creating multiple duplicates with slightly different functionality, what you do is add the different functionality with VBA code. This is demonstrated in the video on Subforms. The video Here:- Sub-Forms shows a main form that appears to have 4 subforms on it, however it only has one subform modified with VBA code to perform four slightly different functions. The demonstration is of a Time Management Matrix.

 

[y_wally]

you should'nt do that.
it is referencing the same Subform, whether you rename it.
you should instead create a copy (copy paste) the subform in Navigation Pane.
rename the copy and used that copy as your main form.
therefore you willl have unique names of subform.

But when I change something on the subform, I have to change it in every copy. Otherwise I only have to change it once.

 

[Uncle Gizmo]

But when I change something on the subform, I have to change it in every copy. Otherwise I only have to change it once.

I couldn't have said it better myself! Oh! I think I did!

 

[SomeSmurf]

Hello everyone!
Bit old thread but not sure the question was fully answered. And what if the question is same as original post, but the form is used with multiple instances of the main form too? Is using the forms collection still best option in that case or is there some other way?

Seems weird that there is no (accessible read only) run time property in form that contains the value of the master link field so it can easily be accessed in any subform.

/SomeSmurf

 

[SomeSmurf]

Me.Parent.Form.CurrentRecord

so maybe not that tricky

 

[Uncle Gizmo]

but the form is used with multiple instances of the main form too?

I don't understand?

Sent from my Pixel 3a using Tapatalk

 

[Uncle Gizmo]

property in form that contains the value of the master link field so it can easily be accessed in any subform.


/SomeSmurf

The linking information is contained within the subform/subreport control.

This subform/subreport control is an intermediary control between your main form and your sub-form.

That means that every subform you add to your main form also adds an intermediary control.

If you wanted to access the linking information of each of these subform/subreport controls then you could do it with VBA code. Have the VBA loop through the collection of subform/subreport controls and do whatever you want to be done. In other words, you could duplicate your idea of having a master link Field property.

However if you explain what you want to do, then there may well be a much simpler way of doing it.

Sent from my Pixel 3a using Tapatalk

 

[SomeSmurf]

What y wally wanted to find in his orginal post was the relevant field to use (master link field) for each of his subforms. Purpose was (unless mistaken) to be able to code an Add/insert button in the subform.

But if he also had multiple instances of the Main form (so 2 or more of the same form, with same form name in the Forms collection), each with 2 subform instances in them then he need to keep track of not only the field to use, but also which current record the corresponding main form is on for each subform. Otherwise if he is on ID 5 in Main form A and on ID 10 in Main form B his add/insert button will not know what to insert in which form even if he sorted out what which linkfield the subform used for his insert button. At least I thougt that would be a problem.

But if he knows the ID of the mainform, he can query for that entry, and like you wrote in earlier posts, VBA can be used to use same subform instead of copies of them.
In main forms open event he can set value in the subforms like a "mode" to tell the subform how it is used. For example 1 means it is subform A, and 2 means it is subform B of the main form.

Then simply using Me.Parent.Form.CurrentRecord gives the last thing needed.

Now he can build whatever code he wants for the Add/Insert button mentioned in the original post because he knows how the subform is used (ie which columns are used in link) and he knows the ID of the parent form so he can
find the values needed for an INSERT statement.

Reason I got stuck on this is that so many answers I found online for similar questions refers to the Forms collection, but that is not a good option (as far as I can tell) if you have multiple instances of the same form. They will have same form name in the Forms collection.

But maybe I misunderstood the problem. I am not very good at access, I try to solve most of my problems in VBA which I have more experience in. Not sure if this was clearer. More detailed at least

 

[Deleted member 147267]

They will have same form name in the Forms collection.

If this is even possible I'll be totally amazed. Is that conjecture or you have actually created 2 forms with exactly the same name in the same database??

 

[The_Doc_Man]

They will have same form name in the Forms collection.

No. The second form will be "FormName (2)" and the third one will be "FormName (3)" based on the way Access handles multiple instantiation conflicts.

 

[SomeSmurf]

I'm confused. We must be talking about different things??
Maybe you mean 2 forms (designtime) with same name? That for sure is not possible.

I am talking about 2 object instances (runtime) of the same 1 form (designtime).
And the Forms Collection, the collection that keeps track of (only) Currently Open forms.

Forms(0).Name can therefore be equal to Forms(1).Name (for example) if they are both based on the same (designtime) form.

Proof: create a form called frmTest in a database, the form can be empty. Then run this code.

Public Sub Test()
Dim objA As Form, objB As Form, i As Long
i = Forms.Count
Set objA = New Form_frmTest
Set objB = New Form_frmTest
Debug.Print Forms(i).Name
Debug.Print Forms(i + 1).Name
Debug.Print Forms(i).Name = Forms(i + 1).Name
Set objA = Nothing
Set objB = Nothing
End Sub

result in vba immediate window is
frmTest
frmTest
True

The forms are immediately destroyed again due to the way I wrote this but the point is 2 or more _object_instances_ of the same form class can (will always?) have the same name in Forms collection. So I am not comfortable using that.

But I do not have to either, previous answers are good enough for me.