DateAdd problem in a form

[KW99]

Hi,

I have set up a form with combo boxes, which get their data from tables.

I have also set up a received date box which uses a calender to fill.

The box on the form I need to autofill is the target date. This will obviously be a date in the future. I can set this as a standard date by using the following expression in the Control Source in properties (i.e. for 30 days):

=DateSerial(Year([Daterec1]),Month([Daterec1]),Day([Daterec1])+30)

Now here is what I want to do:

I have another combo box on the form which has member tasks (again data is from a table).

I would like to have a set job time for each member task (i.e. 3 days), which when the member has selected the member task and input the received date will adjust the target date accordingly.

I also need the set times for each task to be updatable by the user if required. I assume I can put the set timescales in the same table that the member task data is pulling from (the user can then update in the future if required), however I do not know how to combine it all with the form.

Does anyone have any ideas about this one?

Cheers

 

[Guus2005]

I believe this is the code you want.

=DateSerial(Year([Daterec1]+30),Month([Daterec1]+30),Day([Daterec1]+30))
or 
=DateAdd("d",30,DateSerial(Year([Daterec1]),Month([Daterec1]),Day([Daterec1])))
or shorter
=DateAdd("d",30,[Daterec1])

give or take a few parenthesis.

The rest of your question is not clear to me or it has too many possibilities.

Enjoy!

 

[missinglinq]

That last example is really what is needed; no reason to use DateSerial() at all. I, too, am not clear on the rest of the question.

 

[namliam]

Or even just shorter
=[Daterec1] + 30




Instead of the contant your can also use a field elsewhere in another table to simularly add to the date.

=[Daterec1] + [TaskDays]

This can be 3 days or 3.5 days if you like.

If you want it in hours (add 16 hours for example)
=[Daterec1] + [TaskTime] / 24

Task time would then be a number field containing 16 or 48 or 120 or whatever you want

 

[missinglinq]

Using a variable for the number of days per task is a solid idea, but most of the MVPs will tell you that

[Daterec1] + 30

isn't a good idea. It's kept (so far) for backwards compatibity, but may not always be viable.

 

[namliam]

How is that not a good idea??

Dates in access and SQL Server and Oracle all work fine with this method...

 

[KW99]

I am not sure if you have misunderstood my initial question or whether I just dont understand your replies.

My question was how to autofill the target date field by using:

Member task - dropdown the member chooses
Task time for the member task (pre set in the table - however user can change or add to in the future if required)

When the member task has been selected, the task time for that member task is used and automatically completes the target date.

I think I have just about worked it out. If I include the task time along with the member task in the combo box in the form, I can then create a new text box to pull the task time from it using:

Me.name of text box = Me.name of original combo box.column(1)

Surely I can then use this text box value in the code instead of having to type a set number of days or years etc.

I hope that this works as I have not had chance to test it yet.

 

[KW99]

The idea has worked to a degree however there is one flaw.

I have put the code listed below into the control source of the text box.

=DateSerial(Day([Daterec1])+[jobtime])

Works fine except that I cannot now save the result in the table.

Any ideas on how I can get around this?

Thanks

 

[boblarson]

The idea has worked to a degree however there is one flaw.

I have put the code listed below into the control source of the text box.

=DateSerial(Day([Daterec1])+[jobtime])

Works fine except that I cannot now save the result in the table.

Any ideas on how I can get around this?

Thanks

The answer is - YOU DON'T. You should not need to store calculated data (with RARE exception), as you can calculate it at any time via a query or control source. Storing such data is risky because currently there doesn't exist any kind of way for access to update the table if someone were to change one of the fields directly and then your table would be out of sync.

 

[KW99]

Oh, not what I wanted to hear....!

Why will the table not simply update, the same as if a new field was chosen from a drop down (apologies if this sounds stupid).

I take your point that it can be calculated in a report or form, however I have various update / search forms etc for the database which will all require amending to calculate the target date (currently most are locked to the user and I was hoping to have them autofill from the table).

Is there any way of storing a calculated field if required?

 

[namliam]

So what if you have seperate forms? Make one query that will do it all....

A calculated field once stored -if you actually want to do that, which you DONT- will be fixed. You can do a default value or do an update query... but your are setting yourself up for BIG problems if you go down the road.

You got the best advice from Bob, DONT! and use queries or unbound textboxes to calculate it. If it is something that is repeated a lot of times on different forms/reports a function or query is called for.

Again, offcourse anything is possible... but Rule #1 for any developer/database states: Never store calculated values!

 

[KW99]

Yes I take your point, I suppose it is not worth the hassle later on for a bit of effort now to change the forms (a query is not what I require for the specific jobs at this point).

I do have a further qestion regarding this.

I have set up a form to enter the initial data - no problems.

I have also set up a second form to search for a specific record and to allow the user to make amendments.

You search for the task ref using a drop down in the header of the form and it brings up the corresponding table in the main part.

The code I used in the original initial data form to pull through the time for the job was:

Private Sub Combo65_AfterUpdate()
Me.jobtime = Me.Combo65.Column(1)
End Sub

Here is my problem:

When I select the task ref the table pulls up fine, however the above code, which pull through the job time (which incidentally will be hidden), does not automatically come through. This is obviously because it is written in the after update event so if i change the member task it will then work.

How do I get it to work when the data initially comes up in the table (from the task ref selection)?

 

[namliam]

Combo65? What is that?
You should not allow your controls to have their default names.... this causes in-maintainable databases after a while.

It should be send to JobTime after the update of this combobox...

 

[KW99]

I was just doing this for speed today - I will rename eventually do I know what everything is.

I need the calculation to run the instant the record is pulled up. At the moment the member task record needs to either be clicked or changed to trigger the calculation to the job time.

At this point the only selection made by the user will be to enter the task ref from a drop down in the header of the form - this then brings the record up. At this point I need the calculation to trigger and not to have to click or amend the member task.

Hopefully this makes sense.

Thanks

 

[namliam]

You can trigger code "on current" to ensure instant calculation

 

[boblarson]

Oh, not what I wanted to hear....!

Why will the table not simply update, the same as if a new field was chosen from a drop down (apologies if this sounds stupid).

I take your point that it can be calculated in a report or form, however I have various update / search forms etc for the database which will all require amending to calculate the target date (currently most are locked to the user and I was hoping to have them autofill from the table).

Is there any way of storing a calculated field if required?

You don't need to do that. All you need to do is to have a base query that has all of the fields of the table and the calculated field and use that for your forms/reports.

 

[KW99]

How do I use the OnCurrent event as its not on the event list.

Could you tell me in simple terms as writing code is not my speciality as you have probably already guessed.

Thanks

 

[missinglinq]

The OnCurrent event is an event of the form itself, and therefore is only in the event list when the form itself is selected.

 

[KW99]

Thanks for that.

I still do not know how to use it, to get my calculation to run without having to click or amend the member task box.

I am sure it is fairly simple.......I hope!

 

[namliam]

Every time the record is changed the event is auto triggered. Just trigger your after update event to recalculate the field.