Linking 2 sub-forms (1 Viewer)

lawman

New member
Local time
Today, 22:44
Joined
Dec 8, 2020
Messages
15
I'm following a guide to link 2 subforms. Can't seem to post the link here but mdb is attached.

Aim is to have an outlook style 2 vertical forms next to each other.

One master subform on left is tasks list datasheet called keydates. On right is detail of the task that has focus.

The keydates form has a combobox called CaseName. This has 2 fields in the source query, (CaseID, CaseName). CaseID is bound field 1 but 0 width so hidden. Only CaseName field 2 is visible in the Combolist.

The detail subform on right needs to link using CaseID.

The guide explains that 2 subforms cannot link to each other.

I've created a txtbox called txtCaseID as per guide in the main form. This is linked to master subform by putting following in the txtbox control source.

Code:
=[frmKeydates].[Form]![CaseName]

Problem, is that txtCaseID only links to the CaseName combobox visible field (ie. 2nd field CaseName). I need to link to the CaseID field 1 which is not visible.

I've tried linking to

Code:
[frmKeydates].Form![CaseName].BoundColumn

But this just return '1' as the bound column. I want the data inside the bound column 1.

Main form txtCaseID seems to link to CaseID bound field in CaseName combobox only when I add/amend new record in frmKeyDates. Otherwise it seems to links to the visible data in combobox CaseName (ie. column 2) instead.

Any advice on how to resolve this?

Thanks
 

Attachments

  • LinkingTwoSubforms.mdb
    356 KB · Views: 135
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 22:44
Joined
Jul 9, 2003
Messages
16,245
Try:-
=[frmKeydates].[Form]![CaseName].[Column](1)

EDIT

Or Possibly:-

=[frmKeydates].[Form]![CaseName].[Column](0)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:44
Joined
May 21, 2018
Messages
8,463
By far the easiest way to do this is to have a hidden textbox on the master form. Call it txtLink. In the on current of subform 1
me.parent.txtlink = me.caseName

in the second subform set up the link
parent link fields:[txtLink]
child link fields:[caseID]
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:44
Joined
May 7, 2009
Messages
19,169
sample:
 

Attachments

  • Database6.accdb
    640 KB · Views: 242

mike60smart

Registered User.
Local time
Today, 22:44
Joined
Aug 6, 2017
Messages
1,899
I'm following a guide to link 2 subforms. Can't seem to post the link here but mdb is attached.

Aim is to have an outlook style 2 vertical forms next to each other.

One master subform on left is tasks list datasheet called keydates. On right is detail of the task that has focus.

The keydates form has a combobox called CaseName. This has 2 fields in the source query, (CaseID, CaseName). CaseID is bound field 1 but 0 width so hidden. Only CaseName field 2 is visible in the Combolist.

The detail subform on right needs to link using CaseID.

The guide explains that 2 subforms cannot link to each other.

I've created a txtbox called txtCaseID as per guide in the main form. This is linked to master subform by putting following in the txtbox control source.

Code:
=[frmKeydates].[Form]![CaseName]

Problem, is that txtCaseID only links to the CaseName combobox visible field (ie. 2nd field CaseName). I need to link to the CaseID field 1 which is not visible.

I've tried linking to

Code:
[frmKeydates].Form![CaseName].BoundColumn

But this just return '1' as the bound column. I want the data inside the bound column 1.

Main form txtCaseID seems to link to CaseID bound field in CaseName combobox only when I add/amend new record in frmKeyDates. Otherwise it seems to links to the visible data in combobox CaseName (ie. column 2) instead.

Any advice on how to resolve this?

Thanks
Hi

This was answered on your crossposted item here https://www.accessforums.net/showthread.php?t=82459&p=467322#post467322
 

lawman

New member
Local time
Today, 22:44
Joined
Dec 8, 2020
Messages
15
Try:-
=[frmKeydates].[Form]![CaseName].[Column](1)

EDIT

Or Possibly:-

=[frmKeydates].[Form]![CaseName].[Column](0)
Hi,

Tried above. There is no option for .[Column] in the expression builder.

The several options include ColumnCount or ColumnHead or others, but no [Column] by itself.

I entered ...[Column](1) anyway and ...[Column](0)

Neither worked.
 

lawman

New member
Local time
Today, 22:44
Joined
Dec 8, 2020
Messages
15
By far the easiest way to do this is to have a hidden textbox on the master form. Call it txtLink. In the on current of subform 1
me.parent.txtlink = me.caseName

in the second subform set up the link
parent link fields:[txtLink]
child link fields:[caseID]

