REFERENCING Form's instances controls (2 Viewers)

[LjushaMisha]

I have a continuous form named "frmRezultati" (i.e. frmResults) with one of the controls named "SesId". DblClick of this control
opens INSTANCE of "frmRezultati". Idea is to change recordset of new "instance" form, dependig on value in the control which was doubleclicked.
I don't have a problem with declaring recodset and so on.

My problem is REFERENCING controls in form's "frmRezultati" instance.
What have I done so far:
a. In standalone module I have declared NEW COLLECTION "named" KLONI (i.e. Clones) ---> Dim Kloni As New Collection
b. I made a function for "opening" instances (With great help of Mr. Brown instructions (Code on the end of thread)
Opening instances with needed Captions works great. I'm able to openup to 99 instances with "no effort at all". But I don't need them so many.
c. I made function "ShraniSesId" to check value in doubleclicked control SesID of form's instance
ShraniSesId = Screen.ActiveForm!SesId

Almost everithing work's well. Only last two lines (Debug.Prints) "give me wrong answer" - as commented in code

HERE IS THE CODE:

Function Klon_frmRezultat_Funkcija_01()

Dim frm As Form
Dim obj As Object
Dim i As Integer
Dim j As Integer


' Function
ShraniSesId
Debug.Print "1. " & Format(ShraniSesId, "000000") 'Result: 1. 000025, correct

' Open NEW instance, make it visible and give it .Caption
Set frm = New Form_frmRezultati
Set obj = Screen.ActiveForm
i = Right(Screen.ActiveForm.Caption, 2) + 1

frm.Visible = True
frm.Caption = "LEVEL " & i & ""

' Append it to collection Kloni.
Kloni.Add Item:=frm, Key:=CStr(frm.Hwnd)
Debug.Print "2. Instance's index: " & i 'Result: 2. 3, correct
Debug.Print "3. Instance's Caption: " & Kloni(i).Caption 'Result: 3. LEVEL 3, correct
Debug.Print "4. Value !SesID from Funtion : " & ShraniSesId 'Result: 4. 1, WRONG - STRANGE WHY 2nd call RESULT IS WRONG
Debug.Print "5. Value !SesID from Sub: " & Kloni(i)!SesId 'Result: 5. 1, WRONG
'In fact 1 is first value of SesId on "original" form
'In this dblclick I need result 000025 to continue (as firs result of the function

Set frm = Nothing
End Function

Thanks in advance for all helpfull suggestions. I think/know I could make it with Screen.ActiveForm!SesID. But I would have problems in
future for example SET new recordset for which form or better which instance of a form???, requery which instance, and so on and so on.

 

[CJ_London]

Please put your code in the code tags to make it readable - copy your code for source, then click on the <\> button and paste into the new window

 

[LjushaMisha]

which language should I choose? SQL is not what you'll like I think.

 

[LjushaMisha]

Function Klon_frmRezultat_Funkcija_01()

    Dim frm As Form
    Dim obj As Object
    Dim i As Integer
    Dim j As Integer
    
  
    '   Function
    ShraniSesId
    Debug.Print "1. " & Format(ShraniSesId, "000000")                           'Result 000025, correct
    
    '   Open NEW instance, make it visible and give it .Caption
    Set frm = New Form_frmRezultati
    Set obj = Screen.ActiveForm
    i = Right(Screen.ActiveForm.Caption, 2) + 1
    
    frm.Visible = True
    frm.Caption = "LEVEL " & i & ""

    '   Append it to collection Kloni.
    Kloni.Add Item:=frm, Key:=CStr(frm.Hwnd)
    Debug.Print "2. Instance's index: " & i                          'Result 2, correct
    Debug.Print "3. Instance's Caption: " & Kloni(i).Caption         'Result LEVEL 2, correct
    Debug.Print "4. Value !SesID from Funtion : " & ShraniSesId      'Result 1, WRONG
    Debug.Print "5. Value !SesID from Sub: " & Kloni(i)!SesId        'Result 1, WRONG
                                                                     'In fact 1 is first value of SesId on "original" form
                                                                     'In this dblclick I need result 000025 to continue
    

    Set frm = Nothing
End Function

 

[LjushaMisha]

Hope it is better. I see colours are NOK

 

[June7]

I presume "Mr. Brown" is Allen Browne. Were you able to download and open sample db?

The Hwnd of form is saved as Key in collection. If you know the Hwnd of form instance, can reference form object in collection like:
Debug.Print KLONI.Item("1836326")!SesID

The form knows its own Hwnd:
Debug.Print KLONI.Item(CStr(Me.Hwnd))!SesID

 

[LjushaMisha]

Yes. It is about Mr.Browne.Sorry. No, sample db couldn't be opened.I only copied and paste some code and make it work for me.

REGARDING CODE:
Above code doesn't have any sense. My final goal is to write sql string with where condition like WHERE SesId = " & Kloni(i)!SesId
Like drilling deeper and deeper in structure of some product for example.
And after that, if isnull rs.recordcount than open form to enter new product part and after subbmit new part requery (i) instance. If rs.recordcount > 0 then go deeper in product structure on another dblclick
I think if I can somehow figure (with some help) how to get correct Id from Kloni(i)!SesId other steps shouldn't be problem.
I hope explanation is ok?
BR

 

[June7]

I edited my previous post while you were reading and posting. Review again.

Not sure multi-instances is best approach. Have you viewed Allen's Search Form example? This (maybe along with cascading comboboxes) might be more sensible.

 

[LjushaMisha]

No, I haven't seen it. I have problems oppening his "solutions"
Hwnd I already tried. But I thought it is integer. And I referenced it as such. In your example you threat it as string (between quotes).
And you also typed solution of my even bigger problem: how to get Hwnd from the form. Your last part of the post.

I started with instances because I wanted to open always the same form instead of making several different forms, at the end maybe not making them enough. And I didn't see any better solution. Same form, different recordsources, depending on SesId dblclicked value.
If I understood instances, everyone has index. It can be string (Kloni("Level 1")!SesId) given by code or integer (Kloni(1)!SesId) by default.
But it is NOT possible referencing them as I thought?
I put reference with string in sql query -->Error
I put reference as hardcoded integer in sql query i think it worked (Kloni(2)!SesId)
I put reference as integer "softcoded" Kloni(i)!SesId --> not working, see code at the bigining.
So you think only Hwnd saves me?

 

[June7]

Seems that Collection saves Key as string, regardless of what type original value was.

I have never used Screen referencing. It's unreliable. Explicit referencing is best. Hwnd is just as available as the form Caption.
I tried your code setting Caption but every form opened after the primary has same caption. It's building Caption form the primary not new instance that I clicked on.

 

[LjushaMisha]

Dear June7,

sorry not answering you in time but I added my last post around 1:00 AM. Then I went to sleep.
But meanwhile I did a database named "Instances_SampleDb.accdb", attached to my reply.
When you open it Pls read "AAAtblReadme".
Instructions for usage:
1. OPEN FORM "Frm_KOSOVNICA_Find"
2. Type for example string OMA in "txtFind" control (it already has focus). On OnChange event
3. Form "frmRezultati" opens
4. DblClick on "SesId" control on FIRST record on each opened form's instance
(why FIRST ---> product "000001 - OMARA 3 D" has most "elevate product tree", but you can also DblClick on other records)

Of course this is a simple database, now I need to find out a way to EDIT, NEW, DELETE record, BUT I think WE have resolved main problem.
Yes, I have used "Screen.ActiveForm" object but I couldn't find other way. Keyword Me. doesn't work.

Hope you found it interesting. Waiting for your comment.
BR

 

[mike60smart]

Dear June7,

sorry not answering you in time but I added my last post around 1:00 AM. Then I went to sleep.
But meanwhile I did a database named "Instances_SampleDb.accdb", attached to my reply.
When you open it Pls read "AAAtblReadme".
Instructions for usage:
1. OPEN FORM "Frm_KOSOVNICA_Find"
2. Type for example string OMA in "txtFind" control (it already has focus). On OnChange event
3. Form "frmRezultati" opens
4. DblClick on "SesId" control on FIRST record on each opened form's instance
(why FIRST ---> product "000001 - OMARA 3 D" has most "elevate product tree", but you can also DblClick on other records)

Of course this is a simple database, now I need to find out a way to EDIT, NEW, DELETE record, BUT I think WE have resolved main problem.
Yes, I have used "Screen.ActiveForm" object but I couldn't find other way. Keyword Me. doesn't work.

Hope you found it interesting. Waiting for your comment.
BR

Just out of interest, why are you not using Bound Main Form to Subform to display Product Detail in the Main Form
with related Product Details in a Subform?

 

[LjushaMisha]

Just out of interest, why are you not using Bound Main Form to Subform to display Product Detail in the Main Form
with related Product Details in a Subform?

Because I don't know how to do it

 

[Gasman]

Because I don't know how to do it

Well, it will not come to you.
You need to go looking on how to do it.

https://www.youtube.com/results?search_query=main+form+subform+access

Yet, you want to run multiple instances of forms? To my mind, that is way much harder to do.

 

[LjushaMisha]

I know how to make Main form, it's subform, how to reference subform controls but I DON'T KNOW HOW TO MAKE 5, maybe 6 or maybe even 10 subforms (depending how many levels are needed for one product) in one main form. Or maybe it would be possible, as at the end each subform is only ONE control, it can be visible or not, with it's own coded recordset,... . But I thought I must design 10 different subforms and place them in main form. And I was looking for easier way, which, at the end, as you say is harder than many subforms.

 

[Gasman]

You could use Tabs and load the relevant subform, when that tab is selected.
That would be my first way as I have never used multiple instances. Never needed to, else I would copy AB's example, which I have looked at, but a long time ago.

Re subform controls from mainform/or other way around.

Forms: Refer to Form and Subform properties and controls

 

[mike60smart]

I know how to make Main form, it's subform, how to reference subform controls but I DON'T KNOW HOW TO MAKE 5, maybe 6 or maybe even 10 subforms (depending how many levels are needed for one product) in one main form. Or maybe it would be possible, as at the end each subform is only ONE control, it can be visible or not, with it's own coded recordset,... . But I thought I must design 10 different subforms and place them in main form. And I was looking for easier way, which, at the end, as you say is harder than many subforms.

6 or even 10 subforms would be a lot of setting up.

 

[mike60smart]

I know how to make Main form, it's subform, how to reference subform controls but I DON'T KNOW HOW TO MAKE 5, maybe 6 or maybe even 10 subforms (depending how many levels are needed for one product) in one main form. Or maybe it would be possible, as at the end each subform is only ONE control, it can be visible or not, with it's own coded recordset,... . But I thought I must design 10 different subforms and place them in main form. And I was looking for easier way, which, at the end, as you say is harder than many subforms.

Attached screenshot is 3 Levels of Subform

The 2 coloured controls above the Label for Level 2 Item Details are Form references to link Subform to Subform as suggested by Welshgasman

 

[LarryE]

I have a continuous form named "frmRezultati" (i.e. frmResults) with one of the controls named "SesId". DblClick of this control
opens INSTANCE of "frmRezultati". Idea is to change recordset of new "instance" form, dependig on value in the control which was doubleclicked.
I don't have a problem with declaring recodset and so on.

My problem is REFERENCING controls in form's "frmRezultati" instance.
What have I done so far:
a. In standalone module I have declared NEW COLLECTION "named" KLONI (i.e. Clones) ---> Dim Kloni As New Collection
b. I made a function for "opening" instances (With great help of Mr. Brown instructions (Code on the end of thread)
Opening instances with needed Captions works great. I'm able to openup to 99 instances with "no effort at all". But I don't need them so many.
c. I made function "ShraniSesId" to check value in doubleclicked control SesID of form's instance
ShraniSesId = Screen.ActiveForm!SesId

Almost everithing work's well. Only last two lines (Debug.Prints) "give me wrong answer" - as commented in code

HERE IS THE CODE:

Function Klon_frmRezultat_Funkcija_01()

Dim frm As Form
Dim obj As Object
Dim i As Integer
Dim j As Integer


' Function
ShraniSesId
Debug.Print "1. " & Format(ShraniSesId, "000000") 'Result: 1. 000025, correct

' Open NEW instance, make it visible and give it .Caption
Set frm = New Form_frmRezultati
Set obj = Screen.ActiveForm
i = Right(Screen.ActiveForm.Caption, 2) + 1

frm.Visible = True
frm.Caption = "LEVEL " & i & ""

' Append it to collection Kloni.
Kloni.Add Item:=frm, Key:=CStr(frm.Hwnd)
Debug.Print "2. Instance's index: " & i 'Result: 2. 3, correct
Debug.Print "3. Instance's Caption: " & Kloni(i).Caption 'Result: 3. LEVEL 3, correct
Debug.Print "4. Value !SesID from Funtion : " & ShraniSesId 'Result: 4. 1, WRONG - STRANGE WHY 2nd call RESULT IS WRONG
Debug.Print "5. Value !SesID from Sub: " & Kloni(i)!SesId 'Result: 5. 1, WRONG
'In fact 1 is first value of SesId on "original" form
'In this dblclick I need result 000025 to continue (as firs result of the function

Set frm = Nothing
End Function

Thanks in advance for all helpfull suggestions. I think/know I could make it with Screen.ActiveForm!SesID. But I would have problems in
future for example SET new recordset for which form or better which instance of a form???, requery which instance, and so on and so on.

Here is an explanation of what the OP is attempting to do with ACCESS. It appears they are trying to track garment parts to be used to make garments, although I do not speak the language used in construction the tables and fields:

The OP has no relationships in the database between the two tables, so basically, they have two tables they are using like spreadsheets. The OP believes that some code they got off the internet will somehow make this work. It will not.

@LjushaMisha:
Given the description in the Readme table, you need to construct this project as a relational database which is what ACCESS was designed to be, based upon the requirements outlined in your Readme table included in the project. No code will help you do this.

 

[mike60smart]

Here is an explanation of what the OP is attempting to do with ACCESS. It appears they are trying to track garment parts to be used to make garments, although I do not speak the language used in construction the tables and fields:
View attachment 120579
The OP has no relationships in the database between the two tables, so basically, they have two tables they are using like spreadsheets. The OP believes that some code they got off the internet will somehow make this work. It will not.

@LjushaMisha:
Given the description in the Readme table, you need to construct this project as a relational database which is what ACCESS was designed to be, based upon the requirements outlined in your Readme table included in the project. No code will help you do this.

Hi Larry
It is not Garments but Wardrobe Furniture that is being covered.