Problem linking subforms with parentform........ 1

[GWhiz]

Hi, folks!

I have a subform set up to to display a datasheet view of its parent form.

On the form which contains the subform, I have some option buttons which are intended to change the way the subform displays the records.

Problem: option buttons change the display of the PARENT (sourceobject to the subform) form as intended -- but does NOT change the display of the subform.

Have tried subform.Requery, or reset subform.SourceObject to try to get the subform to refresh so that the displayed subform matches its sourceobject -- but cannot get subform to follow.

Any suggestions?

Thanks SOOOOOO much.....you people have been VERY helpful!!!

Respectfully,

drs84@hotmail.com [sig][/sig]

 

[Robertd]

Hi GWhiz,

I'm not exactly sure what the option buttons do, have you tried the refresh method on the subform?
or to change the range of records to be displayed perhaps you might be better off setting the subform to a seperate query with the criteria based on the options, that way the .requery should yeild the changes to the subform data.

HTH
Robert [sig][/sig]

 

[GWhiz]

Hi, Robert!

Thanks for your response.

Option buttons on my form choose different sorts for the information being presented.

Buttons work great in causing the Source Object for my subform to re-sort......but subform refuses to display the different sorts. Have tried .Refresh, .Requery....can't get subform to display new selected sort.

It's gotta be a simple solution.....they usually are. But I seem to be too dense to figure it out.

Any other suggestions?

Thanks!!!

Respectfully,

Doug [sig][/sig]

 

[Robertd]

Hi Doug,

is the main form and the subform based on a query type dynaset and is it the same for both main and subform?

Also how do the buttons work do they apply a filter? if they do you might find that it will only effect the main forms data source that is the underlying record source will be un-changed by applying a filter.

HTH
Robert [sig][/sig]

 

[GWhiz]

Hi, Robert!

Just so I can be sure we're talking about the same thing here, we're dealing with 3 forms, as I understand it:

1) a PARENT form which is the underlying record source for

2) a SUBFORM which resides on

3) a MAIN form which contains the subform

The RecordSource for the MAIN form is not really applicable, since all the work (i.e., editing/displaying records) is done on the SUBFORM. The MAIN form simply acts as a form to put controls on, and allows me to control the physical appearance of the datasheet SUBFORM. The only data being displayed is in the SUBFORM. The controls consist of option (toggle) buttons which select different SORT modes for the SUBFORM which is displayed only in datasheet mode. The option buttons set the OrderBy property for the PARENT form.

The option buttons DO change the PARENT form -- that is, the PARENT form DOES re-sort in accordance with the selected option button. But the SUBFORM whose SourceObject is that PARENT form does NOT show the same re-sort as the PARENT form, in spite of running .Requery to the SUBFORM (.Refresh is not supported on a subform).

If an option button changes a sort order for a PARENT form which is the SourceObject for a SUBFORM, why doesn't the SUBFORM based on that PARENT form also show the re-sort (especially after a Requery of the SUBFORM)?

My question reveals some misunderstanding of exactly how PARENT and SUBFORMS are related. In spite of hours searching through HELP on this, I still can't seem to get a clear understanding what's happening here.

Any ideas? I’m flummoxed!!! Bamboozled!!!

Thanks!!! [sig][/sig]

 

[Robertd]

Hi Doug,

the parent property refers to well a property or field or object on the main form from the subforms point of view. like if on a subform you executed some code and want to know a property of a control or field value you could use

TempTransID = Me.Parent.Form.TransID

for the sub form the main form is the parent form
as opposed to a fully qualified call

TempTransID = Forms![TransDisectMain]![TransID]

both return the same data item
you could try to refresh the subform using this

Me![TransDisectSub].Form.Requery
Me![TransDisectSub].Form.Refresh

this is called from the main form after the records are commited and causes the subform data to be redisplayed in the subforms datasource queries sort order as opposed to the order that the operator entered them.

the reason i mentioned filters is that they don't affect the underlying record set order and what records are in the record set source, only what records are visible, the subset of records there like a query on the record source.
if your main form is based on a table or a select query as as opposed to the subforms query record source they are two different record sources

Also what exactly is the record set source for the subform
is it a dynaset record set, like what is in the datasource property on the subform under form->data->record source, does it point to an external query or is it a select statement?

SELECT LedgerTransactions.BankAcctID, LedgerTransactions.BankRef, LedgerTransactions.TransDate, LedgerTransactions.TransRef FROM LedgerTransactions;

or simply table "name"
(not an external query like in the query tab) thats the lot of it!

sorry after this looong rave i think i found something this seems to work!
are you appling the orderby to the subform?? ;-)