Couldn't see an even 'on current' in subform1.

Tried below but neither worked.

Code:
Private Sub CaseName_GotFocus()
Me.Parent.txtCaseID = Me.CaseName.Column(1)
End Sub


Code:
Private Sub Form_Current()
Me.Parent.txtCaseID = Me.CaseName.Column(1)
End Sub
 

lawman

New member
Local time
Today, 22:44
Joined
Dec 8, 2020
Messages
15
thanks for the effort of making this DB.

The problem is that it doesn't reference hidden column1 of the subform1 field.

I can already do it with the visible CaseName field. But want to reference the primary key instead, which is not visible in bound column 1.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:44
Joined
May 21, 2018
Messages
8,463
Both solutions mine and Arnelgp are basically the same, just a slight modification. Do you get the value to appear in your textbox? If so part 1 is working. Then you just need to get the link correct. I am not sure if you can put brackets around properties. I never tried. If you are using the calculated control instead try
[frmKeydates].[Form]![CaseName].Column(0)

If you want post a db it will be easier to see what you are doing wrong.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 22:44
Joined
Jul 9, 2003
Messages
16,245
Neither worked.

I'm wondering,

This code:-

=[frmKeydates].[Form]![CaseName].[Column](0)

Shouldn't there be a reference to the subform/subreport control in there?

Something like this:-
=[frmKeydates].[Form].subform/subreport.[CaseName].[Column](0)

Where "subform/subreport"
Is the object containing your form, usually and very misleadingly having the same name as the form contained in it, if created automatically by Microsoft.
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 22:44
Joined
Jul 9, 2003
Messages
16,245
Actually I put it in the wrong place, comes from trying to answer the question from my mobile!

Think it should be more like this:-

Something like this:-
=[frmKeydates].subform/subreportControl.[Form].[CaseName].[Column](0)
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 22:44
Joined
Jul 9, 2003
Messages
16,245
I also think majP is correct, you don't need the square brackets around column, and they may well be causing an issue.
 

lawman

New member
Local time
Today, 22:44
Joined
Dec 8, 2020
Messages
15
I also think majP is correct, you don't need the square brackets around column, and they may well be causing an issue.
MajP didn't work. Access automatically puts the [] around Column even if you omit it.

I couldn't get .subform bit in on your suggestion.

db attached with frmKeyCase being the issue i'm trying to resolve.

Easy enough to refer to visible column 1 of combobox. Problem is how to refer to invisible column 0.

Thanks in advance.
 

Attachments

  • BCM16dec.x.accdb
    1.2 MB · Views: 234

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:44
Joined
May 21, 2018
Messages
8,463
You are correct, for some reason in that datasheet view the column property does not work. The simpler solution anyways is to add the caseid to the query.
Code:
Private Sub Form_Current()
  Me.Parent.txtCaseID = Me.CaseID
End Sub
 

Attachments

  • BCM16dec_MajP.x.accdb
    1.7 MB · Views: 247

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 22:44
Joined
Jul 9, 2003
Messages
16,245
Try This:-
 

Attachments

  • BCM16dec_1a.zip
    71.7 KB · Views: 229

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:44
Joined
May 7, 2009
Messages
19,169
see also this one. use CaseID on your Query.
 

Attachments

  • BCM16dec.x.accdb
    1.5 MB · Views: 232

lawman

New member
Local time
Today, 22:44
Joined
Dec 8, 2020
Messages
15
Hi,

All 3 approaches worked great. I wanted to go with MajP or Arnel as it only involved 1 step instead of 2 like Gizmos.

I tried to recreate them so I understand the mechanics, but am confused why it doesn't work when I do them from scratch.

1. MajP - below is my db with your line of code in form current. Why can't my form find CaseID but yours could?

2. Arnel - below is my db with =[frmKeydates].[Form]![CaseName] in parent, same as you used. Why is it that when you used it, it shows the column 1 CaseID ,but when I use same datasource in parent, it shows the column 2 name instead?

3. The right sub-form updates with a noticable delay. Is there any way to resolve this, or is this slowness inherent in Access because it doesn't compile the code? Would it be faster if I tried to program with a compiler such as Lazarus and sqlite?

Thanks
 

Attachments

  • BCMdec17-arnel.accdb
    1.3 MB · Views: 118
  • BCMdec17-majp.accdb
    1.2 MB · Views: 228

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:44
Joined
May 21, 2018
Messages
8,463
1. As we stated you need to add case ID to the first forms query. Makes is a whole lot simpler.
2. The second subform is linked by CaseID and not Case name
 

Users who are viewing this thread

Top Bottom