RE: Keeping SubSubform on New entry

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.


.

Relevant Pages

  • RE: Keeping SubSubform on New entry
    ... 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 ... Then I've managed to control the Subform2 from the Subform1 by passing ... There is no AccountID in the Parentform underlying table. ...
    (microsoft.public.access.formscoding)
  • RE: Keeping SubSubform on New entry
    ... Subform2 ... There is no AccountID in the Parentform underlying table. ... control) - it is working, but the whole Parentform gets into blinking mood ... Get rid of the code behind the Click event of the AccountNumber ...
    (microsoft.public.access.formscoding)
  • RE: Keeping SubSubform on New entry
    ... Do you suggest to reconstruct it and plant Subform2 with its subforms into ... There is no AccountID in the Parentform underlying table. ... control) - it is working, but the whole Parentform gets into blinking mood ... Get rid of the code behind the Click event of the AccountNumber ...
    (microsoft.public.access.formscoding)
  • subform getting data from other subform
    ... this table is an account number, ... don't want the user to have to enter the account number in Subform2, ... it should pull from Subform1. ... How can I accomplish this? ...
    (comp.databases.ms-access)
  • RE: Keeping SubSubform on New entry
    ... So each had ClientID and was to be opened by ckicking buttons of some main ... Do you suggest to reconstruct it and plant Subform2 with its subforms into ... There is no AccountID in the Parentform underlying table. ... Get rid of the code behind the Click event of the AccountNumber ...
    (microsoft.public.access.formscoding)