Linking 2 sub-forms

[lawman]

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.

=[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

[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

 

[MajP]

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]

sample:

 

[mike60smart]

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.

=[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

[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]

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]

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.

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

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

 

[lawman]

sample:

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.

 

[lawman]

Hi

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

Sorry, I thought that was a different site so not cross posted?

Same problem there. All the solutions only work with visible data in the combobox.

There is no solution for the hidden bound column 1 primary key in the combobox.

 

[MajP]

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.

 

[lawman]

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.

 

[MajP]

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.

Private Sub Form_Current()
  Me.Parent.txtCaseID = Me.CaseID
End Sub

 

[arnelgp]

see also this one. use CaseID on your Query.

 

[lawman]

thanks guys. I'll try them all.

 

[lawman]

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

 

[MajP]

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

 

[lawman]

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

sorry for not understanding this fully, but....

1. i've added CaseID in recordsource to the left subform in below db same as you did, but the vba still gives an error that it can't find CaseID member.

2. With Arrnel, his master txtCaseID is only using this code =[frmKeydates].[Form]![CaseName]. It refers to left subform. When I use exactly same code, it gives me different result.

 

[MajP]

If you change it to me!CaseID it will work. The first way is correct Me.Caseid, but it is a long story since you added it to the query after the fact. In fact if you add the field caseid on to the form and save it, it will work with me.caseid. Then you can actually delete it. You hit a very rare case that cause me. not to work and me! to work.

 

[arnelgp]

i abandone the Link Master/Child fields.
instead i used qryCase as rowsource of frmCase.
qryCase is a parameter query.
i Requery frmCase on the Load event of frmKeyCase and
on the Current event of frmKeyDates.

the result is much faster than Link Master/Child fields.

 

[MajP]

That is interesting. Although talking fractions of seconds, it is still noticeably faster. I might have to rethink one of my current dbs that uses a lot of linked subforms.