Update Query to autofill date

[ibbledibble]

Hi

I have a table called Incidents with two date fields: 'date of contact' and 'deadline date'. When date of contact is entered I want the deadline date to be automatically completed as two weeks after the date of contact.

I was thinking of using an update query automatically run 'on exit' from the date of contact field but I keep getting a data mismatch expression. I have been trying to use the Dateadd function in the 'update to:' part of the query. I have to do it this way as I know nothing about VBA code.

Can anyone help please?

Thanks

 

[namliam]

Dont do this in a query rather do it in the form, on the after update event of the date of contact simply fille yoru deadline date.

Me.Deadline = Me.Dateofcontact + 14

 

[DCrake]

If the deadline date is always 14 days hence, then why are you adding this calculated field to the table. In your query you can calculate this base on the original date.

You can *display* it on the form but do not need to *save* it in the table.

David

 

[ibbledibble]

Dont do this in a query rather do it in the form, on the after update event of the date of contact simply fille yoru deadline date.

Me.Deadline = Me.Dateofcontact + 14

Thanks. The problem is I get an error message saying Access can't find the object 'Me.'

 

[ibbledibble]

If the deadline date is always 14 days hence, then why are you adding this calculated field to the table. In your query you can calculate this base on the original date.

You can *display* it on the form but do not need to *save* it in the table.

David

Ok, I'll remove the field 'Deadline date' from the table. How would I display it on the form?

Thank you for your patience!

 

[namliam]

Thanks. The problem is I get an error message saying Access can't find the object 'Me.'

If you do this inside the form like I told you, it shouldnt be a problem.

Thanks but I do need to save it as we need to calculate reports on how many cases meet their deadline date.

This is not a proper criterium to store data, the only reason to store it in a table would be if the data can change (i.e. it is a default date, but not always true)

Any calculation can be based of another calculation without loss of performance very easily

 

[ibbledibble]

If you do this inside the form like I told you, it shouldnt be a problem.

I right-clicked on the 'Date of contact' field name in form design view, selected properties, then pasted the expression you wrote into the 'after update' event. Should I do it another way?

 

[namliam]

Should I do it another way?

Nope that the way offcourse changing the fields to the names they need to be.

The syntax is valid though... 100% sure

 

[ibbledibble]

Nope that the way offcourse changing the fields to the names they need to be.

The syntax is valid though... 100% sure

Yep, I typed in correctly, changing the field names to the correct ones. Still doesn't work though. I've had the 'Me.' problem before, and not sure what it is.

(sound of head scratching).

The error message I get is this:

Access was unable to locate the macro or VBA function. If you are trying to call a macro, make sure that the name of the macro and the name of the macro group are spelled correctly.

If you are trying to call a user-defined VBA function, be sure to use the following syntax:
=FunctionName() or =FunctionName(argument1,argument2,...)

Make sure that the function is either:
Defined in the code for the form or report.
- or -
A public function that is in a module (not a class module).

 

[namliam]

Maybe you are missing a reference?
Check in the menu (while in the code) tools > References
See if anything says "Missing: ... "

Other than that I cannot imagine what might be wrong.

 

[ibbledibble]

Maybe you are missing a reference?
Check in the menu (while in the code) tools > References
See if anything says "Missing: ... "

Other than that I cannot imagine what might be wrong.

Nope, can't see anything saying 'Missing'. How frustrating.

 

[ibbledibble]

From what I understand 'Me.' should just refer to the current form that the code relates to? If so, I must be making a syntax error?

I currently have:

Me.Deadlinedate = Me.DateofContact + 14

 

[namliam]

Yes, Me refers to the form that is containing the code...

Again if deadlinedate is always dateofcontact + 14, then there is NO NEED to store this data in your table.

 

[ibbledibble]

Yes, Me refers to the form that is containing the code...

Again if deadlinedate is always dateofcontact + 14, then there is NO NEED to store this data in your table.

Ok, I'll remove the field 'Deadline date'. How would I display the date of contact + 14 days on the form. Remember that when I open the form, the date of contact won't have a value until I enter it.

P.S. I should add that I wanted the field so it would be easier to calculate averages when I do my end of month reporting.

 

[DCrake]

On the OnCurrent Event of the form

Me.DeadlineDate = Nz(Me.DateOfContract+14,"")

Also you can add this to the Before/After update event of the contract date.

David

 

[namliam]

Same way except with an unbound control

 

[namliam]

Or use
= Nz(Me.DateOfContract+14,"")

As the control source for an unbound control

 

[ibbledibble]

I'm sorry guys I really am, but the 'Me.' keeps throwing up the error. Could it be with how I've named by forms/tables?

I don't know, don't want to be coming across as dull here but it's not working.

 

[namliam]

I am stuck, if you are entering it in the "on event code" coding window, me should work.

If you are using a "general" module though Me will fail

 

[ibbledibble]

I am stuck, if you are entering it in the "on event code" coding window, me should work.

If you are using a "general" module though Me will fail

I'm right clicking on the date of contact field in the form's design view, selecting the event tab and entering the code in the After Update bit.

Although it breaks naming conventions, I've called the form the same name as the table. Could this be it?