Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-14-2019, 02:22 PM   #16
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,227
Thanks: 513
Thanked 917 Times in 869 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Get value of Link Master Field

Quote:
Originally Posted by SomeSmurf View Post

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

__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 07-14-2019, 04:12 PM   #17
SomeSmurf
Newly Registered User
 
Join Date: Jul 2019
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
SomeSmurf is on a distinguished road
Re: Get value of Link Master Field

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
SomeSmurf is offline   Reply With Quote
Old 07-14-2019, 05:23 PM   #18
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 864
Thanks: 10
Thanked 181 Times in 171 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Get value of Link Master Field

Quote:
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??

__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is online now   Reply With Quote
Old 07-14-2019, 09:35 PM   #19
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,241
Thanks: 86
Thanked 1,623 Times in 1,506 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Get value of Link Master Field

Quote:
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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 07-15-2019, 05:59 AM   #20
SomeSmurf
Newly Registered User
 
Join Date: Jul 2019
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
SomeSmurf is on a distinguished road
Re: Get value of Link Master Field

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.
SomeSmurf is offline   Reply With Quote
Old 07-15-2019, 07:23 AM   #21
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 864
Thanks: 10
Thanked 181 Times in 171 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Get value of Link Master Field

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 by Micron; 07-15-2019 at 07:36 AM.
Micron is online now   Reply With Quote
Old 07-15-2019, 10:31 AM   #22
SomeSmurf
Newly Registered User
 
Join Date: Jul 2019
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
SomeSmurf is on a distinguished road
Re: Get value of Link Master Field

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

SomeSmurf is offline   Reply With Quote
Old 07-15-2019, 12:14 PM   #23
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,227
Thanks: 513
Thanked 917 Times in 869 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Get value of Link Master Field

Quote:
Originally Posted by SomeSmurf View Post
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-loa...m-load-events/


Sent from my Pixel 3a using Tapatalk
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 07-15-2019, 12:28 PM   #24
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 864
Thanks: 10
Thanked 181 Times in 171 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Get value of Link Master Field

Quote:
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?
Micron is online now   Reply With Quote
Old 07-15-2019, 01:31 PM   #25
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,227
Thanks: 513
Thanked 917 Times in 869 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Get value of Link Master Field

Quote:
Originally Posted by Micron View Post
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
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 07-15-2019, 05:51 PM   #26
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 864
Thanks: 10
Thanked 181 Times in 171 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Get value of Link Master Field

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
Micron is online now   Reply With Quote
Old 07-15-2019, 07:24 PM   #27
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,744
Thanks: 28
Thanked 522 Times in 495 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Get value of Link Master Field

Quote:
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.

Quote:
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
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
SomeSmurf (07-15-2019)
Old 07-15-2019, 10:31 PM   #28
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,227
Thanks: 513
Thanked 917 Times in 869 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Get value of Link Master Field

Quote:
Originally Posted by Micron View Post
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
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 07-15-2019, 10:44 PM   #29
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,227
Thanks: 513
Thanked 917 Times in 869 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Get value of Link Master Field

Quote:
Originally Posted by Micron View Post
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 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
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)

Last edited by Uncle Gizmo; 07-15-2019 at 10:55 PM.
Uncle Gizmo is offline   Reply With Quote
Old 07-16-2019, 12:53 AM   #30
SomeSmurf
Newly Registered User
 
Join Date: Jul 2019
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
SomeSmurf is on a distinguished road
Re: Get value of Link Master Field

Quote:
Originally Posted by Uncle Gizmo View Post
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 Uncle Gizmo; 07-16-2019 at 02:52 PM. Reason: Fixed Quote... Added Code Tags
SomeSmurf is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
how to link master field to textbox on main form evoingram Modules & VBA 1 11-21-2016 04:50 PM
Revising Data In Link child & Link Master Field duffy Forms 2 04-19-2009 05:35 PM
Link master field, on another subreport Gkirkup Reports 1 04-03-2009 01:15 AM
Setting Link Master Field in VB mnbuckland Modules & VBA 2 12-08-2008 10:00 PM
Making the Subform Master Field Link a Control ^Nightwing^ Forms 0 09-21-2004 02:46 AM




All times are GMT -8. The time now is 07:38 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World