Docmd.OpenForm how to refer to a SubSubform record ID (1 Viewer)

masa1 · Dec 15, 2019

Hi,
I have a continuous form with a command button.
I'm trying to write a command to open another form containing the main form, a subform, and a subsubform and access the record represented by
ID_MainForm > ID_SubForm > ID_SubSubForm
Here is my code:

Code:

Private Sub cmd158_Click()
Dim strMainForm As String       
Dim strSubForm As String        
Dim strSubSubForm As String     
Dim strMainCriteria As String  
Dim strSubCriteria As String    
Dim strSubSubCriteria As String     

strMainForm = "MainForm"

strSubForm = "SubForm"

strSubSubForm = "SubSubForm"

strMainCriteria = "[ID_MainForm] =" & Me.[Tekst166] 'Tekst166 = ID_MainForm

strSubCriteria = "[ID_SubForm]= " & Me.[Tekst174] 'Tekst174=ID_SubForm

strSubSubCriteria = "[ID_SubSubForm]=" & Me.[Tekst175] 'Tekst175=ID_SubSubForm

DoCmd.OpenForm strMainForm, , , strMainCriteria

With Forms(strMainForm).Controls(strSubForm).Controls(strSubSubForm)
.Form.filter = strSubSubCriteria 
.Form.FilterOn = True
End With
End Sub

When I click the command button and ID_SubSubForm belongs to the first ID_SubForm, everything is as I would like.
However, when ID_SubSubForm belongs to the next ID_SubForm, the ID_MainForm form opens (correct) and the subform with the FIRST ID_SubForm record (incorrect) and the SubSubForm with the NEW empty record (incorrect).

Could someone help modify the posted code?

isladogs · Dec 15, 2019

Link the main form & subform using the ID fields as the master & child fields.
Do the same for the subform & sub-subform.

You should then be able to reduce your code to:

Code:

Private Sub cmd158_Click()
Dim strMainForm As String       
Dim strMainCriteria As String  

strMainForm = "MainForm"
strMainCriteria = "[ID_MainForm] =" & Me.Tekst166 'Tekst166 = ID_MainForm

DoCmd.OpenForm strMainForm, , , strMainCriteria

End Sub

or just

Code:

Private Sub cmd158_Click()
DoCmd.OpenForm "MainForm", , , "[ID_MainForm] =" & Me.Tekst166
End Sub

masa1 · Dec 15, 2019

"...or just"
it opens as I would only like if first
ID_MainForm > (first) ID_SubForm > (first) ID_SubSubForm
but when I want to open another one ID_SubForm or ID_SubSubForm the same opens (!)
ie. ID_MainForm > (first) ID_SubForm > (first) ID_SubSubForm
The first version has the same effect.

isladogs · Dec 15, 2019

Hi masa1
Did you add the master/child links as I described before?

What I described will open each form to the same ID value
e.g. if main form opened at ID=4, both subforms will show the equivalent ID=4 record.
That is exactly how subforms are intended to be used.

If that's not what you want, you will need to explain again & possibly provide screenshots

masa1 · Dec 15, 2019

I hope that the attached illustrations will solve the problem.

isladogs · Dec 15, 2019

Not really.
It possibly doesn't help that I can't read the language (Polish?)
However, to me it looks like the 3 forms are working correctly with master/child fields set for appropriate fields.
So what's wrong with what you've shown in the screenshots?

masa1 · Dec 15, 2019

You're right, the forms work correctly, but I want to access the record from another form.
And here is the problem.
I want to open THIS form for editing from ANOTHER which I use as a browser!
In the attached illustration, if I click cmd (1) it is OK because ID_SubForm is the first ID created for ID_MainForm (359).
However, when I click cmd (2) it opens SAME, i.e.
ID_mainForm = 359, ID_SubForm = 60 and ID_SubSubForm = 27 and I want = 30

