Advanced Calculations

[TheDignified]

Hello there I've been trying to get a field to update itself based on another fields entry, but the problem is this involves a Data type & normal text.

So I have fault logging database and there is a DueDate field which I want to be auto populated based on the Priority which is a drop down field, and has the following options:

Low (Due date = Due date +5 Days)
Medium (Due date = Due date +3 Days)
High (Due date = Due date +1 Days)

The problem I am having is I need the database to somehow enter in these values after the users add a new fault to the system where they are able to select the priority.

Thanks in advance!

 

[sneuberg]

I understand you have a form with a combo having three possible selections. But I don't understand what's suppose to happen after a selection. Is text to be added somewhere based on some date. Or is a date to be added base on the selection? Or do you want to display a priority based on a due date and the current date, e.g, the priority would be high if the due date were today or tomorrow.

 

[TheDignified]

So above is what the fault logging form looks like and when a user selects a priority and press' the report button I would like it to automatically fill in the due date based on the what priority is selected, So like I said the system would have to get the current date Date(), and add 1 Day for High Priority, 3 For Medium and 5 for low priority, but I'm not actually sure how I would get this to be added in a table.



So like can be seen in this attachment the last field "DueDate" will need to somehow be filled in after the 'Report' button is clicked depending on the value of 'Priority'

Hope this makes more sense.

 

[sneuberg]

I suggest adding the DateDue field field to the form as a textbox. You could hide this textbox if you want. Then in the afterupdate of the Priority combo box add code to set the value of the DateDue textbox based on the combo box selection. Assuming the name of the combo box were cboPriority and the values of the bound column are Low, Medium and High your code could be something like.

Select Case cboPriority
    Case "Low"
        Me.DueDate = DateAdd("d", 5, Date)
    Case "Medium"
         Me.DueDate = DateAdd("d", 3, Date)
    Case "High"
         Me.DueDate = DateAdd("d", 1, Date)
End Select

The due date would be saved with the rest of the forms data.

 

[The_Doc_Man]

First, if you have a combo box, there IS such a thing as having a second (hidden) field as part of that combo that just lists the number of days to allow for each priority.

Second, Steve's question is to the point. Is the priority due to the short fuse or is the short fuse due to the priority?

Third, if this is original data entry, do the computation in the form as part of the form. Don't try to make some crazy computed field.

 

[TheDignified]

@sneuberg, That worked perfectly, thanks for much for that and everyone else that helped!

 

[sneuberg]

I think The Doc Man has a good idea in making the days to be added part of the combo box. I'd do that by added a table that would be the row source of the combo box. Let;s say the field are named Priority and DaysToAdd and so the table looks Like:

with this as the row source of the comb box the code simplifies to:

   Me.DueDate = DateAdd("d", cboPriority.Column(1), Date)

and the these days that are added wouldn't be hard code. You could make a form for the table so these could be changed without changing the code. You could even add new priorities for example Very Low, 8, Nobody Cares, 30