Prev: Forms based on a query
Next: listbox requery
From: DZ on 1 Jul 2008 17:15
Hi

I want to refresh the data in a form programatically.

I have a main form where the user can update a value in the form's
underlying data by double clicking a control on the form. Doubleclicking
opens another form that allow him to update the value. The reason why there
is a separate form just for updating rather than doing it in the main form is
that the underlying data is a query that join two tables and the value I want
the user to update is not updatable in the query.

So the update form's underlying data is one of the table's in the query
with joins.
In this table I am able to change values.

Now after the user makes the change in the update form and then closes it, I
want the change to appear immediately in the main form. And I want the main
form to update automatically without using a button so that its simpler for
the user. i noticed that if I execute Remove filter Sort from the Records
menu while the main form is active, the changes update. But I can't seem to
do this programmatically.

I tried to perform this update programatically unsuccessfully by doing the
following. none of these worked

From the main form

Private Sub Form_Activate()
DoCmd.RunCommand acCmdRemoveFilterSort
Me.Refresh
Me.Requery
End Sub

From the update form

Private Sub Form_Unload(Cancel As Integer)
Forms("Employee Hours").Controls("Projected Hours") = Me.Projected_Hours
End Sub
'This tries to simply transfer the value from a control in the update form
to a control in the main form....I get an error "The recordset Is not
updateble". That makes sense, but i wanted to try it anyway

please help me refresh the data in the main form

Thanks for any help... I always credit any help I get
From: Marshall Barton on 1 Jul 2008 18:02
DZ wrote:
>I want to refresh the data in a form programatically.
>
>I have a main form where the user can update a value in the form's
>underlying data by double clicking a control on the form. Doubleclicking
>opens another form that allow him to update the value. The reason why there
>is a separate form just for updating rather than doing it in the main form is
>that the underlying data is a query that join two tables and the value I want
>the user to update is not updatable in the query.
>
>So the update form's underlying data is one of the table's in the query
>with joins.
> In this table I am able to change values.
>
>Now after the user makes the change in the update form and then closes it, I
>want the change to appear immediately in the main form. And I want the main
>form to update automatically without using a button so that its simpler for
>the user.
[snip[

This kind of thing is usually done by opening the update
form in dialog mode followed by a requery:

DoCmd.OpenForm "theotherform", WindowMode:=acDialog
Me.Requery

Not only is the code simpler, but it avoids spreading the
code out over the other form and trying to make the Activate
event part of the action. It also prevents "innovative"
users from bouncing around your application doing strange
things to the program and/or data.

--
Marsh
MVP [MS Access]
 | 
Pages: 1
Prev: Forms based on a query
Next: listbox requery