how to update subforms using child/master fields

[Insomnia]

Hi guys,

I have a form containing two subforms, both with a childfield related to a filtered masterfiield.

I filter by choosing a value in a cbo, and setting the filter to this field.
when running the subforms outside this main form they work just fine, but in the main form they remain empty. Why is that?


I wil now provide you with the used code:

-------filter in main form ------------------------------
Me.FilterOn = False
Dim strFilter As String
Dim tmp As String

'###### sets the filter String to the choosen organization, "europe" ##
Me.cboOrgSelect.SetFocus
strFilter = Me.cboOrgSelect.Column(0)

'###### sets formfilter to the choosen Organization ######'
Me.Filter = "Organization = '" & strFilter & "'"
Me.FilterOn = True
------- requerying subforms ---------------------------

Me.subform1.Requery
Me.subform2.Requery


I understand that I might need to update/requery the actual source objects of thes subforms, but how is that done, and is it the answer to my worries?

thank yuo for your time, Insomnia

 

[Insomnia]

still doesn't work m8,

if I remove the child/master link they all show. the child/master link is on a txt field, and the text in them are copies from a third tbl so they should mach just fine... are there any newbie errors you could think off?

 

[Insomnia]

I might not need to filter, since I can delete all the records previous used by the user in this tmp table. But when run the form like that the datasheet records switches to "deleted"!!! does that have anything to do with not having selected a master/child link?

 

[accessimpaired]

Same problem

I am having the exact same problem wished I could help
accesimpaired

 

[Insomnia]

please please do if you find a way to solve this, please please please!

 

[accessimpaired]

I will.. I am working on it as we speak.. and have been for 3 weeks now.. I can get data in data sheet view but not excalty what I need. so I wil keep plugging a way and will let you know and I hope you will do the same
accessimpaired

 

[Insomnia]

What I want is the chosen item in the main form cbo to determine what records to show in the subs. What solution I use doesn't mather aslong as it works, which so far has not been the case. I have other main sub forms working just fine, but this one differs in the way that it contains a datasheetform, and I have so far not been able to requery those DS forms, e.g., the info I've found so far tells me that I should refer to the subform name in the main form, which is not the same thing?

I've tried the set row source and requery thing, without any success, I am as desperate as one can get my app workin, so if any other ideas turn up please let me know.

 

[WayneRyan]

Insomnia,

You need to base the subform on a query. The query uses the primary key
on the main form, as well as the combo-box as its criteria.

Post a sample if you can,
Wayne

 

[Insomnia]

ok,

How do I refer to a cbo in a query? I have tried
"[Forms]![Mainform]![cboOrgSelect].[BoundColumn]" and
"[Forms]![Mainform]![cboOrgSelect]",

which the query doesn't accept, even when I run it by it self (when the app is running).

and this is how I "requery" the subs
Me!Sub1(mainformsubname).Form.RecordSource = "qry1"
Me.Form.Refresh
Me!Sub1.Requery

Since that didn't work I tried copying the cbo orgvalue to a txt, which I refered to in the query "where" statement. this gave me a strange behaviour, described in the thread "subforms update sporadically", but back then I also did also reset the source objects of sub1 and sub2.

Have tried the txt solution again, with fixed sub source objects. this made the query work but the subs remain empty.

-the latest status: the forms do update, just not every time.....

 

[Insomnia]

Here is a sample of the form, and info of how to run it, please take a look at it, maybe you can end this nightmare?

/Insomnia

 

[WayneRyan]

Insomnia,

I'm having a hard time running this. I don't have a "c:\test1.mdb".

You actually have only a main form here. You don't need a subform.

1) Anyway, take out the Master-Child links.

2) Use the AfterUpdate event of the main form's combo to put Me.AceSFTNo.Requery

3) Change the query for the subform to put the main form's combo as
criteria for the organization's field.

4) Set the Default value for organization to the combo on the main form.

OR, even easier ...

Use the subform as a main form, and add the organization combo as an
"unbound" search tool.

Wayne

 

[Insomnia]

thanks m8, I will try out your first suggestion, but since I need to display more than one sub at the same time I cant change them into a main form.

about your mdb problem, did you change the DB Connstring path as I described in the read me file?

 

[Insomnia]

Hey dude!

I tried your suggestions, didn't help, why I reviewed the design, and decided to move the delete/create part from the main form to the form actually opening it. it did the trick!

it seems to me like access doesn't sync properly when doing all these things on after another.