|
|
I have rather complicated form-structure and want to accomplish rather
cumbersome action, but maybe it would look as fisible task to the forum
experts .
I have ParentForm with unbound cboSelectClient. On it there are:
Subform1 (AccountsList) - datasheet list of client's accounts (each record
has a few controls with account-related data, one of wich is AccountNumber)
Subform2 (AccountPortfolio) with two Subforms:
1) Subform2Sub1 - datasheet list of the securities and deposits the
Subform1' current account contains;
2) Subform2Sub2 - Single-view form for entering buy/sell transactions for
the current account.
When client-name is selected in the ParentForm cboSelectClient, both
Subforms 1 and 2 get criteria by:
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboSelectClient], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
so the Subform1 shows the selected client' accounts, Subform2Sub1 shows the
first-in-the-Subform1-list-account' portfolio and Subform2Sub2 shows the
first transaction (by TransactionID) performed for this account.
The Subform1' AccountNumber control is programmed to change on-click the
Subform2' recordset with:
Dim f As Form
Set f = Forms![ParentForm ]![Subform2].Form
f.Filter = "AccountID = " & Me!AccountID
f.FilterOn = True
so the Subform2Sub-forms 1 and 2 show the related portfolio and first
transaction of the AccountNumber control clicked in the Subform1.
I hope I explained the structure clearly.
Now, I want to always keep the Subform2Sub2 transaction form ready for a new
entry never showing any previous records. This is where the trouble begins. I
tried:
1) to set its DataEntry property to Yes: when the Parentform'
cboSelectClient is selected, it still holds for a new entry, but once the
Subform1' AccountNumber control is clicked, it loads the first transactions
details.
2) to set focus and move it to the new record from the AfterUpdate event of
the Parentform' cboSelectClient:
Me![Subform2].Form![Subform2Sub2].SetFocus
DoCmd.GoToRecord , , acNewRec
but for some reason this code sends the whole Parentform to the new record,
instead of dealing with just the Subform2Sub2 (this code works fine on
subform from the first degree but not in case of "grandchild" SubSubform).
3) to add the same code in the OnClick event of Subform1' AccountNumber
control:
Forms![Parentform]![Subform2].Form![Subform2Sub2].SetFocus
DoCmd.GoToRecord , , acNewRec
but here I get total crash with run-time Error 2105 "You can't go to the
specified record" (the code passes the SetFocus successfully, though, but
crashes on the GoToRecord part).
I can manage with putting in the Subform2Sub2 GoToNewRecord command button,
but it is not likable solution.
If someone has patience to read all the above and manages to comprehend the
task, please help.
|
|
I'm not sure I completely understand your structure, so this may not
be a workable solution for you, but you can give it a try. As always,
make a backup first.
Leave the Data Entry property of Subform2Sub2 set to Yes
Get rid of the code behind the Click event of the AccountNumber
control on Subform1
Add an unbound textbox somewhere on your main form (i.e. txtLinkBox)
Make it invisible and set it's control source to;
=[Subform1].[Form]![AccountID]
Open the properties sheet for the *subform control* of Subform2 (not
the subform itself). You should see properties for Link Child Fields and
Link Master Fields.
Set Link Child Fields to: AccountID
Set Link Master Fields to: txtLinkBox
If I understand your setup correctly, this should automatically refresh
the data in Subform2 when you select a different Account in Subform1
without having to use a filter (which overrides the Data Entry property).
--
_________
Sean Bailey
"Mishanya" wrote:
[Quoted Text] > I have rather complicated form-structure and want to accomplish rather
> cumbersome action, but maybe it would look as fisible task to the forum
> experts .
>
> I have ParentForm with unbound cboSelectClient. On it there are:
> Subform1 (AccountsList) - datasheet list of client's accounts (each record
> has a few controls with account-related data, one of wich is AccountNumber)
> Subform2 (AccountPortfolio) with two Subforms:
> 1) Subform2Sub1 - datasheet list of the securities and deposits the
> Subform1' current account contains;
> 2) Subform2Sub2 - Single-view form for entering buy/sell transactions for
> the current account.
>
> When client-name is selected in the ParentForm cboSelectClient, both
> Subforms 1 and 2 get criteria by:
>
> Dim rs As Object
> Set rs = Me.Recordset.Clone
> rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboSelectClient], 0))
> If Not rs.EOF Then Me.Bookmark = rs.Bookmark
>
> so the Subform1 shows the selected client' accounts, Subform2Sub1 shows the
> first-in-the-Subform1-list-account' portfolio and Subform2Sub2 shows the
> first transaction (by TransactionID) performed for this account.
>
> The Subform1' AccountNumber control is programmed to change on-click the
> Subform2' recordset with:
>
> Dim f As Form
> Set f = Forms![ParentForm ]![Subform2].Form
> f.Filter = "AccountID = " & Me!AccountID
> f.FilterOn = True
>
> so the Subform2Sub-forms 1 and 2 show the related portfolio and first
> transaction of the AccountNumber control clicked in the Subform1.
>
> I hope I explained the structure clearly.
> Now, I want to always keep the Subform2Sub2 transaction form ready for a new
> entry never showing any previous records. This is where the trouble begins. I
> tried:
> 1) to set its DataEntry property to Yes: when the Parentform'
> cboSelectClient is selected, it still holds for a new entry, but once the
> Subform1' AccountNumber control is clicked, it loads the first transactions
> details.
> 2) to set focus and move it to the new record from the AfterUpdate event of
> the Parentform' cboSelectClient:
> Me![Subform2].Form![Subform2Sub2].SetFocus
> DoCmd.GoToRecord , , acNewRec
> but for some reason this code sends the whole Parentform to the new record,
> instead of dealing with just the Subform2Sub2 (this code works fine on
> subform from the first degree but not in case of "grandchild" SubSubform).
> 3) to add the same code in the OnClick event of Subform1' AccountNumber
> control:
> Forms![Parentform]![Subform2].Form![Subform2Sub2].SetFocus
> DoCmd.GoToRecord , , acNewRec
> but here I get total crash with run-time Error 2105 "You can't go to the
> specified record" (the code passes the SetFocus successfully, though, but
> crashes on the GoToRecord part).
>
> I can manage with putting in the Subform2Sub2 GoToNewRecord command button,
> but it is not likable solution.
>
> If someone has patience to read all the above and manages to comprehend the
> task, please help.
>
>
|
|
Hi
Subform2 (as well as Subform1) both are linked to the Parentform (by
ClientID). There is no AccountID in the Parentform underlying table. So the
program does not even suggest others then Parentform underlying table fields.
I did what You've advised (forcibly linked the Subform2 to the txtLinkBox
control) - it is working, but the whole Parentform gets into blinking mood
with "Calculation..." message in the bottom. I reckon, it allows the linking,
but does not like it for some reason.
"Beetle" wrote:
[Quoted Text] > I'm not sure I completely understand your structure, so this may not
> be a workable solution for you, but you can give it a try. As always,
> make a backup first.
>
> Leave the Data Entry property of Subform2Sub2 set to Yes
>
> Get rid of the code behind the Click event of the AccountNumber
> control on Subform1
>
> Add an unbound textbox somewhere on your main form (i.e. txtLinkBox)
> Make it invisible and set it's control source to;
>
> =[Subform1].[Form]![AccountID]
>
> Open the properties sheet for the *subform control* of Subform2 (not
> the subform itself). You should see properties for Link Child Fields and
> Link Master Fields.
>
> Set Link Child Fields to: AccountID
> Set Link Master Fields to: txtLinkBox
>
> If I understand your setup correctly, this should automatically refresh
> the data in Subform2 when you select a different Account in Subform1
> without having to use a filter (which overrides the Data Entry property).
>
> --
> _________
>
> Sean Bailey
>
>
> "Mishanya" wrote:
>
> > I have rather complicated form-structure and want to accomplish rather
> > cumbersome action, but maybe it would look as fisible task to the forum
> > experts .
> >
> > I have ParentForm with unbound cboSelectClient. On it there are:
> > Subform1 (AccountsList) - datasheet list of client's accounts (each record
> > has a few controls with account-related data, one of wich is AccountNumber)
> > Subform2 (AccountPortfolio) with two Subforms:
> > 1) Subform2Sub1 - datasheet list of the securities and deposits the
> > Subform1' current account contains;
> > 2) Subform2Sub2 - Single-view form for entering buy/sell transactions for
> > the current account.
> >
> > When client-name is selected in the ParentForm cboSelectClient, both
> > Subforms 1 and 2 get criteria by:
> >
> > Dim rs As Object
> > Set rs = Me.Recordset.Clone
> > rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboSelectClient], 0))
> > If Not rs.EOF Then Me.Bookmark = rs.Bookmark
> >
> > so the Subform1 shows the selected client' accounts, Subform2Sub1 shows the
> > first-in-the-Subform1-list-account' portfolio and Subform2Sub2 shows the
> > first transaction (by TransactionID) performed for this account.
> >
> > The Subform1' AccountNumber control is programmed to change on-click the
> > Subform2' recordset with:
> >
> > Dim f As Form
> > Set f = Forms![ParentForm ]![Subform2].Form
> > f.Filter = "AccountID = " & Me!AccountID
> > f.FilterOn = True
> >
> > so the Subform2Sub-forms 1 and 2 show the related portfolio and first
> > transaction of the AccountNumber control clicked in the Subform1.
> >
> > I hope I explained the structure clearly.
> > Now, I want to always keep the Subform2Sub2 transaction form ready for a new
> > entry never showing any previous records. This is where the trouble begins. I
> > tried:
> > 1) to set its DataEntry property to Yes: when the Parentform'
> > cboSelectClient is selected, it still holds for a new entry, but once the
> > Subform1' AccountNumber control is clicked, it loads the first transactions
> > details.
> > 2) to set focus and move it to the new record from the AfterUpdate event of
> > the Parentform' cboSelectClient:
> > Me![Subform2].Form![Subform2Sub2].SetFocus
> > DoCmd.GoToRecord , , acNewRec
> > but for some reason this code sends the whole Parentform to the new record,
> > instead of dealing with just the Subform2Sub2 (this code works fine on
> > subform from the first degree but not in case of "grandchild" SubSubform).
> > 3) to add the same code in the OnClick event of Subform1' AccountNumber
> > control:
> > Forms![Parentform]![Subform2].Form![Subform2Sub2].SetFocus
> > DoCmd.GoToRecord , , acNewRec
> > but here I get total crash with run-time Error 2105 "You can't go to the
> > specified record" (the code passes the SetFocus successfully, though, but
> > crashes on the GoToRecord part).
> >
> > I can manage with putting in the Subform2Sub2 GoToNewRecord command button,
> > but it is not likable solution.
> >
> > If someone has patience to read all the above and manages to comprehend the
> > task, please help.
> >
> >
|
|
My impression based on your original post was that the purpose of
Subform2 was to show (through two additional subforms) information
that is related to each Account in Subform1. In a case like that, Subform2
would not be linked to the Parent form via ClientID (in fact, Subform2 would
likely not even have a ClientID field). It would be linked to Subform1 via
AccountID (through the hidden text box).
Is there some other information in Subform2 that relates directly to the
Client (not their Accounts) which requires that you have Subform2 linked
directly to the Parent form via ClientID?
--
_________
Sean Bailey
"Mishanya" wrote:
[Quoted Text] > Hi
> Subform2 (as well as Subform1) both are linked to the Parentform (by
> ClientID). There is no AccountID in the Parentform underlying table. So the
> program does not even suggest others then Parentform underlying table fields.
> I did what You've advised (forcibly linked the Subform2 to the txtLinkBox
> control) - it is working, but the whole Parentform gets into blinking mood
> with "Calculation..." message in the bottom. I reckon, it allows the linking,
> but does not like it for some reason.
>
>
> "Beetle" wrote:
>
> > I'm not sure I completely understand your structure, so this may not
> > be a workable solution for you, but you can give it a try. As always,
> > make a backup first.
> >
> > Leave the Data Entry property of Subform2Sub2 set to Yes
> >
> > Get rid of the code behind the Click event of the AccountNumber
> > control on Subform1
> >
> > Add an unbound textbox somewhere on your main form (i.e. txtLinkBox)
> > Make it invisible and set it's control source to;
> >
> > =[Subform1].[Form]![AccountID]
> >
> > Open the properties sheet for the *subform control* of Subform2 (not
> > the subform itself). You should see properties for Link Child Fields and
> > Link Master Fields.
> >
> > Set Link Child Fields to: AccountID
> > Set Link Master Fields to: txtLinkBox
> >
> > If I understand your setup correctly, this should automatically refresh
> > the data in Subform2 when you select a different Account in Subform1
> > without having to use a filter (which overrides the Data Entry property).
> >
> > --
> > _________
> >
> > Sean Bailey
> >
> >
> > "Mishanya" wrote:
> >
> > > I have rather complicated form-structure and want to accomplish rather
> > > cumbersome action, but maybe it would look as fisible task to the forum
> > > experts .
> > >
> > > I have ParentForm with unbound cboSelectClient. On it there are:
> > > Subform1 (AccountsList) - datasheet list of client's accounts (each record
> > > has a few controls with account-related data, one of wich is AccountNumber)
> > > Subform2 (AccountPortfolio) with two Subforms:
> > > 1) Subform2Sub1 - datasheet list of the securities and deposits the
> > > Subform1' current account contains;
> > > 2) Subform2Sub2 - Single-view form for entering buy/sell transactions for
> > > the current account.
> > >
> > > When client-name is selected in the ParentForm cboSelectClient, both
> > > Subforms 1 and 2 get criteria by:
> > >
> > > Dim rs As Object
> > > Set rs = Me.Recordset.Clone
> > > rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboSelectClient], 0))
> > > If Not rs.EOF Then Me.Bookmark = rs.Bookmark
> > >
> > > so the Subform1 shows the selected client' accounts, Subform2Sub1 shows the
> > > first-in-the-Subform1-list-account' portfolio and Subform2Sub2 shows the
> > > first transaction (by TransactionID) performed for this account.
> > >
> > > The Subform1' AccountNumber control is programmed to change on-click the
> > > Subform2' recordset with:
> > >
> > > Dim f As Form
> > > Set f = Forms![ParentForm ]![Subform2].Form
> > > f.Filter = "AccountID = " & Me!AccountID
> > > f.FilterOn = True
> > >
> > > so the Subform2Sub-forms 1 and 2 show the related portfolio and first
> > > transaction of the AccountNumber control clicked in the Subform1.
> > >
> > > I hope I explained the structure clearly.
> > > Now, I want to always keep the Subform2Sub2 transaction form ready for a new
> > > entry never showing any previous records. This is where the trouble begins. I
> > > tried:
> > > 1) to set its DataEntry property to Yes: when the Parentform'
> > > cboSelectClient is selected, it still holds for a new entry, but once the
> > > Subform1' AccountNumber control is clicked, it loads the first transactions
> > > details.
> > > 2) to set focus and move it to the new record from the AfterUpdate event of
> > > the Parentform' cboSelectClient:
> > > Me![Subform2].Form![Subform2Sub2].SetFocus
> > > DoCmd.GoToRecord , , acNewRec
> > > but for some reason this code sends the whole Parentform to the new record,
> > > instead of dealing with just the Subform2Sub2 (this code works fine on
> > > subform from the first degree but not in case of "grandchild" SubSubform).
> > > 3) to add the same code in the OnClick event of Subform1' AccountNumber
> > > control:
> > > Forms![Parentform]![Subform2].Form![Subform2Sub2].SetFocus
> > > DoCmd.GoToRecord , , acNewRec
> > > but here I get total crash with run-time Error 2105 "You can't go to the
> > > specified record" (the code passes the SetFocus successfully, though, but
> > > crashes on the GoToRecord part).
> > >
> > > I can manage with putting in the Subform2Sub2 GoToNewRecord command button,
> > > but it is not likable solution.
> > >
> > > If someone has patience to read all the above and manages to comprehend the
> > > task, please help.
> > >
> > >
|
|
You've understood absolutely right.
Subform2 is detailing each record of Subform1 trough 2 additional subforms.
I designed them originally without intention to plant in one big parent form.
So each had ClientID and was to be opened by ckicking buttons of some main
Switchboard with cboSelectClient on it. But then, learning more about Access
(and moving from 12.9" lap to 19" screen), I came up with all-in-one style.
Do you suggest to reconstruct it and plant Subform2 with its subforms into
the Subform1 (wich,in turn, is planted in the Parentform), or there is some
possible solution? Is it OK to have subform of 4-degree at all?
"Beetle" wrote:
[Quoted Text] > My impression based on your original post was that the purpose of
> Subform2 was to show (through two additional subforms) information
> that is related to each Account in Subform1. In a case like that, Subform2
> would not be linked to the Parent form via ClientID (in fact, Subform2 would
> likely not even have a ClientID field). It would be linked to Subform1 via
> AccountID (through the hidden text box).
>
> Is there some other information in Subform2 that relates directly to the
> Client (not their Accounts) which requires that you have Subform2 linked
> directly to the Parent form via ClientID?
> --
> _________
>
> Sean Bailey
>
>
> "Mishanya" wrote:
>
> > Hi
> > Subform2 (as well as Subform1) both are linked to the Parentform (by
> > ClientID). There is no AccountID in the Parentform underlying table. So the
> > program does not even suggest others then Parentform underlying table fields.
> > I did what You've advised (forcibly linked the Subform2 to the txtLinkBox
> > control) - it is working, but the whole Parentform gets into blinking mood
> > with "Calculation..." message in the bottom. I reckon, it allows the linking,
> > but does not like it for some reason.
> >
> >
> > "Beetle" wrote:
> >
> > > I'm not sure I completely understand your structure, so this may not
> > > be a workable solution for you, but you can give it a try. As always,
> > > make a backup first.
> > >
> > > Leave the Data Entry property of Subform2Sub2 set to Yes
> > >
> > > Get rid of the code behind the Click event of the AccountNumber
> > > control on Subform1
> > >
> > > Add an unbound textbox somewhere on your main form (i.e. txtLinkBox)
> > > Make it invisible and set it's control source to;
> > >
> > > =[Subform1].[Form]![AccountID]
> > >
> > > Open the properties sheet for the *subform control* of Subform2 (not
> > > the subform itself). You should see properties for Link Child Fields and
> > > Link Master Fields.
> > >
> > > Set Link Child Fields to: AccountID
> > > Set Link Master Fields to: txtLinkBox
> > >
> > > If I understand your setup correctly, this should automatically refresh
> > > the data in Subform2 when you select a different Account in Subform1
> > > without having to use a filter (which overrides the Data Entry property).
> > >
> > > --
> > > _________
> > >
> > > Sean Bailey
> > >
> > >
> > > "Mishanya" wrote:
> > >
> > > > I have rather complicated form-structure and want to accomplish rather
> > > > cumbersome action, but maybe it would look as fisible task to the forum
> > > > experts .
> > > >
> > > > I have ParentForm with unbound cboSelectClient. On it there are:
> > > > Subform1 (AccountsList) - datasheet list of client's accounts (each record
> > > > has a few controls with account-related data, one of wich is AccountNumber)
> > > > Subform2 (AccountPortfolio) with two Subforms:
> > > > 1) Subform2Sub1 - datasheet list of the securities and deposits the
> > > > Subform1' current account contains;
> > > > 2) Subform2Sub2 - Single-view form for entering buy/sell transactions for
> > > > the current account.
> > > >
> > > > When client-name is selected in the ParentForm cboSelectClient, both
> > > > Subforms 1 and 2 get criteria by:
> > > >
> > > > Dim rs As Object
> > > > Set rs = Me.Recordset.Clone
> > > > rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboSelectClient], 0))
> > > > If Not rs.EOF Then Me.Bookmark = rs.Bookmark
> > > >
> > > > so the Subform1 shows the selected client' accounts, Subform2Sub1 shows the
> > > > first-in-the-Subform1-list-account' portfolio and Subform2Sub2 shows the
> > > > first transaction (by TransactionID) performed for this account.
> > > >
> > > > The Subform1' AccountNumber control is programmed to change on-click the
> > > > Subform2' recordset with:
> > > >
> > > > Dim f As Form
> > > > Set f = Forms![ParentForm ]![Subform2].Form
> > > > f.Filter = "AccountID = " & Me!AccountID
> > > > f.FilterOn = True
> > > >
> > > > so the Subform2Sub-forms 1 and 2 show the related portfolio and first
> > > > transaction of the AccountNumber control clicked in the Subform1.
> > > >
> > > > I hope I explained the structure clearly.
> > > > Now, I want to always keep the Subform2Sub2 transaction form ready for a new
> > > > entry never showing any previous records. This is where the trouble begins. I
> > > > tried:
> > > > 1) to set its DataEntry property to Yes: when the Parentform'
> > > > cboSelectClient is selected, it still holds for a new entry, but once the
> > > > Subform1' AccountNumber control is clicked, it loads the first transactions
> > > > details.
> > > > 2) to set focus and move it to the new record from the AfterUpdate event of
> > > > the Parentform' cboSelectClient:
> > > > Me![Subform2].Form![Subform2Sub2].SetFocus
> > > > DoCmd.GoToRecord , , acNewRec
> > > > but for some reason this code sends the whole Parentform to the new record,
> > > > instead of dealing with just the Subform2Sub2 (this code works fine on
> > > > subform from the first degree but not in case of "grandchild" SubSubform).
> > > > 3) to add the same code in the OnClick event of Subform1' AccountNumber
> > > > control:
> > > > Forms![Parentform]![Subform2].Form![Subform2Sub2].SetFocus
> > > > DoCmd.GoToRecord , , acNewRec
> > > > but here I get total crash with run-time Error 2105 "You can't go to the
> > > > specified record" (the code passes the SetFocus successfully, though, but
> > > > crashes on the GoToRecord part).
> > > >
> > > > I can manage with putting in the Subform2Sub2 GoToNewRecord command button,
> > > > but it is not likable solution.
> > > >
> > > > If someone has patience to read all the above and manages to comprehend the
> > > > task, please help.
> > > >
> > > >
|
|
Since Subform2 seems to serve no purpose other than to hold the
two additional sub subforms, have you considered just getting rid
of it and putting those two sub subforms directly on the main form
and linking each one to Subform1 using the method I described earlier?
--
_________
Sean Bailey
"Mishanya" wrote:
[Quoted Text] > You've understood absolutely right.
> Subform2 is detailing each record of Subform1 trough 2 additional subforms.
> I designed them originally without intention to plant in one big parent form.
> So each had ClientID and was to be opened by ckicking buttons of some main
> Switchboard with cboSelectClient on it. But then, learning more about Access
> (and moving from 12.9" lap to 19" screen), I came up with all-in-one style.
> Do you suggest to reconstruct it and plant Subform2 with its subforms into
> the Subform1 (wich,in turn, is planted in the Parentform), or there is some
> possible solution? Is it OK to have subform of 4-degree at all?
>
> "Beetle" wrote:
>
> > My impression based on your original post was that the purpose of
> > Subform2 was to show (through two additional subforms) information
> > that is related to each Account in Subform1. In a case like that, Subform2
> > would not be linked to the Parent form via ClientID (in fact, Subform2 would
> > likely not even have a ClientID field). It would be linked to Subform1 via
> > AccountID (through the hidden text box).
> >
> > Is there some other information in Subform2 that relates directly to the
> > Client (not their Accounts) which requires that you have Subform2 linked
> > directly to the Parent form via ClientID?
> > --
> > _________
> >
> > Sean Bailey
> >
> >
> > "Mishanya" wrote:
> >
> > > Hi
> > > Subform2 (as well as Subform1) both are linked to the Parentform (by
> > > ClientID). There is no AccountID in the Parentform underlying table. So the
> > > program does not even suggest others then Parentform underlying table fields.
> > > I did what You've advised (forcibly linked the Subform2 to the txtLinkBox
> > > control) - it is working, but the whole Parentform gets into blinking mood
> > > with "Calculation..." message in the bottom. I reckon, it allows the linking,
> > > but does not like it for some reason.
> > >
> > >
> > > "Beetle" wrote:
> > >
> > > > I'm not sure I completely understand your structure, so this may not
> > > > be a workable solution for you, but you can give it a try. As always,
> > > > make a backup first.
> > > >
> > > > Leave the Data Entry property of Subform2Sub2 set to Yes
> > > >
> > > > Get rid of the code behind the Click event of the AccountNumber
> > > > control on Subform1
> > > >
> > > > Add an unbound textbox somewhere on your main form (i.e. txtLinkBox)
> > > > Make it invisible and set it's control source to;
> > > >
> > > > =[Subform1].[Form]![AccountID]
> > > >
> > > > Open the properties sheet for the *subform control* of Subform2 (not
> > > > the subform itself). You should see properties for Link Child Fields and
> > > > Link Master Fields.
> > > >
> > > > Set Link Child Fields to: AccountID
> > > > Set Link Master Fields to: txtLinkBox
> > > >
> > > > If I understand your setup correctly, this should automatically refresh
> > > > the data in Subform2 when you select a different Account in Subform1
> > > > without having to use a filter (which overrides the Data Entry property).
> > > >
> > > > --
> > > > _________
> > > >
> > > > Sean Bailey
> > > >
> > > >
> > > > "Mishanya" wrote:
> > > >
> > > > > I have rather complicated form-structure and want to accomplish rather
> > > > > cumbersome action, but maybe it would look as fisible task to the forum
> > > > > experts .
> > > > >
> > > > > I have ParentForm with unbound cboSelectClient. On it there are:
> > > > > Subform1 (AccountsList) - datasheet list of client's accounts (each record
> > > > > has a few controls with account-related data, one of wich is AccountNumber)
> > > > > Subform2 (AccountPortfolio) with two Subforms:
> > > > > 1) Subform2Sub1 - datasheet list of the securities and deposits the
> > > > > Subform1' current account contains;
> > > > > 2) Subform2Sub2 - Single-view form for entering buy/sell transactions for
> > > > > the current account.
> > > > >
> > > > > When client-name is selected in the ParentForm cboSelectClient, both
> > > > > Subforms 1 and 2 get criteria by:
> > > > >
> > > > > Dim rs As Object
> > > > > Set rs = Me.Recordset.Clone
> > > > > rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboSelectClient], 0))
> > > > > If Not rs.EOF Then Me.Bookmark = rs.Bookmark
> > > > >
> > > > > so the Subform1 shows the selected client' accounts, Subform2Sub1 shows the
> > > > > first-in-the-Subform1-list-account' portfolio and Subform2Sub2 shows the
> > > > > first transaction (by TransactionID) performed for this account.
> > > > >
> > > > > The Subform1' AccountNumber control is programmed to change on-click the
> > > > > Subform2' recordset with:
> > > > >
> > > > > Dim f As Form
> > > > > Set f = Forms![ParentForm ]![Subform2].Form
> > > > > f.Filter = "AccountID = " & Me!AccountID
> > > > > f.FilterOn = True
> > > > >
> > > > > so the Subform2Sub-forms 1 and 2 show the related portfolio and first
> > > > > transaction of the AccountNumber control clicked in the Subform1.
> > > > >
> > > > > I hope I explained the structure clearly.
> > > > > Now, I want to always keep the Subform2Sub2 transaction form ready for a new
> > > > > entry never showing any previous records. This is where the trouble begins. I
> > > > > tried:
> > > > > 1) to set its DataEntry property to Yes: when the Parentform'
> > > > > cboSelectClient is selected, it still holds for a new entry, but once the
> > > > > Subform1' AccountNumber control is clicked, it loads the first transactions
> > > > > details.
> > > > > 2) to set focus and move it to the new record from the AfterUpdate event of
> > > > > the Parentform' cboSelectClient:
> > > > > Me![Subform2].Form![Subform2Sub2].SetFocus
> > > > > DoCmd.GoToRecord , , acNewRec
> > > > > but for some reason this code sends the whole Parentform to the new record,
> > > > > instead of dealing with just the Subform2Sub2 (this code works fine on
> > > > > subform from the first degree but not in case of "grandchild" SubSubform).
> > > > > 3) to add the same code in the OnClick event of Subform1' AccountNumber
> > > > > control:
> > > > > Forms![Parentform]![Subform2].Form![Subform2Sub2].SetFocus
> > > > > DoCmd.GoToRecord , , acNewRec
> > > > > but here I get total crash with run-time Error 2105 "You can't go to the
> > > > > specified record" (the code passes the SetFocus successfully, though, but
> > > > > crashes on the GoToRecord part).
> > > > >
> > > > > I can manage with putting in the Subform2Sub2 GoToNewRecord command button,
> > > > > but it is not likable solution.
> > > > >
> > > > > If someone has patience to read all the above and manages to comprehend the
> > > > > task, please help.
> > > > >
> > > > >
|
|
Hi Sean!
I've just tried to incorporate the Subform2 into the Subform1 - and this
reminded me of why I'd dropped this idea in the 1st place - I can't have
subform planted into continuous or datasheet-view form! As I want to have all
the accounts shown in the Mainform and also to be able to present its
containing in the same Mainform, I put the accounts list and the [account'
potfolio list + transaction form] in 2 different independant subforms linked
to the Mainform by Client ID. So when the cboSelectClient is selected, the
Subform1 loads the whole list of accounts, while the Subform2 loads the
recordset related to the 1st account in the list (it does not have filter yet
- this happens by default).
Then I've managed to control the Subform2 from the Subform1 by passing
filter on AccountID from one to another. Now I just need to make a
"finishing" by making the transaction subform of the Subform2 available only
for new entry.
Any chance?
"Beetle" wrote:
[Quoted Text] > My impression based on your original post was that the purpose of
> Subform2 was to show (through two additional subforms) information
> that is related to each Account in Subform1. In a case like that, Subform2
> would not be linked to the Parent form via ClientID (in fact, Subform2 would
> likely not even have a ClientID field). It would be linked to Subform1 via
> AccountID (through the hidden text box).
>
> Is there some other information in Subform2 that relates directly to the
> Client (not their Accounts) which requires that you have Subform2 linked
> directly to the Parent form via ClientID?
> --
> _________
>
> Sean Bailey
>
>
> "Mishanya" wrote:
>
> > Hi
> > Subform2 (as well as Subform1) both are linked to the Parentform (by
> > ClientID). There is no AccountID in the Parentform underlying table. So the
> > program does not even suggest others then Parentform underlying table fields.
> > I did what You've advised (forcibly linked the Subform2 to the txtLinkBox
> > control) - it is working, but the whole Parentform gets into blinking mood
> > with "Calculation..." message in the bottom. I reckon, it allows the linking,
> > but does not like it for some reason.
> >
> >
> > "Beetle" wrote:
> >
> > > I'm not sure I completely understand your structure, so this may not
> > > be a workable solution for you, but you can give it a try. As always,
> > > make a backup first.
> > >
> > > Leave the Data Entry property of Subform2Sub2 set to Yes
> > >
> > > Get rid of the code behind the Click event of the AccountNumber
> > > control on Subform1
> > >
> > > Add an unbound textbox somewhere on your main form (i.e. txtLinkBox)
> > > Make it invisible and set it's control source to;
> > >
> > > =[Subform1].[Form]![AccountID]
> > >
> > > Open the properties sheet for the *subform control* of Subform2 (not
> > > the subform itself). You should see properties for Link Child Fields and
> > > Link Master Fields.
> > >
> > > Set Link Child Fields to: AccountID
> > > Set Link Master Fields to: txtLinkBox
> > >
> > > If I understand your setup correctly, this should automatically refresh
> > > the data in Subform2 when you select a different Account in Subform1
> > > without having to use a filter (which overrides the Data Entry property).
> > >
> > > --
> > > _________
> > >
> > > Sean Bailey
> > >
> > >
> > > "Mishanya" wrote:
> > >
> > > > I have rather complicated form-structure and want to accomplish rather
> > > > cumbersome action, but maybe it would look as fisible task to the forum
> > > > experts .
> > > >
> > > > I have ParentForm with unbound cboSelectClient. On it there are:
> > > > Subform1 (AccountsList) - datasheet list of client's accounts (each record
> > > > has a few controls with account-related data, one of wich is AccountNumber)
> > > > Subform2 (AccountPortfolio) with two Subforms:
> > > > 1) Subform2Sub1 - datasheet list of the securities and deposits the
> > > > Subform1' current account contains;
> > > > 2) Subform2Sub2 - Single-view form for entering buy/sell transactions for
> > > > the current account.
> > > >
> > > > When client-name is selected in the ParentForm cboSelectClient, both
> > > > Subforms 1 and 2 get criteria by:
> > > >
> > > > Dim rs As Object
> > > > Set rs = Me.Recordset.Clone
> > > > rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboSelectClient], 0))
> > > > If Not rs.EOF Then Me.Bookmark = rs.Bookmark
> > > >
> > > > so the Subform1 shows the selected client' accounts, Subform2Sub1 shows the
> > > > first-in-the-Subform1-list-account' portfolio and Subform2Sub2 shows the
> > > > first transaction (by TransactionID) performed for this account.
> > > >
> > > > The Subform1' AccountNumber control is programmed to change on-click the
> > > > Subform2' recordset with:
> > > >
> > > > Dim f As Form
> > > > Set f = Forms![ParentForm ]![Subform2].Form
> > > > f.Filter = "AccountID = " & Me!AccountID
> > > > f.FilterOn = True
> > > >
> > > > so the Subform2Sub-forms 1 and 2 show the related portfolio and first
> > > > transaction of the AccountNumber control clicked in the Subform1.
> > > >
> > > > I hope I explained the structure clearly.
> > > > Now, I want to always keep the Subform2Sub2 transaction form ready for a new
> > > > entry never showing any previous records. This is where the trouble begins. I
> > > > tried:
> > > > 1) to set its DataEntry property to Yes: when the Parentform'
> > > > cboSelectClient is selected, it still holds for a new entry, but once the
> > > > Subform1' AccountNumber control is clicked, it loads the first transactions
> > > > details.
> > > > 2) to set focus and move it to the new record from the AfterUpdate event of
> > > > the Parentform' cboSelectClient:
> > > > Me![Subform2].Form![Subform2Sub2].SetFocus
> > > > DoCmd.GoToRecord , , acNewRec
> > > > but for some reason this code sends the whole Parentform to the new record,
> > > > instead of dealing with just the Subform2Sub2 (this code works fine on
> > > > subform from the first degree but not in case of "grandchild" SubSubform).
> > > > 3) to add the same code in the OnClick event of Subform1' AccountNumber
> > > > control:
> > > > Forms![Parentform]![Subform2].Form![Subform2Sub2].SetFocus
> > > > DoCmd.GoToRecord , , acNewRec
> > > > but here I get total crash with run-time Error 2105 "You can't go to the
> > > > specified record" (the code passes the SetFocus successfully, though, but
> > > > crashes on the GoToRecord part).
> > > >
> > > > I can manage with putting in the Subform2Sub2 GoToNewRecord command button,
> > > > but it is not likable solution.
> > > >
> > > > If someone has patience to read all the above and manages to comprehend the
> > > > task, please help.
> > > >
> > > >
|
|
I guess you got it to work?
--
_________
Sean Bailey
"Mishanya" wrote:
[Quoted Text] > Hi Sean!
> I've just tried to incorporate the Subform2 into the Subform1 - and this
> reminded me of why I'd dropped this idea in the 1st place - I can't have
> subform planted into continuous or datasheet-view form! As I want to have all
> the accounts shown in the Mainform and also to be able to present its
> containing in the same Mainform, I put the accounts list and the [account'
> potfolio list + transaction form] in 2 different independant subforms linked
> to the Mainform by Client ID. So when the cboSelectClient is selected, the
> Subform1 loads the whole list of accounts, while the Subform2 loads the
> recordset related to the 1st account in the list (it does not have filter yet
> - this happens by default).
> Then I've managed to control the Subform2 from the Subform1 by passing
> filter on AccountID from one to another. Now I just need to make a
> "finishing" by making the transaction subform of the Subform2 available only
> for new entry.
> Any chance?
>
>
> "Beetle" wrote:
>
> > My impression based on your original post was that the purpose of
> > Subform2 was to show (through two additional subforms) information
> > that is related to each Account in Subform1. In a case like that, Subform2
> > would not be linked to the Parent form via ClientID (in fact, Subform2 would
> > likely not even have a ClientID field). It would be linked to Subform1 via
> > AccountID (through the hidden text box).
> >
> > Is there some other information in Subform2 that relates directly to the
> > Client (not their Accounts) which requires that you have Subform2 linked
> > directly to the Parent form via ClientID?
> > --
> > _________
> >
> > Sean Bailey
> >
> >
> > "Mishanya" wrote:
> >
> > > Hi
> > > Subform2 (as well as Subform1) both are linked to the Parentform (by
> > > ClientID). There is no AccountID in the Parentform underlying table. So the
> > > program does not even suggest others then Parentform underlying table fields.
> > > I did what You've advised (forcibly linked the Subform2 to the txtLinkBox
> > > control) - it is working, but the whole Parentform gets into blinking mood
> > > with "Calculation..." message in the bottom. I reckon, it allows the linking,
> > > but does not like it for some reason.
> > >
> > >
> > > "Beetle" wrote:
> > >
> > > > I'm not sure I completely understand your structure, so this may not
> > > > be a workable solution for you, but you can give it a try. As always,
> > > > make a backup first.
> > > >
> > > > Leave the Data Entry property of Subform2Sub2 set to Yes
> > > >
> > > > Get rid of the code behind the Click event of the AccountNumber
> > > > control on Subform1
> > > >
> > > > Add an unbound textbox somewhere on your main form (i.e. txtLinkBox)
> > > > Make it invisible and set it's control source to;
> > > >
> > > > =[Subform1].[Form]![AccountID]
> > > >
> > > > Open the properties sheet for the *subform control* of Subform2 (not
> > > > the subform itself). You should see properties for Link Child Fields and
> > > > Link Master Fields.
> > > >
> > > > Set Link Child Fields to: AccountID
> > > > Set Link Master Fields to: txtLinkBox
> > > >
> > > > If I understand your setup correctly, this should automatically refresh
> > > > the data in Subform2 when you select a different Account in Subform1
> > > > without having to use a filter (which overrides the Data Entry property).
> > > >
> > > > --
> > > > _________
> > > >
> > > > Sean Bailey
> > > >
> > > >
> > > > "Mishanya" wrote:
> > > >
> > > > > I have rather complicated form-structure and want to accomplish rather
> > > > > cumbersome action, but maybe it would look as fisible task to the forum
> > > > > experts .
> > > > >
> > > > > I have ParentForm with unbound cboSelectClient. On it there are:
> > > > > Subform1 (AccountsList) - datasheet list of client's accounts (each record
> > > > > has a few controls with account-related data, one of wich is AccountNumber)
> > > > > Subform2 (AccountPortfolio) with two Subforms:
> > > > > 1) Subform2Sub1 - datasheet list of the securities and deposits the
> > > > > Subform1' current account contains;
> > > > > 2) Subform2Sub2 - Single-view form for entering buy/sell transactions for
> > > > > the current account.
> > > > >
> > > > > When client-name is selected in the ParentForm cboSelectClient, both
> > > > > Subforms 1 and 2 get criteria by:
> > > > >
> > > > > Dim rs As Object
> > > > > Set rs = Me.Recordset.Clone
> > > > > rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboSelectClient], 0))
> > > > > If Not rs.EOF Then Me.Bookmark = rs.Bookmark
> > > > >
> > > > > so the Subform1 shows the selected client' accounts, Subform2Sub1 shows the
> > > > > first-in-the-Subform1-list-account' portfolio and Subform2Sub2 shows the
> > > > > first transaction (by TransactionID) performed for this account.
> > > > >
> > > > > The Subform1' AccountNumber control is programmed to change on-click the
> > > > > Subform2' recordset with:
> > > > >
> > > > > Dim f As Form
> > > > > Set f = Forms![ParentForm ]![Subform2].Form
> > > > > f.Filter = "AccountID = " & Me!AccountID
> > > > > f.FilterOn = True
> > > > >
> > > > > so the Subform2Sub-forms 1 and 2 show the related portfolio and first
> > > > > transaction of the AccountNumber control clicked in the Subform1.
> > > > >
> > > > > I hope I explained the structure clearly.
> > > > > Now, I want to always keep the Subform2Sub2 transaction form ready for a new
> > > > > entry never showing any previous records. This is where the trouble begins. I
> > > > > tried:
> > > > > 1) to set its DataEntry property to Yes: when the Parentform'
> > > > > cboSelectClient is selected, it still holds for a new entry, but once the
> > > > > Subform1' AccountNumber control is clicked, it loads the first transactions
> > > > > details.
> > > > > 2) to set focus and move it to the new record from the AfterUpdate event of
> > > > > the Parentform' cboSelectClient:
> > > > > Me![Subform2].Form![Subform2Sub2].SetFocus
> > > > > DoCmd.GoToRecord , , acNewRec
> > > > > but for some reason this code sends the whole Parentform to the new record,
> > > > > instead of dealing with just the Subform2Sub2 (this code works fine on
> > > > > subform from the first degree but not in case of "grandchild" SubSubform).
> > > > > 3) to add the same code in the OnClick event of Subform1' AccountNumber
> > > > > control:
> > > > > Forms![Parentform]![Subform2].Form![Subform2Sub2].SetFocus
> > > > > DoCmd.GoToRecord , , acNewRec
> > > > > but here I get total crash with run-time Error 2105 "You can't go to the
> > > > > specified record" (the code passes the SetFocus successfully, though, but
> > > > > crashes on the GoToRecord part).
> > > > >
> > > > > I can manage with putting in the Subform2Sub2 GoToNewRecord command button,
> > > > > but it is not likable solution.
> > > > >
> > > > > If someone has patience to read all the above and manages to comprehend the
> > > > > task, please help.
> > > > >
> > > > >
|
|
Hi
I've done exactly as You told - took the Subform2 subforms (portfolio and
transactions) out, put them directly on the Parentform and linked them to the
hidden box with AccountID mirrored from the Subform1 + erased the CLick event
from the Subform1.
The transaction subform keeps itself on new allright, but the portfolio
subform does not like linking to the hidden box - it makes the whole
structure go to loop (blinking+ "calculation...") although the data is
presented allright. Maybe it has something to do with portfolio subform being
datasheet and transaction subform being singleform - this kind of linking
works for single but can not complete the loop for datasheet?
"Beetle" wrote:
[Quoted Text] > I guess you got it to work?
> --
> _________
>
> Sean Bailey
>
>
> "Mishanya" wrote:
>
> > Hi Sean!
> > I've just tried to incorporate the Subform2 into the Subform1 - and this
> > reminded me of why I'd dropped this idea in the 1st place - I can't have
> > subform planted into continuous or datasheet-view form! As I want to have all
> > the accounts shown in the Mainform and also to be able to present its
> > containing in the same Mainform, I put the accounts list and the [account'
> > potfolio list + transaction form] in 2 different independant subforms linked
> > to the Mainform by Client ID. So when the cboSelectClient is selected, the
> > Subform1 loads the whole list of accounts, while the Subform2 loads the
> > recordset related to the 1st account in the list (it does not have filter yet
> > - this happens by default).
> > Then I've managed to control the Subform2 from the Subform1 by passing
> > filter on AccountID from one to another. Now I just need to make a
> > "finishing" by making the transaction subform of the Subform2 available only
> > for new entry.
> > Any chance?
> >
> >
> > "Beetle" wrote:
> >
> > > My impression based on your original post was that the purpose of
> > > Subform2 was to show (through two additional subforms) information
> > > that is related to each Account in Subform1. In a case like that, Subform2
> > > would not be linked to the Parent form via ClientID (in fact, Subform2 would
> > > likely not even have a ClientID field). It would be linked to Subform1 via
> > > AccountID (through the hidden text box).
> > >
> > > Is there some other information in Subform2 that relates directly to the
> > > Client (not their Accounts) which requires that you have Subform2 linked
> > > directly to the Parent form via ClientID?
> > > --
> > > _________
> > >
> > > Sean Bailey
> > >
> > >
> > > "Mishanya" wrote:
> > >
> > > > Hi
> > > > Subform2 (as well as Subform1) both are linked to the Parentform (by
> > > > ClientID). There is no AccountID in the Parentform underlying table. So the
> > > > program does not even suggest others then Parentform underlying table fields.
> > > > I did what You've advised (forcibly linked the Subform2 to the txtLinkBox
> > > > control) - it is working, but the whole Parentform gets into blinking mood
> > > > with "Calculation..." message in the bottom. I reckon, it allows the linking,
> > > > but does not like it for some reason.
> > > >
> > > >
> > > > "Beetle" wrote:
> > > >
> > > > > I'm not sure I completely understand your structure, so this may not
> > > > > be a workable solution for you, but you can give it a try. As always,
> > > > > make a backup first.
> > > > >
> > > > > Leave the Data Entry property of Subform2Sub2 set to Yes
> > > > >
> > > > > Get rid of the code behind the Click event of the AccountNumber
> > > > > control on Subform1
> > > > >
> > > > > Add an unbound textbox somewhere on your main form (i.e. txtLinkBox)
> > > > > Make it invisible and set it's control source to;
> > > > >
> > > > > =[Subform1].[Form]![AccountID]
> > > > >
> > > > > Open the properties sheet for the *subform control* of Subform2 (not
> > > > > the subform itself). You should see properties for Link Child Fields and
> > > > > Link Master Fields.
> > > > >
> > > > > Set Link Child Fields to: AccountID
> > > > > Set Link Master Fields to: txtLinkBox
> > > > >
> > > > > If I understand your setup correctly, this should automatically refresh
> > > > > the data in Subform2 when you select a different Account in Subform1
> > > > > without having to use a filter (which overrides the Data Entry property).
> > > > >
> > > > > --
> > > > > _________
> > > > >
> > > > > Sean Bailey
> > > > >
> > > > >
> > > > > "Mishanya" wrote:
> > > > >
> > > > > > I have rather complicated form-structure and want to accomplish rather
> > > > > > cumbersome action, but maybe it would look as fisible task to the forum
> > > > > > experts .
> > > > > >
> > > > > > I have ParentForm with unbound cboSelectClient. On it there are:
> > > > > > Subform1 (AccountsList) - datasheet list of client's accounts (each record
> > > > > > has a few controls with account-related data, one of wich is AccountNumber)
> > > > > > Subform2 (AccountPortfolio) with two Subforms:
> > > > > > 1) Subform2Sub1 - datasheet list of the securities and deposits the
> > > > > > Subform1' current account contains;
> > > > > > 2) Subform2Sub2 - Single-view form for entering buy/sell transactions for
> > > > > > the current account.
> > > > > >
> > > > > > When client-name is selected in the ParentForm cboSelectClient, both
> > > > > > Subforms 1 and 2 get criteria by:
> > > > > >
> > > > > > Dim rs As Object
> > > > > > Set rs = Me.Recordset.Clone
> > > > > > rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboSelectClient], 0))
> > > > > > If Not rs.EOF Then Me.Bookmark = rs.Bookmark
> > > > > >
> > > > > > so the Subform1 shows the selected client' accounts, Subform2Sub1 shows the
> > > > > > first-in-the-Subform1-list-account' portfolio and Subform2Sub2 shows the
> > > > > > first transaction (by TransactionID) performed for this account.
> > > > > >
> > > > > > The Subform1' AccountNumber control is programmed to change on-click the
> > > > > > Subform2' recordset with:
> > > > > >
> > > > > > Dim f As Form
> > > > > > Set f = Forms![ParentForm ]![Subform2].Form
> > > > > > f.Filter = "AccountID = " & Me!AccountID
> > > > > > f.FilterOn = True
> > > > > >
> > > > > > so the Subform2Sub-forms 1 and 2 show the related portfolio and first
> > > > > > transaction of the AccountNumber control clicked in the Subform1.
> > > > > >
> > > > > > I hope I explained the structure clearly.
> > > > > > Now, I want to always keep the Subform2Sub2 transaction form ready for a new
> > > > > > entry never showing any previous records. This is where the trouble begins. I
> > > > > > tried:
> > > > > > 1) to set its DataEntry property to Yes: when the Parentform'
> > > > > > cboSelectClient is selected, it still holds for a new entry, but once the
> > > > > > Subform1' AccountNumber control is clicked, it loads the first transactions
> > > > > > details.
> > > > > > 2) to set focus and move it to the new record from the AfterUpdate event of
> > > > > > the Parentform' cboSelectClient:
> > > > > > Me![Subform2].Form![Subform2Sub2].SetFocus
> > > > > > DoCmd.GoToRecord , , acNewRec
> > > > > > but for some reason this code sends the whole Parentform to the new record,
> > > > > > instead of dealing with just the Subform2Sub2 (this code works fine on
> > > > > > subform from the first degree but not in case of "grandchild" SubSubform).
> > > > > > 3) to add the same code in the OnClick event of Subform1' AccountNumber
> > > > > > control:
> > > > > > Forms![Parentform]![Subform2].Form![Subform2Sub2].SetFocus
> > > > > > DoCmd.GoToRecord , , acNewRec
> > > > > > but here I get total crash with run-time Error 2105 "You can't go to the
> > > > > > specified record" (the code passes the SetFocus successfully, though, but
> > > > > > crashes on the GoToRecord part).
> > > > > >
> > > > > > I can manage with putting in the Subform2Sub2 GoToNewRecord command button,
> > > > > > but it is not likable solution.
> > > > > >
> > > > > > If someone has patience to read all the above and manages to comprehend the
> > > > > > task, please help.
> > > > > >
> > > > > >
|
|
The Portfolio subform being a datasheet shouldn't be causing that
problem. The hidden text box method is a common approach when
it is necessary to display a heirarchal structure of continuous or
datasheet subforms. You might double check the fields in the
record source for the Portfolio subform (if it is based on a query
with calculated fields) or see if there might be any code that could
be causing the Portfolio subform to go into a requery loop or something.
These are just a couple of guesses on my part, it's hard to say for sure
what could be causing that problem.
--
_________
Sean Bailey
"Mishanya" wrote:
[Quoted Text] > Hi
> I've done exactly as You told - took the Subform2 subforms (portfolio and
> transactions) out, put them directly on the Parentform and linked them to the
> hidden box with AccountID mirrored from the Subform1 + erased the CLick event
> from the Subform1.
> The transaction subform keeps itself on new allright, but the portfolio
> subform does not like linking to the hidden box - it makes the whole
> structure go to loop (blinking+ "calculation...") although the data is
> presented allright. Maybe it has something to do with portfolio subform being
> datasheet and transaction subform being singleform - this kind of linking
> works for single but can not complete the loop for datasheet?
>
> "Beetle" wrote:
>
> > I guess you got it to work?
> > --
> > _________
> >
> > Sean Bailey
> >
> >
> > "Mishanya" wrote:
> >
> > > Hi Sean!
> > > I've just tried to incorporate the Subform2 into the Subform1 - and this
> > > reminded me of why I'd dropped this idea in the 1st place - I can't have
> > > subform planted into continuous or datasheet-view form! As I want to have all
> > > the accounts shown in the Mainform and also to be able to present its
> > > containing in the same Mainform, I put the accounts list and the [account'
> > > potfolio list + transaction form] in 2 different independant subforms linked
> > > to the Mainform by Client ID. So when the cboSelectClient is selected, the
> > > Subform1 loads the whole list of accounts, while the Subform2 loads the
> > > recordset related to the 1st account in the list (it does not have filter yet
> > > - this happens by default).
> > > Then I've managed to control the Subform2 from the Subform1 by passing
> > > filter on AccountID from one to another. Now I just need to make a
> > > "finishing" by making the transaction subform of the Subform2 available only
> > > for new entry.
> > > Any chance?
> > >
> > >
> > > "Beetle" wrote:
> > >
> > > > My impression based on your original post was that the purpose of
> > > > Subform2 was to show (through two additional subforms) information
> > > > that is related to each Account in Subform1. In a case like that, Subform2
> > > > would not be linked to the Parent form via ClientID (in fact, Subform2 would
> > > > likely not even have a ClientID field). It would be linked to Subform1 via
> > > > AccountID (through the hidden text box).
> > > >
> > > > Is there some other information in Subform2 that relates directly to the
> > > > Client (not their Accounts) which requires that you have Subform2 linked
> > > > directly to the Parent form via ClientID?
> > > > --
> > > > _________
> > > >
> > > > Sean Bailey
> > > >
> > > >
> > > > "Mishanya" wrote:
> > > >
> > > > > Hi
> > > > > Subform2 (as well as Subform1) both are linked to the Parentform (by
> > > > > ClientID). There is no AccountID in the Parentform underlying table. So the
> > > > > program does not even suggest others then Parentform underlying table fields.
> > > > > I did what You've advised (forcibly linked the Subform2 to the txtLinkBox
> > > > > control) - it is working, but the whole Parentform gets into blinking mood
> > > > > with "Calculation..." message in the bottom. I reckon, it allows the linking,
> > > > > but does not like it for some reason.
> > > > >
> > > > >
> > > > > "Beetle" wrote:
> > > > >
> > > > > > I'm not sure I completely understand your structure, so this may not
> > > > > > be a workable solution for you, but you can give it a try. As always,
> > > > > > make a backup first.
> > > > > >
> > > > > > Leave the Data Entry property of Subform2Sub2 set to Yes
> > > > > >
> > > > > > Get rid of the code behind the Click event of the AccountNumber
> > > > > > control on Subform1
> > > > > >
> > > > > > Add an unbound textbox somewhere on your main form (i.e. txtLinkBox)
> > > > > > Make it invisible and set it's control source to;
> > > > > >
> > > > > > =[Subform1].[Form]![AccountID]
> > > > > >
> > > > > > Open the properties sheet for the *subform control* of Subform2 (not
> > > > > > the subform itself). You should see properties for Link Child Fields and
> > > > > > Link Master Fields.
> > > > > >
> > > > > > Set Link Child Fields to: AccountID
> > > > > > Set Link Master Fields to: txtLinkBox
> > > > > >
> > > > > > If I understand your setup correctly, this should automatically refresh
> > > > > > the data in Subform2 when you select a different Account in Subform1
> > > > > > without having to use a filter (which overrides the Data Entry property).
> > > > > >
> > > > > > --
> > > > > > _________
> > > > > >
> > > > > > Sean Bailey
> > > > > >
> > > > > >
> > > > > > "Mishanya" wrote:
> > > > > >
> > > > > > > I have rather complicated form-structure and want to accomplish rather
> > > > > > > cumbersome action, but maybe it would look as fisible task to the forum
> > > > > > > experts .
> > > > > > >
> > > > > > > I have ParentForm with unbound cboSelectClient. On it there are:
> > > > > > > Subform1 (AccountsList) - datasheet list of client's accounts (each record
> > > > > > > has a few controls with account-related data, one of wich is AccountNumber)
> > > > > > > Subform2 (AccountPortfolio) with two Subforms:
> > > > > > > 1) Subform2Sub1 - datasheet list of the securities and deposits the
> > > > > > > Subform1' current account contains;
> > > > > > > 2) Subform2Sub2 - Single-view form for entering buy/sell transactions for
> > > > > > > the current account.
> > > > > > >
> > > > > > > When client-name is selected in the ParentForm cboSelectClient, both
> > > > > > > Subforms 1 and 2 get criteria by:
> > > > > > >
> > > > > > > Dim rs As Object
> > > > > > > Set rs = Me.Recordset.Clone
> > > > > > > rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboSelectClient], 0))
> > > > > > > If Not rs.EOF Then Me.Bookmark = rs.Bookmark
> > > > > > >
> > > > > > > so the Subform1 shows the selected client' accounts, Subform2Sub1 shows the
> > > > > > > first-in-the-Subform1-list-account' portfolio and Subform2Sub2 shows the
> > > > > > > first transaction (by TransactionID) performed for this account.
> > > > > > >
> > > > > > > The Subform1' AccountNumber control is programmed to change on-click the
> > > > > > > Subform2' recordset with:
> > > > > > >
> > > > > > > Dim f As Form
> > > > > > > Set f = Forms![ParentForm ]![Subform2].Form
> > > > > > > f.Filter = "AccountID = " & Me!AccountID
> > > > > > > f.FilterOn = True
> > > > > > >
> > > > > > > so the Subform2Sub-forms 1 and 2 show the related portfolio and first
> > > > > > > transaction of the AccountNumber control clicked in the Subform1.
> > > > > > >
> > > > > > > I hope I explained the structure clearly.
> > > > > > > Now, I want to always keep the Subform2Sub2 transaction form ready for a new
> > > > > > > entry never showing any previous records. This is where the trouble begins. I
> > > > > > > tried:
> > > > > > > 1) to set its DataEntry property to Yes: when the Parentform'
> > > > > > > cboSelectClient is selected, it still holds for a new entry, but once the
> > > > > > > Subform1' AccountNumber control is clicked, it loads the first transactions
> > > > > > > details.
> > > > > > > 2) to set focus and move it to the new record from the AfterUpdate event of
> > > > > > > the Parentform' cboSelectClient:
> > > > > > > Me![Subform2].Form![Subform2Sub2].SetFocus
> > > > > > > DoCmd.GoToRecord , , acNewRec
> > > > > > > but for some reason this code sends the whole Parentform to the new record,
> > > > > > > instead of dealing with just the Subform2Sub2 (this code works fine on
> > > > > > > subform from the first degree but not in case of "grandchild" SubSubform).
> > > > > > > 3) to add the same code in the OnClick event of Subform1' AccountNumber
> > > > > > > control:
> > > > > > > Forms![Parentform]![Subform2].Form![Subform2Sub2].SetFocus
> > > > > > > DoCmd.GoToRecord , , acNewRec
> > > > > > > but here I get total crash with run-time Error 2105 "You can't go to the
> > > > > > > specified record" (the code passes the SetFocus successfully, though, but
> > > > > > > crashes on the GoToRecord part).
> > > > > > >
> > > > > > > I can manage with putting in the Subform2Sub2 GoToNewRecord command button,
> > > > > > > but it is not likable solution.
> > > > > > >
> > > > > > > If someone has patience to read all the above and manages to comprehend the
> > > > > > > task, please help.
> > > > > > >
> > > > > > >
|
|
I guess I'm trying too much for my level. There must be some obstacle, but I
can't find it.
Anyway - thanks for the method - I'll definitely use in the future.
"Beetle" wrote:
[Quoted Text] > The Portfolio subform being a datasheet shouldn't be causing that
> problem. The hidden text box method is a common approach when
> it is necessary to display a heirarchal structure of continuous or
> datasheet subforms. You might double check the fields in the
> record source for the Portfolio subform (if it is based on a query
> with calculated fields) or see if there might be any code that could
> be causing the Portfolio subform to go into a requery loop or something.
>
> These are just a couple of guesses on my part, it's hard to say for sure
> what could be causing that problem.
>
> --
> _________
>
> Sean Bailey
>
>
> "Mishanya" wrote:
>
> > Hi
> > I've done exactly as You told - took the Subform2 subforms (portfolio and
> > transactions) out, put them directly on the Parentform and linked them to the
> > hidden box with AccountID mirrored from the Subform1 + erased the CLick event
> > from the Subform1.
> > The transaction subform keeps itself on new allright, but the portfolio
> > subform does not like linking to the hidden box - it makes the whole
> > structure go to loop (blinking+ "calculation...") although the data is
> > presented allright. Maybe it has something to do with portfolio subform being
> > datasheet and transaction subform being singleform - this kind of linking
> > works for single but can not complete the loop for datasheet?
> >
> > "Beetle" wrote:
> >
> > > I guess you got it to work?
> > > --
> > > _________
> > >
> > > Sean Bailey
> > >
> > >
> > > "Mishanya" wrote:
> > >
> > > > Hi Sean!
> > > > I've just tried to incorporate the Subform2 into the Subform1 - and this
> > > > reminded me of why I'd dropped this idea in the 1st place - I can't have
> > > > subform planted into continuous or datasheet-view form! As I want to have all
> > > > the accounts shown in the Mainform and also to be able to present its
> > > > containing in the same Mainform, I put the accounts list and the [account'
> > > > potfolio list + transaction form] in 2 different independant subforms linked
> > > > to the Mainform by Client ID. So when the cboSelectClient is selected, the
> > > > Subform1 loads the whole list of accounts, while the Subform2 loads the
> > > > recordset related to the 1st account in the list (it does not have filter yet
> > > > - this happens by default).
> > > > Then I've managed to control the Subform2 from the Subform1 by passing
> > > > filter on AccountID from one to another. Now I just need to make a
> > > > "finishing" by making the transaction subform of the Subform2 available only
> > > > for new entry.
> > > > Any chance?
> > > >
> > > >
> > > > "Beetle" wrote:
> > > >
> > > > > My impression based on your original post was that the purpose of
> > > > > Subform2 was to show (through two additional subforms) information
> > > > > that is related to each Account in Subform1. In a case like that, Subform2
> > > > > would not be linked to the Parent form via ClientID (in fact, Subform2 would
> > > > > likely not even have a ClientID field). It would be linked to Subform1 via
> > > > > AccountID (through the hidden text box).
> > > > >
> > > > > Is there some other information in Subform2 that relates directly to the
> > > > > Client (not their Accounts) which requires that you have Subform2 linked
> > > > > directly to the Parent form via ClientID?
> > > > > --
> > > > > _________
> > > > >
> > > > > Sean Bailey
> > > > >
> > > > >
> > > > > "Mishanya" wrote:
> > > > >
> > > > > > Hi
> > > > > > Subform2 (as well as Subform1) both are linked to the Parentform (by
> > > > > > ClientID). There is no AccountID in the Parentform underlying table. So the
> > > > > > program does not even suggest others then Parentform underlying table fields.
> > > > > > I did what You've advised (forcibly linked the Subform2 to the txtLinkBox
> > > > > > control) - it is working, but the whole Parentform gets into blinking mood
> > > > > > with "Calculation..." message in the bottom. I reckon, it allows the linking,
> > > > > > but does not like it for some reason.
> > > > > >
> > > > > >
> > > > > > "Beetle" wrote:
> > > > > >
> > > > > > > I'm not sure I completely understand your structure, so this may not
> > > > > > > be a workable solution for you, but you can give it a try. As always,
> > > > > > > make a backup first.
> > > > > > >
> > > > > > > Leave the Data Entry property of Subform2Sub2 set to Yes
> > > > > > >
> > > > > > > Get rid of the code behind the Click event of the AccountNumber
> > > > > > > control on Subform1
> > > > > > >
> > > > > > > Add an unbound textbox somewhere on your main form (i.e. txtLinkBox)
> > > > > > > Make it invisible and set it's control source to;
> > > > > > >
> > > > > > > =[Subform1].[Form]![AccountID]
> > > > > > >
> > > > > > > Open the properties sheet for the *subform control* of Subform2 (not
> > > > > > > the subform itself). You should see properties for Link Child Fields and
> > > > > > > Link Master Fields.
> > > > > > >
> > > > > > > Set Link Child Fields to: AccountID
> > > > > > > Set Link Master Fields to: txtLinkBox
> > > > > > >
> > > > > > > If I understand your setup correctly, this should automatically refresh
> > > > > > > the data in Subform2 when you select a different Account in Subform1
> > > > > > > without having to use a filter (which overrides the Data Entry property).
> > > > > > >
> > > > > > > --
> > > > > > > _________
> > > > > > >
> > > > > > > Sean Bailey
> > > > > > >
> > > > > > >
> > > > > > > "Mishanya" wrote:
> > > > > > >
> > > > > > > > I have rather complicated form-structure and want to accomplish rather
> > > > > > > > cumbersome action, but maybe it would look as fisible task to the forum
> > > > > > > > experts .
> > > > > > > >
> > > > > > > > I have ParentForm with unbound cboSelectClient. On it there are:
> > > > > > > > Subform1 (AccountsList) - datasheet list of client's accounts (each record
> > > > > > > > has a few controls with account-related data, one of wich is AccountNumber)
> > > > > > > > Subform2 (AccountPortfolio) with two Subforms:
> > > > > > > > 1) Subform2Sub1 - datasheet list of the securities and deposits the
> > > > > > > > Subform1' current account contains;
> > > > > > > > 2) Subform2Sub2 - Single-view form for entering buy/sell transactions for
> > > > > > > > the current account.
> > > > > > > >
> > > > > > > > When client-name is selected in the ParentForm cboSelectClient, both
> > > > > > > > Subforms 1 and 2 get criteria by:
> > > > > > > >
> > > > > > > > Dim rs As Object
> > > > > > > > Set rs = Me.Recordset.Clone
> > > > > > > > rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboSelectClient], 0))
> > > > > > > > If Not rs.EOF Then Me.Bookmark = rs.Bookmark
> > > > > > > >
> > > > > > > > so the Subform1 shows the selected client' accounts, Subform2Sub1 shows the
> > > > > > > > first-in-the-Subform1-list-account' portfolio and Subform2Sub2 shows the
> > > > > > > > first transaction (by TransactionID) performed for this account.
> > > > > > > >
> > > > > > > > The Subform1' AccountNumber control is programmed to change on-click the
> > > > > > > > Subform2' recordset with:
> > > > > > > >
> > > > > > > > Dim f As Form
> > > > > > > > Set f = Forms![ParentForm ]![Subform2].Form
> > > > > > > > f.Filter = "AccountID = " & Me!AccountID
> > > > > > > > f.FilterOn = True
> > > > > > > >
> > > > > > > > so the Subform2Sub-forms 1 and 2 show the related portfolio and first
> > > > > > > > transaction of the AccountNumber control clicked in the Subform1.
> > > > > > > >
> > > > > > > > I hope I explained the structure clearly.
> > > > > > > > Now, I want to always keep the Subform2Sub2 transaction form ready for a new
> > > > > > > > entry never showing any previous records. This is where the trouble begins. I
> > > > > > > > tried:
> > > > > > > > 1) to set its DataEntry property to Yes: when the Parentform'
> > > > > > > > cboSelectClient is selected, it still holds for a new entry, but once the
> > > > > > > > Subform1' AccountNumber control is clicked, it loads the first transactions
> > > > > > > > details.
> > > > > > > > 2) to set focus and move it to the new record from the AfterUpdate event of
> > > > > > > > the Parentform' cboSelectClient:
> > > > > > > > Me![Subform2].Form![Subform2Sub2].SetFocus
> > > > > > > > DoCmd.GoToRecord , , acNewRec
> > > > > > > > but for some reason this code sends the whole Parentform to the new record,
> > > > > > > > instead of dealing with just the Subform2Sub2 (this code works fine on
> > > > > > > > subform from the first degree but not in case of "grandchild" SubSubform).
> > > > > > > > 3) to add the same code in the OnClick event of Subform1' AccountNumber
> > > > > > > > control:
> > > > > > > > Forms![Parentform]![Subform2].Form![Subform2Sub2].SetFocus
> > > > > > > > DoCmd.GoToRecord , , acNewRec
> > > > > > > > but here I get total crash with run-time Error 2105 "You can't go to the
> > > > > > > > specified record" (the code passes the SetFocus successfully, though, but
> > > > > > > > crashes on the GoToRecord part).
> > > > > > > >
> > > > > > > > I can manage with putting in the Subform2Sub2 GoToNewRecord command button,
> > > > > > > > but it is not likable solution.
> > > > > > > >
> > > > > > > > If someone has patience to read all the above and manages to comprehend the
> > > > > > > > task, please help.
> > > > > > > >
> > > > > > > >
|
|
|