I can't remember quickly WHO is the author of StudentExplorer.accdb (it's possible that this is your work!)
Yes Polish.

isladogs · Dec 15, 2019

The Student Explorer app is indeed my work.
Did you find it here at AWF or at my website http://www.mendipdatasystems.co.uk/multiple-group-filter/4594454290
I hadn't recognised it as my example app as its in Polish. Good to see it can be adapted to suit the needs of another country.

However as you know there were no subforms in my app.
Suggest you upload a copy after anonymising your data and I'll try to have a look at it in the next couple of days.
If its possible to provide an English version, that would be much appreciated.

Cronk · Dec 15, 2019

@masa1
Pass the SubSubForm ID in the openargs of the form. In the main form's OnLoad event, you can then lookup the ParentID and GrandfatherID by opening a recordset or using dLookup() and set the filters of the subforms accordingly

Code:

Me.subform.form!ID=60
me.subform.form!subsubform.form!ID=30

masa1 · Dec 16, 2019

Hello, hi Cronk.
Thank you for joining the topic.
I am sorry that I did not write anything about myself in the introduction to the case.
Well, I'm not very familiar with database design, and actually I don't know much about it!
My interest was caused by a colleague who was doing something and when I looked at it I realized that it was not right and the matter should be treated differently.
From that moment I became interested in database design.
For this reason I have a lot of work, I encounter many problems and I have a lot of satisfaction if I can achieve something!:banghead:

Returning to the subject I would like to ask for more detailed instructions in relation to your idea.
I have devoted a lot of time to overcoming this problem and I will certainly try to use your idea but it will take a while. Maybe additional explanations will shorten my torments.
Best regards.

Cronk · Dec 16, 2019

Given you want

ID_mainForm = 359, ID_SubForm = 60 and ID_SubSubForm = 30

the required ID_SubSubForm = 30

Assuming the table on which the SubSubForm is based, has fields

Code:

