Get value of Link Master Field (1 Viewer)

Micron

AWF VIP
Local time
Today, 07:41
Joined
Oct 20, 2018
Messages
3,476
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.
 
Last edited:

SomeSmurf

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

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:41
Joined
Jul 9, 2003
Messages
16,244
Instead of using Form.Load you need to use Form.Open event.

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

I usually create a public function in the Form code module to replace the Load & Open events, with the name fSetUp().... then it's just an extra line of code to call this function, which then does exactly what you want it to do without relying on the Open and Load events...

See my blog here for more info:-

https://www.niftyaccess.com/form-load-activate-open-issue-form-load-events/


Sent from my Pixel 3a using Tapatalk
 

Micron

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

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:41
Joined
Jul 9, 2003
Messages
16,244
Wouldn't the right approach be to learn how they work rather than create work-arounds?

It's the way they work that's the problem. I've explained it in the videos linked to on my website.

To summarize the problem. You have a custom property in your forms code module, you can set the value of that custom property when you open the form. Now you would expect the forms Load event or Open event to take the value from that custom property and utilize it somehow in your application. But it doesn't happen! the Load event, the on Open event and I'm pretty sure I tried all of the other events in my testing, are not able to access the custom property. It's like the custom property is set after the Load event, on Open event and all of the other events have run. Hence the necessity of having to add your own function to do what ever you need with the custom property.

Sent from my Pixel 3a using Tapatalk
 

Micron

AWF VIP
Local time
Today, 07:41
Joined
Oct 20, 2018
Messages
3,476
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):
Code:
DoCmd.OpenForm "frm2"
Forms!frm2.mTest = "TEST"
With Forms!frm2
    .txtShowResult = .prpTest
End With
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:41
Joined
May 21, 2018
Messages
8,463
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.
Code:
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
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:41
Joined
Jul 9, 2003
Messages
16,244
other methods such as OpenArgs

OpenArgs can be very useful, but I see people abuse it (to my way of thinking) by trying to pass through loads of variables in a string!

It was as an alternative to OpenArgs that I adopted custom properties because you can separate out your variables. This results in clearer cleaner code.

Another method which I don't see used so much lately is to have a hidden control on your form and pass your variable through to that. I call it the "Yellow Peril" method, because traditionally you colour these controls yellow so that they are clearly visible in design view. They stick out like sore thumbs!

Another method which would avoid the necessity of having custom properties, would be to call a function similar to my public fSetUp() function, but send the variables through as parameters of the function. But again this just seems less readable, less clear to me.

Sent from my Pixel 3a using Tapatalk
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:41
Joined
Jul 9, 2003
Messages
16,244
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 reason it is unexpected to my mind, is because when you use OpenArgs the value passed through can be used. So why not the values of Custom Properties?

I suspect it's deliberate , (or possibly a mistake!) In one way it makes a lot of sense, if you are using custom properties, then you are taking control of the situation, and adding your own function to apply the custom properties is just another step up in this control you are exercising. In other words, you're not relying on the built-in functionality of the form ...

Sent from my Pixel 3a using Tapatalk
 
Last edited:

SomeSmurf

New member
Local time
Today, 12:41
Joined
Jul 14, 2019
Messages
6
The reason it is unexpected to my mind, is because when you use OpenArgs the value passed through can be used. So why not the values of Custom Properties?

Your custom property get is in fact used in the events, but the value of the property is blank at the time when event is triggered so the value of the textbox is blank too. If your property get had returned prpTest= "Test" instead of prpTest = mTest you would have had results in the textbox.

Example - new project with only a Form1 in it and a module.

Code:
'form code
Private mTest As String

Property Let prpTest(strTest As String)
    If mTest <> "" Then mTest = mTest & vbCrLf
    mTest = mTest & strTest
    Debug.Print "prpTest:" & vbCrLf & mTest
End Property

Property Get prpTest() As String
    prpTest = mTest
End Property

Private Sub Form_Activate()
    prpTest = "Activate Event"
End Sub

Private Sub Form_Load()
    prpTest = "Load Event"
End Sub

Private Sub Form_Open(Cancel As Integer)
    prpTest = "Open Event"
End Sub

'module code (runs the test)
Public Sub Test()
    Dim objForm1 As New Form_Form1 '--> here the events are triggered
    objForm1.prpTest = "Manually set property value"
    Set objForm1 = Nothing
End Sub

Results in immediate window show why the video example did not work.

