One subform interfering with another

[Jonathan Blitz]

I have a form in datasheet view.
In it I have a subform (datashet or single form view).
When I open up the subform (clicking on the '+') I find that the instances
of the subform seem to be connected.
If I have an event for one of the instances (for example, FormOpen) and I
perform some action it affects all the subforms.

Why is this?

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."

 

[Gary Miller]

Jonathan,

Not sure about the other problems, but FormOpen applies to
the form or subform as a whole, not to the individual
records on the form. That means that FormOpen will fire when
the subform first loads only. For what you are talking about
here you may try the OnCurrent event as that fires every
time you switch to a new subform record. They are not
actually all different subforms, rather different records on
the same one.

Gary Miller
Sisters, OR

 

[Alick [MSFT]]

Hi Jonathan,

Not sure the question is, however, it seems you can try current event of
the subform. The Current event occurs when the focus moves to a record,
making it the current record, or when the form is refreshed or requeried

BTW: what action did you take that affects all the subforms?

Please feel free to reply to the threads if you have any concerns or
questions.



Sincerely,

Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: "Jonathan Blitz" <[email protected]>
| Subject: One subform interfering with another
|
| I have a form in datasheet view.
| In it I have a subform (datashet or single form view).
| When I open up the subform (clicking on the '+') I find that the instances
| of the subform seem to be connected.
| If I have an event for one of the instances (for example, FormOpen) and I
| perform some action it affects all the subforms.
|
| Why is this?
|
| --
| Jonathan Blitz
| AnyKey Limited
| Israel
|
| "When things seem bad
| Don't worry and shout
| Just count up the times
| Things have worked themselves out."
|
|
|

 

[Jonathan Blitz]

What I am trying to do is to dynamically change the subform data source.

This is because the source is an SQL statement from an SQL server. It must
use values from a parent where this field is not in the connecting fields
between the levels.

For example, The main and subform are connect via the field PlanId.
However, I need to limit the rows also using another value in the main form
(Month).

So, I change the SQL to read: Select * from table Where PlanId = value and
Month = value

where the values of PlanId and Month I take from the parent.PlanId and
parent.Month fields.

What seems to happen is that when I change the data source for the subform
of one of the entries in the main form it changes the values for ALL of the
subforms.

Maybe I am just going about it the wrong way.

I hope I have explained it better this time.

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."

 

[Gary Miller]

Jonathan,

You say...

For example, The main and subform are connect via the field PlanId.
However, I need to limit the rows also using another value in the main form
(Month).

Are you aware that you can link the parent and subform to
more than one field? If you go to the properties of the
subform control on the main form and click on the grey on
the right side of the Link Child Fields you will get a
builder box that will let you select your Month fields as
well. If you do this now you will have the dual link that it
sounds like you are trying to achieve.

Gary Miller
Sisters, OR

 

[Jonathan Blitz]

Problem is that it is actually 3 levels deep.
In additition there are 2 extra search fields that I need.

If I add these fields to the intermediary forms and make them hidden will
the values then be passed on and used automatically in the subform?
In other words, if I add month to the second form and connect it to the
first and then do the same for the third to the second will it then
automatically add it to the WHERE clause when selecting the rows from SQL?
The Month field is part of the table that is used to populate the inner
form.
Will it also use these values when I insert a new row?

If so, then this is great!

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."

 

[Gary Miller]

Is this three levels of forms? Should be able to work it
out. Not quite sure what you mean about the 2 extra search
fields.

Here are some of the guidelines:

You can link more than one field per subform. In the
Master/Child link properties use syntax like this...

PlanID; Month; ThirdLinkField
PlanID; Month; ThirdLinkField

.... making sure that they are in the same order on both
lines.

All the linking fields DO have to be in the recordsources of
both forms, but do NOT have to actually be on the form
itself, so it looks like you don't have to hide any
controls.

Any linking fields WILL automatically be inserted into the
record of the child form.

In short, if you link all your criteria you don't have to
worry about any WHERE clause, just base the form on the
appropriate recordset or view from SQL Server.

Does it look like this will fit your situation?

Gary Miller

 

[Jonathan Blitz]

Problem is that the second level form does not include the extra search
field within the data source.
On the bottom level one includes it.

That's why I set the Data Source dynamically.

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."

 

[Gary Miller]

Can't you add it? How are you doing it dynamically if it is
not there?

Gary

 

[Jonathan Blitz]

The field we are dealing with is a month field.
It is in the table that is used in the lowest level form but not in any of
the others.
However, I only want to display the lines for a specific month.
So I set a month field at the top level and I use it in the Where clause at
the bottom level by accessing using parent.parent.month.
To do this I have to change the Data Source for the form.

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."

 

[Gary Miller]

Went back and reviewed all the thread to date. Is this an
adp project connected to SQL Server? If so, it complicates
things as in an mdb you could just reference the Month field
on the form in a query.

Do you manually type this Month in or select it from a
combobox? I wonder what would happen if you did something
like this in the AfterUpdate of that control...(I am
assuming that Month is a string)

Dim strSQL as String
strSQL = "SELECT * FROM vw_YourSQLServerView WHERE Month =
'" & Me!Month & "'"
Me!1stSubFrmControlName.Form!2ndSubFormControlName.Form.Reco
rdSource = strSQL

 

[Jonathan Blitz]

I'll give it a try and see what happens.

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."

Went back and reviewed all the thread to date. Is this an
adp project connected to SQL Server? If so, it complicates
things as in an mdb you could just reference the Month field
on the form in a query.

Do you manually type this Month in or select it from a
combobox? I wonder what would happen if you did something
like this in the AfterUpdate of that control...(I am
assuming that Month is a string)

Dim strSQL as String
strSQL = "SELECT * FROM vw_YourSQLServerView WHERE Month =
'" & Me!Month & "'"
Me!1stSubFrmControlName.Form!2ndSubFormControlName.Form.Reco
rdSource = strSQL