If...End if combination logic

[VBABEGINER]

Hi All,
Can anyone help me in below code, to rectify it. Something went wrong in logic.

Situation is, when selecting "Core", submit not happened and when select "Non-Core", submit works.

Also, if on any place we can minimize the code, please do guide / help..

Private Sub btnsubmit_Click()
Application.ScreenUpdating = False


Dim Cn As New ADODB.Connection
Dim cs As String
Dim rs1 As New ADODB.Recordset
                 
'Case 1
If cmbActivity.Value = "" Or ComboBox1.Value = "" Or TxtCaseID.Value = "" Or TxtEETime.Value = "" Or cmbProjectName = "" Or cmbTaskName.Value = "" Or cmbTaskStatus.Value = "" Then
    MsgBox "Select respective item's"
Else


'case 2
If cmbActivity.Value = "Non-Core" And TxtCaseID.Value = "" And TxtEETime.Value = "" And cmbProjectName = "" And cmbTaskName.Value = "" And cmbTaskStatus.Value = "" Then
If ComboBox1.Value = "" Then
    MsgBox "Select sub non-core activity..!!"
Else
    cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\group$\WNA\Timesheet_Data.accdb"
    Cn.Open cs
    rs1.Open "Table1", Cn, adOpenDynamic, adLockOptimistic
    
    With rs1
    .AddNew
    .Fields("Start Date/Time") = Me.txtstartdate
    .Fields("Closed Date/Time") = DateTime.Now
    .Fields("Activity Type") = Me.cmbActivity
    .Fields("Sub Type") = Me.ComboBox1
    .Fields("Comments") = Me.txtcomm
    .Fields("User name") = Me.LblUname
    .Update
    End With
    
    Set rs1 = Nothing
    Cn.Close
    Set Cn = Nothing
    
    txtstartdate.Value = ""
    cmbActivity.Value = ""
    ComboBox1.Value = ""
    cmbTaskStatus.Value = ""
    txtcomm.Value = ""
    
    MsgBox "Details Updated"
    Call UserForm_Initialize


    Exit Sub




'case 3
If cmbActivity.Value = "Core" Then
If ComboBox1.Value = "" Then
    MsgBox "Select sub core activity..!!"
     If TxtCaseID.Value = "" Then
            MsgBox "Copy-Paste CASE ID from EE Tool"
                If TxtEETime.Value = "" Then
                    MsgBox "Copy-Paste EE Time from EE Tool"
                    If cmbProjectName.Value = "" Then
                        MsgBox "Select Project Name.."
                            If cmbTaskName.Value = "" Then
                                MsgBox "Select Task Name.."
                                    If cmbTaskStatus.Value = "" Then
                                        MsgBox "Select Status of the Task"


Else


    cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\group$\WNA\Timesheet_Data.accdb"
    Cn.Open cs
    rs1.Open "Table1", Cn, adOpenDynamic, adLockOptimistic
    
    With rs1
    .AddNew
    .Fields("Start Date/Time") = Me.txtstartdate
    .Fields("Closed Date/Time") = DateTime.Now
    .Fields("Activity Type") = Me.cmbActivity
    .Fields("Sub Type") = Me.ComboBox1
    .Fields("Case ID") = Me.TxtCaseID
    .Fields("EE Tool TimeDate") = Me.TxtEETime
    .Fields("Project Name") = Me.cmbProjectName
    .Fields("Task Name") = Me.cmbTaskName
    .Fields("Task Status") = Me.cmbTaskStatus
    .Fields("Comments") = Me.txtcomm
    .Fields("User name") = Me.LblUname
    .Update
    End With
    
    Set rs1 = Nothing
    Cn.Close
    Set Cn = Nothing
    
    txtstartdate.Value = ""
    cmbActivity.Value = ""
    ComboBox1.Value = ""
    TxtCaseID.Value = ""
    TxtEETime.Value = ""
    cmbProjectName.Value = ""
    cmbTaskName.Value = ""
    cmbTaskStatus.Value = ""
    txtcomm.Value = ""
    
    MsgBox "Details Updated"
    Call UserForm_Initialize
        
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If


Application.ScreenUpdating = True
End Sub

 

[Norie]

You need to totally re-arrange things, for a start you don't need so much nesting.

For example instead of having all these Ifs nested each one should be separate and include an Exit Sub.

     If TxtCaseID.Value = "" Then
            MsgBox "Copy-Paste CASE ID from EE Tool"
                If TxtEETime.Value = "" Then
                    MsgBox "Copy-Paste EE Time from EE Tool"
                    If cmbProjectName.Value = "" Then
                        MsgBox "Select Project Name.."
                            If cmbTaskName.Value = "" Then
                                MsgBox "Select Task Name.."
                                    If cmbTaskStatus.Value = "" Then
                                        MsgBox "Select Status of the Task"

So it would look something like this.

