Date Problem

[uplink600]

I have the following line of code in the form_Current event of my main database form.

Me.[FollowUpDate].Value = Me.[QuoteDate] + (8 - Me.Text70)

The value in Text70 is calculated using the formula =DatePart("w",[QuoteDate],2) which is in the control source of the textbox.

It sets a quotation review date to the Monday of the following week and this is displayed in the textbox FollowUpdate and the corresponding field in the table. All dates have the medium date format.

OK, the problem is that although the correct date (Monday of the week following the quote) is stored in the field, this date value is changed each time I open the form in subsequent weeks so it keeps rolling over to the next week. Once I have the review date set when the record is entered I don't
want it changed unless it's done manually.

Please advise if you can help. Is the code/event etc correct. I would appreciate your help.

Thanks

VC

 

[Pat Hartman]

You have used the wrong event for your code. The form's Current event fires each time the form moves to a new record. So the code that modifies the date field is being executed by simply scrolling through the recordset.

If you only want the value set once when the QuoteDate is changed, put the code in the AfterUpdate event for the QuoteDate control.

 

[uplink600]

Thanks

The QuoteDate field defaults to the date of record entry when the form is opened for a new record using =Now(). Would your solution work with this setup. I won't have access to the database until I get home this evening so I can't try it yet.

Thanks

VC