auto update a field based on a previous record

[Freida]

I posted this in the forms coding section but did not get a reply, probably
cause it was the wrong section. My data is actually stored in a SQL database,
I am only using the access forms for a front end so the data can actually be
typed in by the person doing the budget.

I want to know how do I populate the prior year budget
automatatically after the current budget year amount if put in. The prior
year Budget is in the same table as the current year. For example, the
person is entering the Company code, GL number, and current budget amount
for 2008. After this information is typed in. I need the code that will
look up Company code, GL number, and current amount for 2007, then I need
that current amount to go into a field called prior year budget on a got
focus event. Is this even possible?

 

[Armen Stein]

I posted this in the forms coding section but did not get a reply, probably
cause it was the wrong section. My data is actually stored in a SQL database,
I am only using the access forms for a front end so the data can actually be
typed in by the person doing the budget.

I want to know how do I populate the prior year budget
automatatically after the current budget year amount if put in. The prior
year Budget is in the same table as the current year. For example, the
person is entering the Company code, GL number, and current budget amount
for 2008. After this information is typed in. I need the code that will
look up Company code, GL number, and current amount for 2007, then I need
that current amount to go into a field called prior year budget on a got
focus event. Is this even possible?

Yes, it's possible. I'm assuming you just want to display the amount,
not store it again in the current year record, right?

There are a couple of ways to do this, but the simplest is probably:

type =GetLastYearsBudget into the ControlSource property of an unbound
textbox on your form.

Create that function (GetLastYearsBudget) behind your form. In it,
write code to check that all of the necessary fields on your form are
actually filled in. If they are, do a DLookup on your table to get
last year's budget.

Something like this: (warning - air code)

If not isnull(Me.Field1) and not isnull(Me.Field2) Then
Me.LastYearsBudget = DLookup("BudgetField", "MyBudgetTable",
"CompanyCode = """ & MyField1 & """ AND Year = " & MyField2 - 1)
End If

(note the subtraction of 1 from the Year to get last year's budget)

Hope this gets you started at least...

Armen Stein
Microsoft Access MVP
www.JStreetTech.com