Solved Open form and subform to specific record in form AND specific record in subform (1 Viewer)

Leo_Polla_Psemata

Registered User.
Local time
Today, 08:25
Joined
Mar 24, 2014
Messages
364
Hi
I have three tables, linked one to many, table A, B and C;
Then, I have Four (4) forms. three of those (Af, Bf. Cf) are form subform subform
Af (single), Bf (single- child of Af) , Cf (continue form, child of Bf) and the continues form ABC
which combines few basic details from A B C and works as summary. I find fast the one out of 4000 records.
Then i click a button and go fast to specific form.
I use the following code and open specific record in form Af

DoCmd.OpenForm "Af", acNormal, "", "[field1_from_A]=" & "'" & .field1_from_A & "'", , acNormal

The question is, while AF opens at specific record, how could i open the subform Bf to a specific record as well ?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:25
Joined
Oct 29, 2018
Messages
21,359
I am guessing you want to "navigate" the subforms to a specific record. If so, take a look at the Bookmark property.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:25
Joined
May 21, 2018
Messages
8,463
May do something like
Code:
DoCmd.OpenForm "Af", acNormal, "", "[field1_from_A]=" & "'" & .field1_from_A & "'", , acNormal
Forms("AF").SubFormControlName.form.Recordset.FindFirst "SomeField = '" & SomeTextValue & "'"
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:25
Joined
Feb 19, 2002
Messages
42,986
How do you know what subform record you want to navigate to?
 

Leo_Polla_Psemata

Registered User.
Local time
Today, 08:25
Joined
Mar 24, 2014
Messages
364
How do you know what subform record you want to navigate to?
Hi, the main form contains controls that are same in Form A , subform B
If i follow docmd openform B, then i go to correct record, yes okay, but i don't see form A. Only form B and her subform C.
If i make docmd openform A, then I go to A but i see the first on the list record B, then i have to "click next" several times
and find manually the correct record in B .

So, yes, i need a code that will open Form and navigate to subform based on controls on the main form. Easier said than done.

I tried the below code but not sure is the second line can work just like that without any connection to the first line.
I am casual access user.

Code:


DoCmd.OpenForm "Af", acNormal, "", "[field1_from_A]=" & "'" & .field1_from_A & "'", , acNormal
Forms("AF").SubFormControlName.form.Recordset.FindFirst "SomeField = '" & SomeTextValue & "'"
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:25
Joined
May 7, 2009
Messages
19,175
you add OpenArgs parameter to your first form:

DoCmd.OpenForm FormName:="Af",WhereCondition:="[field1] = '" & [filterValue] & "'", OpenArgs:="someTextValue"

add code to the Open event of the subformB:
Code:
Private Sub Form_Load()
    Dim varArgs As Variant
    varArgs = Null
    On Error Resume Next
    varArgs = Me.Parent.OpenArgs
    If Not IsNull(varArgs) Then
        Me.Recordset.FindFirst "someField = '" & varArgs & "'"
    End If
End Sub
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:25
Joined
May 21, 2018
Messages
8,463
I tried the below code but not sure is the second line can work just like that without any connection to the first line.
I am casual access user.

Code:

DoCmd.OpenForm "Af", acNormal, "", "[field1_from_A]=" & "'" & .field1_from_A & "'", , acNormal
Forms("AF").SubFormControlName.form.Recordset.FindFirst "SomeField = '" & SomeTextValue & "'
Not sure what more of a connection you would expect to see.
The first line Opens the Form AF.
The second line gets the openform and sets the value of a control on the subform

When you open a form code keeps on running in the calling form (unless you purposely open it in windowmode ACDIALOG). So once a form open another the calling form can modify the open form.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:25
Joined
Feb 19, 2002
Messages
42,986
Maybe sorting the RecordSource for the subforms will solve the problem. If you always want to see the most recently added record, sort descending by the autonumber field (assuming you have an autonumber and assuming it is sequential, which the vast majority are).
 

Users who are viewing this thread

Top Bottom