ID_SubSubForm   PK
ID_SubForm      FK (to its parent tblSubForm
<other fields>

you can find the ID_SubForm by

Code:

lngSubFormID= dLookup("ID_SubForm","tblSubSubForm",ID_SubSubForm=" & me.openargs)

lngSubFormID should equal 60 in your test case.
Then assuming the table on which SubForm is based is

Code:

ID_SubForm   PK
ID_Form      FK (to its parent tblForm
<other fields>

you can find the ID_Form by

Code:

lngFormID=dLookup("ID_Form","tblSubForm","ID_SubForm=" & lngSubFormID)

So the code in the Load event of the MainForm would be

Code:

Me.filter = "ID_Form=" & lngFormID
me.filteron=true
Me.subform.form.filter=lngSubFormID
Me.subform.form.filteron= true

me.subform.form!subsubform.form!ID=Me.openargs
me.subform.form.filteron=true

And your code on the calling form would be

Code:

docmd.openform "mainForm", , , , , , Me.[Tekst175]

masa1 · Dec 19, 2019

Hi Cronk.
I am sorry that I took a break in activities but it caused current other matters.
Thank you very much for your work.

Now about the effects I got ...
I tried to follow your instructions very carefully.
However, the effect I obtained is still unsatisfactory.
As before, if I select a record in the continuous form
where ID_MainForm = any, ID_SubForm = first, I can open any (ID_SubSubForm = any)desired record correctly.
However, when I choose a record in which ID_SubForm = second or third or any,
the main form opens with the correct ID_MainForm, ID_SubForm = first (wrong) and ID_SubSub = correct!
I was looking in the VBA window for the values that are obtained as a result of the code and the values meet the expectations
while the form opens incorrectly.
In addition, two error messages were created;
ERROR The expression you enter requires the control to be in the active window (?)
The record cannot be added or changed because a related record must exist in the "..." table. (?)

Private Sub Form_Load()
Dim lngSubFormID As Integer
Dim lngFormID As Integer
Dim frmTblHarmonogramBraki As String

'you can find the ID_SubForm by
'ID_SubForm tblSubSubForm ID_SubSubForm
lngSubFormID = DLookup("ID_HarRem_02", "tblHarmonogramBraki", "ID_ZgloszBrakuMater =" & Me.OpenArgs)
'Then assuming the table on which SubForm is based is
'ID_MainForm tblSubForm ID_SubForm
lngFormID = DLookup("Identyfikator_EwidencjiSilnikow", "tblHarmonogramRemontu02", "ID_HarRem_02 =" & lngSubFormID)
MsgBox "STOP1"
'ID_MainForm
Me.filter = "Identyfikator_EwidencjiSilnikow =" & lngFormID
Me.FilterOn = True
MsgBox "STOP2"
'subform
Me.frmHarmonogramRemontu02.Form.filter = lngSubFormID
'subform
Me.frmHarmonogramRemontu02.Form.FilterOn = True
MsgBox "STOP3"
'subform subsubform form!ID
Me.frmHarmonogramRemontu02.Form!frmTblHarmonogramBraki.Form!ID_HarRem_02 = Me.OpenArgs
'subform
Me.frmHarmonogramRemontu02.Form.FilterOn = True
MsgBox "STOP4"

End Sub

Private Sub Polecenie158_Click()
DoCmd.OpenForm "frmHarmonogram_02", , , , , , Me.[Tekst175]
End Sub

Micron · Dec 19, 2019

Not sure if this comment will help or not as I'm a bit late to this party, but I've read through the posts without completely grasping the process:
a subform loads before the main form, so keep that in mind. You cannot get or set anything that's part of a main form during a subform load. In the meantime I should review (again) what's been suggested and see if I can make sense of how named things relate to one another.

pbaldy · Dec 19, 2019

Post 12 was moderated, I'm posting to trigger email notifications.

Cronk · Dec 20, 2019

@masa1
I have no idea how your tables, fields and various forms relate and so cannot comment.

Perhaps you could post a cut down version of your database to show this.

Cronk · Dec 22, 2019

@masa1
Instructions for uploading files are at
https://www.access-programmers.co.uk/forums/showthread.php?t=276413

masa1 · Dec 22, 2019

Hi.

I could not solve the reported problem to get the result I wanted ...
As a reminder I had MainForm> SubForm> SubSubForm to enter data.
Then I created a continuous form through which other database clients could view the content in a short version.
I wanted to use a button or text field in the continuous form, after clicking which I could open the original form containing the directly required ID_MainForm> Id_SubForm and ID_SubSubForm.
Because I couldn't get it, I shared my original form.
In the first MainForm I left the original SubForm, while in MainForm_2 I inserted SubSubForm.
MainForm_2 is a copy of MainForm.
Of course, I made the necessary changes in relationships and questions.
After all these operations, I placed a text box and a button behind which I used the solutions in the continuous form;
BaldyWeb

- "Open a second form to the record selected on the first form"
- "Open a second form to the record selected on the first form, but still include all records"
It is very possible that in my case the obtained effect is better than intended!
I leave the topic as open and I will gladly come back to it.
Because my database is quite large and I'm a beginner, I don't promise but I will try to prepare a short version so that I can publish.:banghead:

Docmd.OpenForm how to refer to a SubSubform record ID (1 Viewer)

masa1

Registered User.

isladogs

MVP / VIP

masa1

Registered User.

isladogs

MVP / VIP

masa1

Registered User.

Attachments

isladogs

MVP / VIP

masa1

Registered User.

Attachments

isladogs

MVP / VIP

Cronk

Registered User.

masa1

Registered User.

Cronk

Registered User.

masa1

Registered User.

Micron

AWF VIP

pbaldy

Wino Moderator

Cronk

Registered User.

Cronk

Registered User.

masa1

Registered User.

Similar threads

Users who are viewing this thread