Updating from a form 1

[klmorgan]

I have made a Form that is based on a query to update the time that the next payment is due for a caravan storage system. It is based on a query that finds the record I want to change.

In the database there are two relevant fields: LastPay and NextPay which the form displays.

I have put two Text boxes in the form one is for the number of weeks paid and the other to calculate when the payment is next due ie.

If the NextPay field displays the date 02/10/2008 and I enter 12 in the WeeksPaid box the NextDue box displays 25/12/2008 which is correct.

But here is the problem I want to then press an accept button on the form to carry out the following

Set LastPay = NextPay
Set NextPay = NextDue Box
Save the record

I have tried to write a Macro to work off the button but it dosn't seem to work, it seems to think that the file is closed.

Can you tell me how I can achieve what I want?

Kind Regards

Keith

 

[ClydeData]

Keith
Have a look at the date add function in VBA rather than a macro,or do it in your SQL

In VBA
if your fields were
WeeksPaid
NextDueDate
ActualDatePaid

The this would calculate the next date


Me.NextDueDate = DateAdd("ww", Me.WeeksPaid, Me.ActualDatePaid)

Hope this helps


Jimmy

 

[klmorgan]

I have used your suggestion in a Private sub but it Invalid outside procedure error.

I have produced an update query with the following sequal statement: UPDATE Customers SET Customers.LastPay = Customers.NextPay, Customers.Nextpay = Customers.Nextpay+([weeks paid]*7), Customers.weekspaid = [weeks paid]
WHERE (((Customers.ContactID)=[Enter Id No]));

And it works fine but I can't seem to replicate this off a control button in a form.

Sorry if i'm being dense

Regards

Keith

 

[PHV]

In the Click event procedure of the button:

DoCmd.RunSQL "UPDATE Customers SET LastPay=NextPay, Nextpay=Nextpay+(" & Me![weeks paid]*7 _
 & "), weekspaid=" & Me![weeks paid] & " WHERE ContactID=" & Me![contact Id]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886