If TxtCaseID.Value = "" Then
    MsgBox "Copy-Paste CASE ID from EE Tool"
    Exit Sub
End If

If TxtEETime.Value = "" Then
    MsgBox "Copy-Paste EE Time from EE Tool"
    Exit Sub
End If

If cmbProjectName.Value = "" Then
    MsgBox "Select Project Name.."
End If

If cmbTaskName.Value = "" Then
    MsgBox "Select Task Name.."
End If

If cmbTaskStatus.Value = "" Then
    MsgBox "Select Status of the Task"
End If

 

[MUKESHY12390]

are you missing somthing after these lines?

MsgBox "Select respective item's"

And
MsgBox "Select sub non-core activity..!!"

I think you should use "Exit sub"

 

[VBABEGINER]

Thanks guys for replies..but wrong in my code something...logic is not correct..

 

[VBABEGINER]

VBA Form

cmbActivity = Core / Non Core

if cmbActivity = Core then

combobox1 = Core sub items

else

if cmbActivity = Non Core then

combobox1 = Non Core sub items

end if

If its Core selection, user should fill all the items and then Submit button.

If its Non Core selection, user should fill only cmbActivity and combobox1 value and submit.

 

[Norie]

The 'problem' with the logic is caused by the nesting/order of your If statements.

What is the first thing you want/need to check?

 

[VBABEGINER]

Hi Norie, Thanks for revert.
See its like..based on condition..I cant show you my user form...but I will try to explain here..


1st is the Combo box named cmbActivity.
Values = Core and Non-Core
**
2nd is the Combo box named combobox1.
Values = sub list of Core and Non-Core selection..
**
3rd is the text box named TxtCaseID.
Values = User will copy paste here 5 digit number from other source.
**
4th is the text box named TxtEETime.
Values = User will copy paste here date from other source.
**
5th is the combo box named cmbProjectName.
Values = Same Workbook - Sheet1 - Col C
**
6th is the combo box named cmbTaskName.
Values = Same Workbook - Sheet1 - Col D
**
7th is the combo box named cmbTaskStatus.
Values = Declared on user form activate

 

[VBABEGINER]

Code for post no 7.. UserForm Code

Private Sub UserForm_Initialize()
mTimer.TimerOn 10
BtnStart.Enabled = False
BtnStop.Enabled = True
    
UserForm1.BackColor = RGB(200, 215, 223)


Dim Uname As String
Uname = Application.UserName
Me.LblUname = Uname


With cmbActivity
    .AddItem "Core"
    .AddItem "Non-Core"
End With


With cmbTaskStatus
    .AddItem "WIP"
    .AddItem "Completed"
    .AddItem "Pend (Queries) - Tech"
    .AddItem "Pend (Queries) - Non-Tech"
    .AddItem "Pend (Queries) - INPUT"
    .AddItem "Closed - Succesfull"
    .AddItem "Closed - Un-Succesfull"
    .AddItem "Re-Assign"
    .AddItem "Diary"
End With


End Sub

 

[VBABEGINER]

Code for post no 7.. cmbActivity_Change() code

Private Sub cmbActivity_Change()
BtnStart.Enabled = True
BtnStop.Enabled = False
mTimer.TimerOff


Me.txtstartdate = DateTime.Now


If cmbActivity.Value = "Core" Then
Label14.Caption = "Core Activity"


TxtCaseID.Visible = True
TxtEETime.Visible = True
cmbProjectName.Visible = True
cmbTaskName.Visible = True
cmbTaskStatus.Visible = True


LblCaseID.Visible = True
LblEETime.Visible = True
LblProjectName.Visible = True
LblTaskName.Visible = True
LblTaskStatus.Visible = True
        
Else


If cmbActivity.Value = "Non-Core" Then
Label14.Caption = "Non-Core Activity"


TxtCaseID.Visible = False
TxtEETime.Visible = False
cmbProjectName.Visible = False
cmbTaskName.Visible = False
cmbTaskStatus.Visible = False


LblCaseID.Visible = False
LblEETime.Visible = False
LblProjectName.Visible = False
LblTaskName.Visible = False
LblTaskStatus.Visible = False
End If
End If




Dim index As Integer
index = cmbActivity.ListIndex
ComboBox1.Clear 'ComboBox1 is the Core / Non-Core list option.
Select Case index
Case Is = 0
With ComboBox1
    .AddItem "New Work"
    .AddItem "Rework"
    .AddItem "Rework Error"
    .AddItem "Pend"
    .AddItem "WIP"
    .AddItem "Rework WIP"
    .AddItem "Rework Others"
    .AddItem "New Work Audit"
    .AddItem "Rework Error Audit"
    .AddItem "Rework Audit"
    .AddItem "Pend Audit"
    .AddItem "WIP Audit"
    .AddItem "Rework WIP Audit"
    .AddItem "Rework Others Audit"
    .AddItem "Projects"
    .AddItem "Process Training"
    .AddItem "Tech Meeting"
    .AddItem "Allocation"
    .AddItem "Stake holder reviews"
    .AddItem "Reporting"
    .AddItem "Senior Role"
