Automatically Refreshing Subforms

[klfduncan]

Hi there,

I know it's not a very original topic, but subforms are getting me down. I've searched through previous topics and can't find anything to match my query (is that a reserved word?)

I have a form "ActionDB" which contains two subforms:

1. ActionIn
2. ActionHist

ActionIn is a single form used to input new Actions (seven fields). Action History is a continuous form which shows all of the actions which have been inputted into ActionIn.
Both of these forms are successfully linked to ActionDB using the parent-child relationship and I have no troubles with the tables and relationships (all records are stored in Table ActionTab).

Now my question is purely aesthetic, but when I've inputted an Action into subform1 I want subform2 to automatically Requery. But it doesn't I've tried everything I can think of and what's annoying is that if I click on the Parent form (ActionDB) and press Shift+F9 it updates it; if I click on subform2 and press Shift+F9 it updates it; if I click on subform1 and press Shift+F9 nothing happens (nothing should, so I'm quite happy with that) but I can't for the life of me find anyway to do it automatically. I've spent the last day on this, working with macros and specifying recordsets and programming in VB and nothing seems to work.

Please help as I'm tearing out what meagre reserves of hair are left on my head.

Thanks,

KLF

 

[willir]

klfduncan

Try using...

[COLOR=blue]
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70[/color]
Me.YourSubformName.Requery

To do this the "simple" way, add a command button. Use wizard - "Forms" category -> "Refresh from Data".

This will create the first line of code (in blue)

Add the Requery line afterwards as depicted.

Want to know how to edit the code?

Have the form open in design mode. Have the "Properties" window open (from the menu, "View" -> "Properties"). Select the command button. Click on the "Event" tab on the properties window. You will see an "Event Procedure" created for the OnClick event. Select the field - you will see a small command button appear on the right, "...". Click on this command button and the system will open up the VBA coding editor.

Richard

 

[TheAceMan1]

How are ya klfduncan . . . . .

[blue]when I've inputted an Action into subform1 [purple]I want subform2 to automatically Requery[/purple].[/blue]

Since you want to requery on all data entry of subForm1, in the [blue]AfterUpdate Event[/blue] of each data input control, put the following code:

[blue]Me![purple][b]subForm2Name[/b][/purple].Form.Requery[/blue]

See Ya! . . . . . .

 

[klfduncan]

OK, I've inputted:

Me.ActionHistory.Form.Requery

and I get the old "Microsoft can't find the field '(vertical line)' in your expression. Same result for

Me.ActionHistory!Form.Requery

So then I inputted

Me!ActionHistory.Form.Requery

and I get "Microsoft can't find the field "ActionHistory" referred to in your expression.

I think the problem may be that even though the form contains data, Access doesn't recognise that it's open because when I try to select it, it says that it's not open. Any ideas?

I really appreciate you guys taking the time to help out by the way

 

[willir]

klfduncan

As per my post, try...
Me.ActionHistory.Requery

Not,
Me.ActionHistory.Form.Requery

If this does not work, this means the data from the main form can not be accessed from the table.

...Add the Refresh method, then Requery.

Richard

 

[TheAceMan1]

[blue]even though the form contains data, Access doesn't recognise that it's open because when I try to select it, [purple]it says that it's not open[/purple].[/blue]

Wow . . . . are you seeing data in ActionHistory when you try to select it?
Are you sure your [blue]Relationships[/blue] are correct?
Are the forms linked with [blue]Master/Child[/blue] properties?

Also, in your post origination you said:

[blue]I have a form "ActionDB" which contains two subforms:
1. ActionIn
2. [purple]ActionHist[/purple][/blue]

Then in your code you put:

[blue]Me.[purple][b]ActionHistory[/b][/purple].Form.Requery[/blue]

Which is it, [purple]ActionHist[/purple] or [purple]ActionHistory[/purple]?

Also what version Access ya using?

See Ya! . . . . . .

 

[klfduncan]

Sorry. It's ActionHistory. And I'm using 2002

The relationships are all there up and running, including Parent/Master -> Child properties functioning and it's all displaying it nicely when I click on the form and Shift+F9 and also when I do the Refresh button (which I never knew existed - thanks).

I just can't understand why pushing the button works, but if I call it on an AfterUpdate procedure it doesn't. I've tried everything written above.

It's driving me to distraction. I've tried now to:

Docmd.selectobject acform, "ActionDB"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

but this doesn't do anything either. I'm at my wit's end.

 

[klfduncan]

Right guys, thanks for the help, but I'm just going to take the easy option.

I've set the 'cycle' properties of ActionIn to 'Current Record' and force them to press the Refresh button.

I know it's a copout but it's more than my sanity's worth to continue with this.

Thanks again and sorry if I've wasted your time/set you on the path of insanity in trying to find out why this doesn't work!

KLF

 

[PHV]

klfduncan, are you sure that the control hosting the subform has the same name ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244