Maybe you already knew this and only think it is improper behavior for the form events, but I'm pretty sure this is the intended behavior of the events though.
 
Last edited by a moderator:

Micron

AWF VIP
Local time
Today, 07:41
Joined
Oct 20, 2018
Messages
3,476
So why not the values of Custom Properties?
I stated why not (The property value isn't assigned until execution returns to form1...) which simply means your attempt is late, but I guess SomeSmurf stated it more clearly. However, OpenArgs IS a property of a form. Its value is passed to the opening form without having to write a custom property for the same purpose.
I suspect it's deliberate , (or possibly a mistake!) In one way it makes a lot of sense, if you are using custom properties m, then you are taking control of the situation, and adding your own function to apply the custom properties is just another step up in this control you are exercising. In other words, you're not relying on the built-in functionality of the form ...
Sent from my Pixel 3a using Tapatalk
Sorry, I don't buy into that at all, or your prior statements about "abusing" OpenArgs or it being less clear or somehow not being able to separate the values. We just have different opinions on the matter, I guess.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:41
Joined
Jul 9, 2003
Messages
16,244
your prior statements about "abusing" OpenArgs or it being less clear or somehow not being able to separate the values.

I stand by that. I never did like trying to cram loads of variables through in an OpenArgs string, it's just not right. A very clever workaround though ...

Sent from my Pixel 3a using Tapatalk
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:41
Joined
Jul 9, 2003
Messages
16,244
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):
Code:
DoCmd.OpenForm "frm2"
Forms!frm2.mTest = "TEST"
With Forms!frm2
    .txtShowResult = .prpTest
End With


I'm not happy with the idea of making the custom property variable public. In any event it's not necessary because just a slight change to your example:-

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


And it works fine...
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:41
Joined
May 21, 2018
Messages
8,463
OpenArgs and custom properties is an apples and oranges discussion. OpenArgs is a workaround for a limitation in Access that custom properties cannot address. The only time OpenArgs is needed (and required) is if you open a form in ACDIALOG. Because code execution stops in the calling form there is no way to pass anything to the called form. OpenArgs works like a parameterized constructor where you can pass in a value at instantiation. If you do not open the form ACDIALOG then custom properties or other solutions are available. I agree, if you do not open ACDIALOG then overusing OpenArgs is not a good approach.
Working in something more modern like VB.NET is much simpler because you can set properties of the form before opening the form. So for example you can set the value of a textbox before even opening the form in a dialog mode. The purpose ofOpenArgs is a workaround for the limitation in Access or not being able to set these properties before opening in a modal/dialog mode where code execution is given to the called form.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 07:41
Joined
Apr 27, 2015
Messages
6,280
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
This bit of information was a God-send to me today. Arnel, you are da MAN, man...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:41
Joined
Feb 19, 2002
Messages
42,970
Wow. I'm glad someone found this thread useful. I was getting nauseous reading it. It seems that the purpose of the master/child links got lost in the shuffle.

When you define the master/child links for a main form/subform, Access automagically populates the FK on the subform with the value of the PK on the main form. The point is that this property controls the relationship between the two different sets of data. While it makes perfect sense to have multiple subforms related to the same main form, it makes no sense at all to have the Master field be different for the two subforms. A table has one primary key. Therefore, the Master for ALL subforms on the main form should always be the PK of the main form. No other field would be unique (unless you have two unique indexes - certainly possible if one of them is a field from a different application but you would NEVER use it as a FK in this app) and if the FK doesn't point to a unique PK, there will potentially be more than a single parent record and that would be quite confusing.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 07:41
Joined
Apr 27, 2015
Messages
6,280
I was getting nauseous reading it.
:ROFLMAO: :ROFLMAO:

I didn't have a clue about what you were on about - a google search led me to Arnel's post and I stopped there once I tested out his code. Of course it worked, I cant think of a time when it (Arnel's code) didn't!

So when you posted what you did, my first thought was "Pat's off her meds today..." But then I read the entire post and see that there were a few rabbit holes...

...BUT...

There are times that rabbit holes like these can really provide some knowledge you just cant find anywhere else. I cant even begin to express what this site has done for me. The short of it is that the members here are almost directly responsible for ensuring I had a paycheck since 2014.

I realize that it took effort on my part too, the "horse to water" bit and all that, but without this site and it's members, there is no telling how my future would have turned out. One thing is for sure, it has made it MUCH easier.

So...if anyone went down THIS rabbit hole, allow me to say one more time: Thank You!
 

Users who are viewing this thread

Top Bottom