Hi
I have code in vba that I want to execute only if a number of criteria are met.
This is the full code I have and although it does appear to work, is there a cleaner way of handling this? I thought a case select would work, but I couldn't get it to work as its not one criteria and many actions. Rather it is many criteria to produce one action.
I am looking to add an extra line that would be something like
If SFStatus=SFStatus3 and TAStatus=TAStatus2 and SentStatus = SentStatus2 and MemoStatus=MemoStatus1 or MemoStatus2
But this won't work and seems incredibly messy. I'm really going around in circles here - please help!!
Thanks
I have code in vba that I want to execute only if a number of criteria are met.
Code:
Private Sub Form_Current()
'displays the file status and the TA status for any study
On Error GoTo PROC_ERR
Dim SFStatus As String
Dim SFStatus1 As String
Dim SFStatus2 As String
Dim SFStatus3 As String
Dim TAStatus As String
Dim TAStatus1 As String
Dim TAStatus2 As String
Dim CommitStatus As String
Dim CommitStatus1 As String
Dim CommitStatus2 As String
Dim MemoStatus As String
Dim MemoStatus1 As String
Dim MemoStatus2 As String
Dim MemoStatus3 As String
Dim SentStatus As String
Dim SentStatus1 As String
Dim SentStatus2 As String
Dim SentStatus3 As String
'declare all variables then show equivelence for SF Status.
SFStatus1 = "NOT ASSIGNED"
SFStatus2 = "IN PROGRESS"
SFStatus3 = "COMPLETED"
'show equivelence for TA status
TAStatus1 = "NOT ASSIGNED"
TAStatus2 = "ASSIGNED"
'for Commit Date
CommitStatus1 = "NO COMMIT DATE"
CommitStatus2 = "A COMMIT DATE"
'and again for GMO status
MemoStatus1 = "a memo is NOT REQUIRED."
MemoStatus2 = "a memo is still REQUIRED."
MemoStatus3 = "a memo has been PROVIDED."
'finally for SentToLab Status
SentStatus1 = "HAS NOT been sent"
SentStatus2 = "HAS sent"
'arguments
'if a CS coordinator has not been assigned then the Study file is "not assigned"
'if a CS coordinator has been assigned but the file is not complete then "in progress"
'if the file is both assigned and complete, then "completed"
If IsNull(Me.CS_coordinator) Then
SFStatus = SFStatus1
ElseIf Not IsNull(Me.CS_coordinator) And Completed = False Then
SFStatus = SFStatus2
Else
SFStatus = SFStatus3
End If
'if the TA receipt date is null then the TA status "TA unassigned"
If IsNull(Me.TA_receipt_date) Then
TAStatus = TAStatus1
'otherwise the TA is "assigned"
ElseIf Not IsNull(Me.TA_receipt_date) Then
TAStatus = TAStatus2
End If
'if the commit date is null then notify the user that a commmit date has not been provided
If IsNull(Me.Client_Commit_Date) Then
CommitStatus = CommitStatus1
'if the commit date has been provided then notify user as such
ElseIf Not IsNull(Me.Client_Commit_Date) Then
CommitStatus = CommitStatus2
End If
'if the GMO is not required and there is not GMO number then GMO "not required"
If (Me.GMO_req) = "No" And IsNull(Me.GMO_) Then
GMOStatus = GMOStatus1
'if the GMO is required but the GMO number has not been provided then notify user as such
ElseIf (Me.GMO_req) = "Yes" And IsNull(Me.GMO_) Then
GMOStatus = GMOStatus2
'the last possibility should be that the GMO is required and the number has been provided. If so then GMO "provided"
ElseIf (Me.GMO_req) = "Yes" And Not IsNull(Me.GMO_) Then
GMOStatus = GMOStatus3
End If
'gosh! finally for the sent to lab status and hide the box if no value
If IsNull(Me.Sent) Then
SentStatus = SentToLabStatus1
Date.Visible = False
Else
SentStatus = SentStatus2
Date.Visible = True
End If
'if the memo required field is set to no then dont show field
If Me.memo_req = "No" Then
Me.memo.Visible = False
Else
Me.memo.Visible = True
End If
'hide the date completed box if it is null
If IsNull(Me.DateCompleted) Then
DateCompleted.Visible = False
Else
Me.DateCompleted.Visible = True
End If
'display complete status of study file to user in the lblmessage label
lblmessage.Caption = "This STUDY FILE is " & SFStatus & " and the TEST ARTICLE is " & TAStatus & ". " & CommitStatus & " has been provided and " & MemoStatus & " The SF " & SentStatus
PROC_EXIT:
Exit Sub
PROC_ERR:
Call ShowError("PubFunErrorHandling", "frmStudyFileStatusSD, Form_Current", err.Number, err.Description)
GoTo PROC_EXIT
End Sub
This is the full code I have and although it does appear to work, is there a cleaner way of handling this? I thought a case select would work, but I couldn't get it to work as its not one criteria and many actions. Rather it is many criteria to produce one action.
I am looking to add an extra line that would be something like
If SFStatus=SFStatus3 and TAStatus=TAStatus2 and SentStatus = SentStatus2 and MemoStatus=MemoStatus1 or MemoStatus2
But this won't work and seems incredibly messy. I'm really going around in circles here - please help!!
Thanks