Me.TransDisectSub.Form.OrderBy = "[TransAmnt] desc"
Me![TransDisectSub].Form.Requery
Me![TransDisectSub].Form.Refresh

HTH
Robert [sig][/sig]

 

[Robertd]

PS Doug, the names of the forms, data items etc. have been changed to protect the guilty,...bugger it's late X-)
you will have to change to suit

see ya
Robert [sig][/sig]

 

[GWhiz]

Hi, Robert!

This is driving me BATTY!!! In some ways, I LOVE Access because it is SO powerful. But in other ways, I HATE it, because it’s VERY non-intuitive in several areas.

Let’s see – I have the SourceObject property of my subform set to the name of the form which supplies records to the subform. The RecordSource for the form (which is the basis of the subform) is a table (not a query).

That is – if FormB.SourceObject = FormA, and FormA.RecordSource = MySQLStatement – then wouldn’t the recordsource for FormB = MySQLStatement as well?

When the user presses one of the sort options (toggle buttons), it re-sets the RecordSource for the parent of the subform to a SQL statement based on that table (instead of the table itself).

This re-sorts the underlying form just fine – but somehow does not make it to the subform, even after a SubForm.Requery and Subform.Refresh.

Have tried

Me.MySubForm.Form.OrderBy = OrderByStr (as selected by the forementioned toggle buttons)
Me![MySubForm].Form.Requery
Me![MySubFomr].Form.Refresh

as you suggested. No error message, but no results, either.

You must be right that the subform and its underlying sourceobject MUST be getting records from two different sources – but I don’t see how that is happening. If underlying form sorts as selected, and subform.SourceObject = underlying form, then it seems that the subform should be forced to display same sort as its SourceObject.

But that’s too simple, isn’t it?

Any other possibilities?

Thanks!!!

Doug
[sig][/sig]

 

[Robertd]

Hi Doug,

I aggree with your comments the bad points make me think that c++ and classes have a lot going for them ;-)
also notice how it's also not consistent,..

but that means i would have to write the database management myself,... or find more about builder

Ok a silly question the subform is enabled?

the code methods worked on a form in my current db develepment, only thing i could think of is try seting the sub forms record set to a query (as is the subform i tried these methods on)

also
you have a form(0) which has a mainform(1) embeded on it and a subform(2) which is embeded on the the mainform(1)
is this correct??
if so doesn't this make the main form a subform??
and subform(2) is a sub subform.

there was also another posting along similar lines where it was serguested to set focus on the "form" in question and apply the .requery and .refresh methods,.. maybe or fully qualify the reference and apply the methods

although from what you describe i would have thought that both form baased on the same rst would yeild the same results

HTH
Robert




[sig][/sig]

 

[Robertd]

Hi Doug

Another Thought (yes i do get them from time to time) i was just stuffing around and to have the orderby = "x" you need to also set the OrderByOn property to true! then it works.

i was toying with using this for a cheque (check) reconciliation form to alow the user to re-order the listed cheques, and it didn't work ;-(
more reading and doh,...

Private Sub Label1_Click()
Me.OrderByOn = True
Me.OrderBy = "[TradePrice] Desc"
'Me.Requery
'Me.Refresh
MsgBox "Me.OrderBy = TradePrice Desc " &
OrderBy & " Me.OrderByOn = " & Me.OrderByOn
End Sub

also this is in the column heading click event, i found i didn't need the .refresh and .requery

HTH
Robert [sig][/sig]

 

[GWhiz]

Yo, Robert!!!

Finally!!! And THANK YOU!!!

Turns out that this suggestion by you

Me.Subform.Form.OrderBy = OrderByString
Me.Subform.Form.OrderByOn = True

is the one that works.

I didn't get the syntax exactly right first time through. No error messages, but no results. So I played around with it just a little more and BINGO!!!

Sheesh!!! Arrrrrrgh!!! Simple, as predicted.

REALLY appreciate your help!!! May life bring you an extra dose of happiness soon!!!

Respectfully,

Doug [sig][/sig]

 

[Robertd]

Hi Doug,

your welcome,

Hi have this saying
"It's always the small things that get you!"

based on the premise that large complicated issues always keep us aware and respectful, the simple things tend make us complacent, especialy wrt to code ;-)

see ya
Robert
[sig][/sig]

 

[Culby]

Doug and Robert:

I'm having a similar problem but I don't understand all the stuff you guys were talking about. Any help would be appreciated greatly!

Here is the goal: I have created a form to display data (photos, and long comments) from a table. I have created a subform based on a query that is based on the same table to display the other data in a data sheet view looking similar to a spread sheet.

My problem is I want to display the correct record on the main form when a click on a record on the subform. Perhaps this is not the best way to set this up. I'm open to any suggestions.

Thanks,
Culby