End With
Case Is = 1
With ComboBox1
    .AddItem "Break"
    .AddItem "Huddle"
    .AddItem "Fun Activity"
    .AddItem "Non Technical Meetings"
    .AddItem "No Work"
    .AddItem "Non Process Training"
    .AddItem "R&R/Town hall"
    .AddItem "System issues"
    .AddItem "1 to 1"
    .AddItem "MI"
    .AddItem "Delay - Transport"
    .AddItem "Delay - Personal"
End With
End Select


Dim iC As Long
iC = Workbooks("Timesheet.xlsm").Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Row
UserForm1.cmbProjectName.RowSource = "'[Timesheet.xlsm]Sheet2'!$C$2:$C" & iC


Dim iD As Long
iD = Workbooks("Timesheet.xlsm").Sheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Row
UserForm1.cmbTaskName.RowSource = "'[Timesheet.xlsm]Sheet2'!$D$2:$D" & iD
End Sub

 

[VBABEGINER]

And, Submit button Code..

Private Sub btnsubmit_Click()
Application.ScreenUpdating = False


Dim Cn As New ADODB.Connection
Dim cs As String
Dim rs1 As New ADODB.Recordset
                 
'Case 1
If cmbActivity.Value = "" And ComboBox1.Value = "" And TxtCaseID.Value = "" And _
TxtEETime.Value = "" And cmbProjectName = "" And cmbTaskName.Value = "" And cmbTaskStatus.Value = "" Then
    MsgBox "Select respective item's"
    
    Exit Sub
    
Else


''case 2
'If cmbActivity.Value = "Non-Core" And ComboBox1.Value = "" Then
'    MsgBox "Select sub non-core activity..!!"
'Else


If cmbActivity.Value = "Non-Core" And _
TxtCaseID.Value = "" And TxtEETime.Value = "" And cmbProjectName = "" And cmbTaskName.Value = "" And cmbTaskStatus.Value = "" Then


    
    cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\group$\WNA\Timesheet_Data.accdb"
    Cn.Open cs
    rs1.Open "Table1", Cn, adOpenDynamic, adLockOptimistic
    
    With rs1
    .AddNew
    .Fields("Start Date/Time") = Me.txtstartdate
    .Fields("Closed Date/Time") = DateTime.Now
    .Fields("Activity Type") = Me.cmbActivity
    .Fields("Sub Type") = Me.ComboBox1
    .Fields("Comments") = Me.txtcomm
    .Fields("User name") = Me.LblUname
    .Update
    End With
    
    Set rs1 = Nothing
    Cn.Close
    Set Cn = Nothing
    
    txtstartdate.Value = ""
    cmbActivity.Value = ""
    ComboBox1.Value = ""
    cmbTaskStatus.Value = ""
    txtcomm.Value = ""
    
    MsgBox "Details Updated"
 
Else


''case 3
'If cmbActivity.Value = "Core" And ComboBox1.Value = "" Then
'    MsgBox "Select sub core activity..!!"
'Else
'If cmbActivity.Value = "Core" And (TxtCaseID.Value = "" Or TxtEETime.Value = "" Or _
'cmbProjectName = "" Or cmbTaskName.Value = "" Or cmbTaskStatus.Value = "") Then
'    MsgBox "Select respective item's"
'Else


    cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\group$\WNA\Timesheet_Data.accdb"
    Cn.Open cs
    rs1.Open "Table1", Cn, adOpenDynamic, adLockOptimistic
    
    With rs1
    .AddNew
    .Fields("Start Date/Time") = Me.txtstartdate
    .Fields("Closed Date/Time") = DateTime.Now
    .Fields("Activity Type") = Me.cmbActivity
    .Fields("Sub Type") = Me.ComboBox1
    .Fields("Case ID") = Me.TxtCaseID
    .Fields("EE Tool TimeDate") = Me.TxtEETime
    .Fields("Project Name") = Me.cmbProjectName
    .Fields("Task Name") = Me.cmbTaskName
    .Fields("Task Status") = Me.cmbTaskStatus
    .Fields("Comments") = Me.txtcomm
    .Fields("User name") = Me.LblUname
    .Update
    End With
    
    Set rs1 = Nothing
    Cn.Close
    Set Cn = Nothing
    
    txtstartdate.Value = ""
    cmbActivity.Value = ""
    ComboBox1.Value = ""
    TxtCaseID.Value = ""
    TxtEETime.Value = ""
    cmbProjectName.Value = ""
    cmbTaskName.Value = ""
    cmbTaskStatus.Value = ""
    txtcomm.Value = ""
    
    MsgBox "Details Updated"
    
End If


Call UserForm_Initialize


End If






Application.ScreenUpdating = True
End Sub