passing start and stop date based on status

[megatronixs]

Hi all,

I have a 2 columns that needs to be filled in with a date.
recently the status changed, but this should not impact the rest of the idea.
If there is a status already filled in, and the user changes to "Approved - Awaiting Consent" or "Approved - Awaiting Documents" then it should fill in the date in the column "stop_status" and if the status changes to other that are not equal to the 2 above, a date should be filled in column "status_go_on"

I wanted to creat a function for this, but got lost with the Select Case.

I created the below code that is trigered by a person changing the status, but it could be also be updated in the morning on the table and then it would be missed.

Private Sub combo_status_AfterUpdate()
If (Me.combo_status = "Approved - Awaiting Consent" Or Me.combo_status = "Approved - Awaiting Documents") And IsNull(Me.status_stop) Then
    Me.status_stop = Date
    End If
If Not IsNull(Me.status_stop) And (Me.combo_status <> "Approved - Awaiting Consent" Or Me.combo_status <> "Approved - Awaiting Documents") And IsNull(Me.status_go_on) Then
    Me.status_go_on = Date
    End If
End Sub

The code will not always work and I can't find where it goes wrong even by running the code step by step.


basically, it should check the date if it is blank in the first column "status_stop" if empty, then fill it in. then, when another status is changed and is not equal to the 2 above and the first column is not empty, fill the date in the column "status_go_on", if both are filled in, they should be skiped to avoid that a date is filled in again and change the last date and so ruin the calculations.

Greetings.

 

[static]

If Not IsDate(status_stop) Then
    Select Case combo_status
        Case "Approved - Awaiting Consent", "Approved - Awaiting Documents"
            status_stop = Date
        Case Else
            status_go_on = Date
    End Select
Else
    If Not IsDate(status_go_on) Then
        status_go_on = Date
    End If
End If

 

[megatronixs]

Hi Static,

Can I use this also in a function so it trigers when it is changed in the table?


Greetings.

 

[static]

I've never tried it. Apparently you can run VBA from a trigger but it's not recommended...

http://stackoverflow.com/questions/12352620/run-vba-code-from-within-2010-data-macro

The code I posted wouldn't work as is though.

 

[megatronixs]

Hi, I meant that when the forms open, the function will check the status and update if necesery. If it needs to be changed manually and the status was changed in the morning directly in the table, then when the forms open the new status is there but not added those dates into the columns.

Greetings.

 

[static]

If the users randomly modify values directly in the tables then there doesn't seem to be much point in trying to control anything.
I think all you could do in that case is put the code in the form's oncurrent event so values are updated when the user selects each record.

 

[megatronixs]

Hi,
I should have mentioned that the updates on the tabales are done by a different team that runs an update query and then change the updates acording to the data in the main system. It is done in the mornings and the users don't have access to the tables.
I added your code to the on load form, this way when the database is opened, it will update all the records (I hope).

Greetings.

 

[static]

Nope, that will update the first record only.

I'll see if I can write some SQL for you later.

 

[static]

s= " update table1 set status_stop = date() where"
s=s & " not isdate(status_stop) and combo_status like 'Approved - Awaiting*'"
CurrentDb.Execute s

s= " update table1 set status_go_on = date() where"
s=s & " (isdate(status_stop) and combo_status not like 'Approved - Awaiting*') or"
s=s & " (Not IsDate(status_go_on) and combo_status not like 'Approved - Awaiting*')"
CurrentDb.Execute s

Change table1 to your table

 

[megatronixs]

Hi,
I will give it a try.
Thanks a lot.

Greetings.