Want subform updated

[GeorgeJ]

This question describes a kind of a complicated situation but it would be really helpful if I could do what I am trying to do. I have a form (call it BigForm) which has two subforms, call them Subf1 and Subf2. Subf1 is a tabular form with record selectors. I have a routine inside the
sub Form_Current


property of form Subf1. This routine executes every time the user changes the record which is selected in Subf1.

The routine takes one of the fields of the selected record in subf1 and puts it into a global string variable. Then another routine which is called from sub Form_current uses this string to create records in a table. The info in this table depends on the info in the global string variable set with the value obtained from the selected record in subf1.

The second subform (subf2) gets its data from this table. The thing is, the second subform is not ubpdated when this happens. If I close BigForm and open Subf2 directly I see that it now shows the appropriate data in the table, corresponding to which record was selected in Subf1 inside BigForm. But this isn’t really helpful. What I want is for the info shown in Subf2 to be updated when the user clicks on a different record in Subf1. In other words, when the table from which Subf2 gets its data is changed, I would like the information displayed in Subf2 on Bigform to reflect this fact. Is there some sort of a refresh command?

Any suggestions.

[A S MANN]

In the after update event of SubF1 use

DoCmd.Requery "table"
DoCmd.RunCommand acCmdRefresh

[GeorgeJ]

Thank you for your response.

The recordsource for Subf2 is the table "Table1". When I open BigForm, which has as subforms both Subf1 and Subf2, I see the correct info in Subf2.

When I put the following code in the Form_Current subprogram in the code file of Subf1

Docmd.Requery "Table1"

DoCmd.RunCommand acCmdRefresh

and then I select another record in subf1 I get the following error message


"Runtime error 2046. The command or action Requery isn't available now"

I get the same message if the code references subf2 rather than Table1, e. g. Docmd.Requery "Subf2"

Any suggestions?

[ssanfu]

I am having trouble understanding the form relationships.

First though, you cannot "requery" a table. A table is just a container that holds (stores) data. You can requery forms/subforms, combo boxes and list boxes because they have a record source.

So you have a main form, "BigForm", with two subforms - "Subf1" and "Subf2".
Is "BigForm" unbound? Why are "Subf1" and "Subf2" in "BigForm"?


It sounds like "Subf2" is a subform of "Subf1".
Do they have a common linking field? (Link Master Field and Link Child Field/)

[GeorgeJ]

Well, I finally figure out the answer, which I am posting here in case anyone will find it helpful

Condition:

A form BigForm contains 2 subforms, Subf1 and Subf2. (Subf2 is directly on Bigform, it is not a subform of Subf1). Both of these subforms are tabular. Their record-sources are Table1 and Table2 respectively. One of the fields of Table1 is InfoField. There is a public routine MakeTable2 which puts data into Table2. The data which MakeTable2 puts into Table2 is dependent on a global variable Infostring$. It is desired that as the user selects different records in Subf1, the global InfoString$ is set to Subf1!Infofield and then Table2 is updated by running MakeTable2, and then the display in Subf2 reflects this update.

Here is how to accomplish this:

In the code file of Subf1, Put the following code

Sub Form_Current()
Infostring$ = me!InfoField
MakeTable2
me.parent.Subf2.Requery
end sub


(A problem that vexed me for a long time was that I tried to access Forms!Subf2 and kept getting an error message saying that it couldn't be found. I have discovered that sometimes you can access an open form this way and other times you can't. It may be that you can access a parent form this way but not a subform.)