Get value of Link Master Field (1 Viewer)

y_wally

New member
Local time
Today, 14:51
Joined
Dec 11, 2017
Messages
4
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

..forever waiting... waiting for jellybean!
Local time
Today, 21:51
Joined
May 7, 2009
Messages
19,169
it is automatically saved when you have link.
 

Cronk

Registered User.
Local time
Tomorrow, 00:51
Joined
Jul 4, 2013
Messages
2,770
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

New member
Local time
Today, 14:51
Joined
Dec 11, 2017
Messages
4
@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

..forever waiting... waiting for jellybean!
Local time
Today, 21:51
Joined
May 7, 2009
Messages
19,169
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
 

y_wally

New member
Local time
Today, 14:51
Joined
Dec 11, 2017
Messages
4
@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

..forever waiting... waiting for jellybean!
Local time
Today, 21:51
Joined
May 7, 2009
Messages
19,169
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

Nifty Access Guy
Staff member
Local time
Today, 13:51
Joined
Jul 9, 2003
Messages
16,244
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

Nifty Access Guy
Staff member
Local time
Today, 13:51
Joined
Jul 9, 2003
Messages
16,244
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

New member
Local time
Today, 14:51
Joined
Dec 11, 2017
Messages
4
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

Nifty Access Guy
Staff member
Local time
Today, 13:51
Joined
Jul 9, 2003
Messages
16,244
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

New member
Local time
Today, 14:51
Joined
Jul 14, 2019
Messages
6
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
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:51
Joined
Jul 9, 2003
Messages
16,244
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

New member
Local time
Today, 14:51
Joined
Jul 14, 2019
Messages
6
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 :)
 

Micron

AWF VIP
Local time
Today, 09:51
Joined
Oct 20, 2018
Messages
3,476
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

Immoderate Moderator
Staff member
Local time
Today, 08:51
Joined
Feb 28, 2001
Messages
26,999
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

New member
Local time
Today, 14:51
Joined
Jul 14, 2019
Messages
6
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.
 

Users who are viewing this thread

Top Bottom