Updating data in a form

  • Thread starter Thread starter Les
  • Start date Start date
L

Les

I have created a form and added a refresh button that
runs a query in the background and updates the
information in the form. How can I get the form to update
on change data without having to manually click the
update button?
Thank you in advance for your great support.
 
Les said:
I have created a form and added a refresh button that
runs a query in the background and updates the
information in the form. How can I get the form to update
on change data without having to manually click the
update button?
Thank you in advance for your great support.
Click to expand...
There is no need for a query to do this.
Information on a form updates the recordsource when you move to a new
record, close the form or move to a subform. It can also be done in code
without the need for a query.
 
Les,

Could you please give a bit more detail? An example of how the data is
getting updated would help.
 
Field 1 = Total Amount comes from a table
Field 2 = Remaining Amount comes from the same table.
Field 3 = Add to Amount is an amount that is entered in
the form and is added to Field 1 and Field 2 using an
update query.
The process I use not is to enter the Add to Amount in
the field, then click on a refresh button to update the
table with the data I have just entered in the field.
Then click on a button I have created that runs the query
to update the amounts in the tables, then again click the
refresh button to update the final amount in the form. I
know that what I am doing is a long way to get to a
simple end.
 
-----Original Message-----

There is no need for a query to do this.
Information on a form updates the recordsource when you move to a new
record, close the form or move to a subform. It can also be done in code
without the need for a query.

OK, but the data in the form doesn't change unless you
Click to expand...
refresh. Can you get the for to automatically refresh the
data in the form when you make a change or exit a field?
 
Les,

The first point to make here, without really knowing enough details to
be specific, is that your table design is probably not the best, and
probably more complicated than necessary. What you are really saying is
that you have values stored in table fields which are derived from, or
calculated from, the values in other fields, and this is almost never a
good plan. It is the kind of thing you see in spreadsheets and stuff
like that, but it is seldom applicable in a database. Anyway, just
something to consider.

I suppose you could use the AfterUpdate event of the Field 3 control to
run your code. If I understand your process correctly, this could be as
simple as...

Private Sub Field_3_AfterUpdate()
Me.Field_1 = Me.Field_1 + Me.Field_3
Me.Field_2 = Me.Field_2 + Me.Field_3
End Sub

However, this woud be a bit dangerous without asking the user for
confirmation, e.g.

Private Sub Field_3_AfterUpdate()
If MsgBox("Add " & Me.Field_3 & " to F1 and F2?", vbYesNo) = vbYes
Then
Me.Field_1 = Me.Field_1 + Me.Field_3
Me.Field_2 = Me.Field_2 + Me.Field_3
End If
End Sub
 
Back
Top