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.

 

[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.

 

[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

 

[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.

 

[Deleted member 147267]

I see, except that if I recall correctly, this is about 2 subforms - and open subforms are not part of the Forms collection AFAIK. Thus I don't think your code represents the originally stated situation - that OP has a form with 2 sub forms that are the same form. You are opening 2 instances of a form as if they were main forms.

The whole idea is intriguing and I doubt I would ever have thought to do such a thing, not knowing where it would ever come in useful. Maybe it's better than trying to put search fields in a split form header? I've helped in such posts and found that to be fraught with issues.


EDIT - Also see that between this and 2 other forums, I've kinda missed the fact that you resurrected an old thread. Methinks better to start your own and paste a link to a related thread in most cases if helpful, but that's just an opinion.

 

[SomeSmurf]

@Micron: you are correct, I probably should have opened a new thread. To me it is the same issue really, but I absolutely see your point. Sorry for confusion.

Thanks to all for helpful suggestions!

It may be that I completely misinterpreted Y Wallys question.
Original question by Y Wally was interpreted by me as how to know in which subform instance he is when button is pressed in the Subform (not main form) - Is he in SubformA linked to FieldA of the main form, or is he in SubformB linked to FieldB of the main form? He must know because he is using an Add button (with SQL INSERT statement behind in the VBA event procedure) in the subform.

Uncle Gizmo's suggestion to by VBA go through the subform controls of the main form is the by far best in my opinion, but it still does not (explicitly) answer how to actually know which one of the 2 subform controls (both linked to the same subform, but using Different master link fields) you are in when the button in the subform (either instance A or B) is pushed.

Here is a simple method:

If you are using single instance form:
in the Form.Load event of the main form pass the master link field name to each of the subforms that you have multiple of in your form like this: Me.YourSubFormControlName1.Form.Tag = "TheMasterLinkColumnName1"
Me.YourSubFormControlName2.Form.Tag = "TheMasterLinkColumnName2"
Later you can access this info in VBA in your Subform.AddButton_Click() using Me.Tag and build your SQL INSERT-statement to add new entries from within the subform now that you know the link to the master form.

If you need to check some current values (like current ID for the master link) from the main form you can use Me.Parent.FieldName

If you are using multiple instance form:
Same, but instead of using Form.Load you need to use Form.Open event.

The multiple instance version also works with single instance form.

 

[Deleted member 147267]

One thing I have learned over the years is that the form events don't operate as you would expect.

Honestly, no disrespect intended as I think you have a greater general knowledge of Access than I, but wouldn't the right approach be to learn how they work rather than create work-arounds?

 

[Deleted member 147267]

OK, I watched the vid. I get what you're doing but I would use other methods such as OpenArgs to pass the value to the control, or just make mTest public and code as below. The behaviour that you say is unexpected may be to some at first, but it is exactly what I'd expect and it makes perfect sense.

The property value isn't assigned until execution returns to form1 where you have .prpTest = "TEST". It is too late now because the form2 events that attempt to set the control value are not going to run again after you define what they should have used as a value. To me, this is just how code executes when control passes from one form or procedure to another.

Obviously you're not going to change the way you do things just because I don't see an issue. After all, your fSetup is only 3 lines long.
Here's how if the property declaration was Public (not going to bother with OpenArgs):

DoCmd.OpenForm "frm2"
Forms!frm2.mTest = "TEST"
With Forms!frm2
    .txtShowResult = .prpTest
End With

 

[MajP]

t may be that I completely misinterpreted Y Wallys question.
Original question by Y Wally was interpreted by me as how to know in which subform instance he is when button is pressed in the Subform (not main form) - Is he in SubformA linked to FieldA of the main form, or is he in SubformB linked to FieldB of the main form? He must know because he is using an Add button (with SQL INSERT statement behind in the VBA event procedure) in the subform.

Uncle Gizmo's suggestion to by VBA go through the subform controls of the main form is the by far best in my opinion, but it still does not (explicitly) answer how to actually know which one of the 2 subform controls (both linked to the same subform, but using Different master link fields) you are in when the button in the subform (either instance A or B) is pushed.

You are really over thinking this one. You have two instances of the same form as a subform. Both forms have different linking in their subform control. You put a button on the subform and have a single piece of code that returns the link. Simply determine if the sourceobject is the instance that caused the event.

Private Sub cmdMyLink_Click()
Dim ctrl As Access.Control
For Each ctrl In Me.Parent.Controls
If ctrl.ControlType = acSubform Then
If ctrl.Form Is Me Then
MsgBox ctrl.LinkMasterFields
Exit Sub
End If
End If
Next ctrl
End Sub

There is also a lot of wrong information about form instances. Every form instance of the same form gets the same name. That is why you always manage form instances in a custom collection.

This code my help explain what happens when you add multiple form instances to the form collection, what are the names, and what happens when you try to close them.

Public Sub MultiInstances()
  Dim frmA As Form_Form1
  Dim frmB As Form_Form1
  Dim frm As Access.Form
  Set frmA = New Form_Form1
  Debug.Print Forms.Count
  Set frmB = New Form_Form1
  Debug.Print Forms.Count
  For Each frm In Forms
    Debug.Print frm.Name
  Next frm
  Debug.Print Forms(0).Hwnd
  Debug.Print Forms(1).Hwnd
  DoCmd.Close acForm, "form1"
  Debug.Print Forms.Count
  Debug.Print Forms(0).Hwnd
  DoCmd.Close acForm, "form1"
  Debug.Print Forms.Count
End Sub