are my if statements clashing?

H

hoda64

Registered User.
Local time
Today, 09:10
Joined
Jun 18, 2008
Messages
30
Hi everyone,

I am having trouble getting my code to work. I have a form that has 5 checkboxes and under each of these 5 checkboxes there is a field where I want today's date to be placed when the checkbox is clicked and nulled when the checkbox is unclicked, but for some reason nothing happens in the date field when the checkbox is clicked. Here is my code:

Code: 
Private Sub chkCSDAB_AfterUpdate()
If chkCSDAB Then
    csdabapprovedate = Date
Else
    csdabapprovedate = Null
End If
clickconditions
End Sub
Private Sub chkLB_AfterUpdate()
If chkLB Then
    SFLSApproveDate = Date
Else
    SFLSApproveDate = Null
End If
clickconditions
End Sub
Private Sub chkPM_AfterUpdate()
If chkPM Then
    PMApproveDate = Date
Else
    PMApproveDate = Null
End If
clickconditions
End Sub
Private Sub chkSMMB_AfterUpdate()
If chkSMMB Then
    smmbapprovedate = Date
Else
    smmbapprovedate = Null
End If
clickconditions
End Sub
Private Sub chkTCB_AfterUpdate()
If chkTCB Then
    tcbapprovedate = Date
Else
    tcbapprovedate = Null
End If
clickconditions
End Sub

This is the function clickconditions:

Code: 
Private Sub clickconditions()
Dim intPress As Integer
intPress = MsgBox("Are you sure you want to make this update?", vbOKCancel + vbQuestion, "Continue?")
If intPress = vbOK Then
If Not chkPM Then
    Forms!schedview!SchedStatus = "Unapproved-PM"
    FormHeader.BackColor = 128
Else
    If chkPM.Visible And Not chkLB.Visible And Not chkTCB.Visible And Not chkSMMB.Visible And Not chkCSDAB.Visible Then
        If chkPM Then
                    Forms!schedview!SchedStatus = "Approved"
                    FormHeader.BackColor = 32768
                    'PMApproveDate = Date
        Else
                    Forms!schedview!SchedStatus = "Unapproved-PM"
                    FormHeader.BackColor = 128
                    'PMApproveDate = Null
        End If
    End If
    
    If chkPM.Visible And chkLB.Visible And Not chkTCB.Visible And Not chkSMMB.Visible And Not chkCSDAB.Visible Then
        If chkPM And chkLB Then
                    Forms!schedview!SchedStatus = "Approved"
                    FormHeader.BackColor = 32768
                    'PMApproveDate = Date
        End If
        If Not chkPM And chkLB Then
                    Forms!schedview!SchedStatus = "Unapproved-PM"
                    FormHeader.BackColor = 128
'                    PMApproveDate = Null
'                    SFLSApproveDate = Date
        End If
        If chkPM And Not chkLB Then
                    Forms!schedview!SchedStatus = "Unapproved-LB"
                    FormHeader.BackColor = 128
'                    PMApproveDate = Null
'                    SFLSApproveDate = Null
        End If
    End If
    If chkSMMB.Visible And chkTCB.Visible And chkCSDAB.Visible And chkLB.Visible Then
    
        If Not chkSMMB And chkCSDAB And chkTCB And chkLB Then 'smmb is not checked
                Forms!schedview!SchedStatus = "Unapproved-SMMB"
                FormHeader.BackColor = 128
'                smmbapprovedate = Null
'                csdabapprovedate = Date
'                tcbapprovedate = Date
'                SFLSApproveDate = Date
            
        End If
        If Not chkLB And chkSMMB And chkCSDAB And chkTCB Then 'lb is not checked
                Forms!schedview!SchedStatus = "Unapproved-LB"
                FormHeader.BackColor = 128
'                SFLSApproveDate = Null
'                smmbapprovedate = Date
'                csdabapprovedate = Date
'                tcbapprovedate = Date
        End If
        If Not chkCSDAB And chkSMMB And chkTCB And chkLB Then 'csdab is not checked
                Forms!schedview!SchedStatus = "Unapproved-CSDAB"
                FormHeader.BackColor = 128
'                csdabapprovedate = Null
'                smmbapprovedate = Date
'                tcbapprovedate = Date
'                SFLSApproveDate = Date
        End If
        
        If Not chkTCB And chkSMMB And chkCSDAB And chkLB Then 'tcb is not checked
                Forms!schedview!SchedStatus = "Unapproved-TCB"
                FormHeader.BackColor = 128
'                tcbapprovedate = Null
'                smmbapprovedate = Date
'                csdabapprovedate = Date
'                SFLSApproveDate = Date
        End If
        
        If Not chkTCB And Not chkSMMB And Not chkCSDAB And chkLB Then
                Forms!schedview!SchedStatus = "Unapproved-TRD" 'none of the trd branch checked
                FormHeader.BackColor = 128
'                tcbapprovedate = Null
'                smmbapprovedate = Null
'                csdabapprovedate = Null
'                SFLSApproveDate = Date
        End If
        
        If chkSMMB And Not chkCSDAB And Not chkTCB And chkLB Then
                Forms!schedview!SchedStatus = "Unapproved-TRD" 'csdab and tcb do not check
                FormHeader.BackColor = 128
'                smmbapprovedate = Date
'                csdabapprovedate = Null
'                tcbapprovedate = Null
'                SFLSApproveDate = Date
        End If
        
        If chkCSDAB And Not chkSMMB And Not chkTCB And chkLB Then
                Forms!schedview!SchedStatus = "Unapproved-TRD" 'smmb and tcb do not check
                FormHeader.BackColor = 128
'                csdabapprovedate = Date
'                smmbapprovedate = Null
'                tcbapprovedate = Null
'                SFLSApproveDate = Date
        End If
        
        If chkTCB And Not chkSMMB And Not chkCSDAB And chkLB Then
                Forms!schedview!SchedStatus = "Unapproved-TRD" 'smmb and csdab do not check
                FormHeader.BackColor = 128
'                tcbapprovedate = Date
'                smmbapprovedate = Null
'                csdabapprovedate = Null
'                SFLSApproveDate = Date
        End If
        If chkTCB And chkSMMB And chkCSDAB And chkLB Then 'all check
                Forms!schedview!SchedStatus = "Approved"
                FormHeader.BackColor = 32768
'                tcbapprovedate = Date
'                smmbapprovedate = Date
'                csdabapprovedate = Date
'                SFLSApproveDate = Date
        End If
    End If
        
    If Not chkCSDAB.Visible And chkSMMB.Visible And chkTCB.Visible And chkLB.Visible Then
'only lb smmb and tcb are visible
            If Not chkSMMB And chkTCB And chkLB Then 'smmb does not check
                    Forms!schedview!SchedStatus = "Unapproved-SMMB"
                    FormHeader.BackColor = 128
'                    smmbapprovedate = Null
'                    tcbapprovedate = Date
'                    SFLSApproveDate = Date
            End If
    
            If chkSMMB And Not chkTCB And chkLB Then 'tcb does not check
                    Forms!schedview!SchedStatus = "Unapproved-TCB"
                    FormHeader.BackColor = 128
'                    smmbapprovedate = Date
'                    tcbapprovedate = Null
'                    SFLSApproveDate = Date
            End If
            If Not chkLB And chkTCB And chkSMMB Then 'lb does not check
                    Forms!schedview!SchedStatus = "Unapproved-LB"
                    FormHeader.BackColor = 128
'                    SFLSApproveDate = Null
'                    tcbapprovedate = Date
'                    smmbapprovedate = Date
            End If
            If chkSMMB And chkTCB And chkLB Then 'all check
                    Forms!schedview!SchedStatus = "Approved"
                    FormHeader.BackColor = 32768
'                    smmbapprovedate = Date
'                    tcbapprovedate = Date
'                    SFLSApproveDate = Date
            End If
     End If
    If Not chkSMMB.Visible And chkCSDAB.Visible And chkTCB.Visible And chkLB.Visible Then
'only csdab and tcb are visible
            If Not chkCSDAB And chkTCB And chkLB Then 'csdab does not check
                    Forms!schedview!SchedStatus = "Unapproved-CSDAB"
                    FormHeader.BackColor = 128
'                    csdabapprovedate = Null
'                    tcbapprovedate = Date
'                    SFLSApproveDate = Date
            End If
    
            If chkCSDAB And Not chkTCB And chkLB Then 'tcb does not check
                Forms!schedview!SchedStatus = "Unapproved-TCB"
                FormHeader.BackColor = 128
'                csdabapprovedate = Date
'                tcbapprovedate = Null
'                SFLSApproveDate = Date
            End If
            If chkCSDAB And chkTCB And Not chkLB Then 'lb does not check
                Forms!schedview!SchedStatus = "Unapproved-LB"
                FormHeader.BackColor = 128
'                csdabapprovedate = Date
'                tcbapprovedate = Date
'                SFLSApproveDate = Null
            End If
            If chkCSDAB And chkTCB And chkLB Then 'all check
                Forms!schedview!SchedStatus = "Approved"
                FormHeader.BackColor = 32768
'                csdabapprovedate = Date
'                tcbapprovedate = Date
'                SFLSApproveDate = Date
            End If
    End If
    If Not chkTCB.Visible And chkCSDAB.Visible And chkSMMB.Visible And chkLB.Visible Then
'only csdab and smmb are visible
            If Not chkCSDAB And chkSMMB And chkLB Then 'csdab does not check
                Forms!schedview!SchedStatus = "Unapproved-CSDAB"
                FormHeader.BackColor = 128
'                csdabapprovedate = Null
'                smmbapprovedate = Date
'                SFLSApproveDate = Date
            End If
    
            If chkCSDAB And Not chkSMMB And chkLB Then
                Forms!schedview!SchedStatus = "Unapproved-SMMB" 'smmb does not check
                FormHeader.BackColor = 128
'                csdabapprovedate = Date
'                smmbapprovedate = Null
'                SFLSApproveDate = Date
            End If
            If chkCSDAB And chkSMMB And Not chkLB Then 'lb does not check
                Forms!schedview!SchedStatus = "Unapproved-LB"
                FormHeader.BackColor = 128
'                csdabapprovedate = Date
'                smmbapprovedate = Date
'                SFLSApproveDate = Null
            End If
            If chkCSDAB And chkSMMB And chkLB Then 'all check
                Forms!schedview!SchedStatus = "Approved"
                FormHeader.BackColor = 32768
'                csdabapprovedate = Date
'                smmbapprovedate = Date
'                SFLSApproveDate = Date
            End If
    End If
            
    If chkSMMB.Visible And Not chkCSDAB.Visible And Not chkTCB.Visible And chkLB.Visible Then
            If chkSMMB And chkLB Then 'of the three only SMMB can be clicked
                Forms!schedview!SchedStatus = "Approved"
                FormHeader.BackColor = 32768
'                smmbapprovedate = Date
'                SFLSApproveDate = Date
            End If
            If chkSMMB And Not chkLB Then
                Forms!schedview!SchedStatus = "Unapproved-LB" 'lb does not check
                FormHeader.BackColor = 128
'                smmbapprovedate = Date
'                SFLSApproveDate = Null
            End If
            If Not chkSMMB And chkLB Then
                Forms!schedview!SchedStatus = "Unapproved-SMMB" 'smmb does not check
                FormHeader.BackColor = 128
'                smmbapprovedate = Null
'                SFLSApproveDate = Date
            End If
    End If
    
    If chkCSDAB.Visible And Not chkSMMB.Visible And Not chkTCB.Visible And chkLB.Visible Then
            If chkCSDAB And chkLB Then 'of the three only CSDAB can be clicked
                Forms!schedview!SchedStatus = "Approved"
                FormHeader.BackColor = 32768
'                csdabapprovedate = Date
'                SFLSApproveDate = Date
            End If
            If Not chkCSDAB And chkLB Then
                Forms!schedview!SchedStatus = "Unapproved-CSDAB" 'csdab does not check
                FormHeader.BackColor = 128
'                csdabapprovedate = Null
'                SFLSApproveDate = Date
            End If
            If chkCSDAB And Not chkLB Then
                Forms!schedview!SchedStatus = "Unapproved-LB" 'lb does not check
                FormHeader.BackColor = 128
'                csdabapprovedate = Date
'                SFLSApproveDate = Null
            End If
    End If
    
    If chkTCB.Visible And Not chkSMMB.Visible And Not chkCSDAB.Visible And chkLB.Visible Then
            If chkTCB And chkLB Then 'of the three only TCB can be clicked
                Forms!schedview!SchedStatus = "Approved"
                FormHeader.BackColor = 32768
'                tcbapprovedate = Date
'                SFLSApproveDate = Date
            End If
            If chkTCB And Not chkLB Then
                Forms!schedview!SchedStatus = "Unapproved-LB" 'lb does not check
                FormHeader.BackColor = 128
'                tcbapprovedate = Date
'                SFLSApproveDate = Null
            End If
            If Not chkTCB And chkLB Then
                Forms!schedview!SchedStatus = "Unapproved-TCB" 'tcb does not check
                FormHeader.BackColor = 128
'                tcbapprovedate = Null
'                SFLSApproveDate = Date
            End If
    End If
            
End If
End If

As you can see I have commented out other bits of code in the clickconditions function because when they were there, it gives me a runtime error 2448 "you cant assign a value to this object" and because I am not completely done writing them yet.

Any help will be greatly appreciated, thanks in advance
 
When you step through it what line is it zonking on?
 
Well with the version that I posted, there are no errors so I dont think any of the lines are giving it an issue, I think there might be some sort of logical inconsistency.

But when I un-comment those bits of code in the clickconditions function it dosent like this line:

SFLSApproveDate = Date

I am also a little confused because access dosent seem to recognize my tcb, smmb, and csdab approvedates, even though I am sure they are in the table.

Also, I comment out the if and else statements in the after update functions, just to let you know, whenever the clickconditions functions has the code uncommented.
 
So if you take call to 'clickconditions' out of the 'chkCSDAB_AfterUpdate' event the rest of the code does not work?
 
When the event looks like this:

Code: 
Private Sub chkCSDAB_AfterUpdate()
If chkCSDAB Then
    csdabapprovedate = Date
Else
    csdabapprovedate = Null
End If
'clickconditions
End Sub

When I click on the CSDAB checkbox on the form, nothing happens.
 
Ok, so I changed my code and instead of using the clickconditions I am going to write something different in each of the after update subfunctions.

Code: 
Private Sub chkLB_AfterUpdate()
If chkLB Then
    If MsgBox("Are you sure you want to make this update?", vbOKCancel) = vbOK Then
        SFLSApproveDate = Date
        
        If Forms!schedview!CritNeed = True Or Forms!schedview!ShldNeed = True Then
            CSDABon
        End If
        
        If Forms!schedview!StrucNeed = True Or Forms!schedview!MatlNeed = True Then
            SMMBon
        End If
        
        If Forms!schedview!ThrmNeed = True Or Forms!schedview!ContNeed = True Then
           TCBon
        End If
    Else
        chkLB = False
    End If
Else
    If MsgBox("Are you sure you want to continue?", vbOKCancel) = vbOK Then
        tcbapprovedate = Null
        smmbapprovedate = Null
        csdabapprovedate = Null
        
        chkSMMB = False
        chkCSDAB = False
        chkTCB = False
        
        CSDABoff
        SMMBoff
        TCBoff
        
        If Forms!schedview!SchedStatus = "Approved" Then
            SchedRev = SchedRev + 1
            RevDate = Null
        End If
        
        Forms!schedview!SchedStatus = "Unapproved-LB"
    Else
        chkLB = True
    End If
End If
End Sub
Private Sub chkPM_AfterUpdate()
Dim intpress As Integer
If chkPM Then
    intpress = MsgBox("Are you sure you want to make this update?", vbOKCancel + vbQuestion, "Continue?")
        If intpress = vbOK Then
            PMApproveDate = Date
                LBon
                    
            If Forms!schedview!CritNeed = True Or Forms!schedview!ShldNeed = True Then
            
                CSDABon
            End If
            
            If Forms!schedview!StrucNeed = True Or Forms!schedview!MatlNeed = True Then
            
                SMMBon
            End If
            
            If Forms!schedview!ThrmNeed = True Or Forms!schedview!ContNeed = True Then
            
                TCBon
            End If
        Else
            chkPM.Value = False
        End If
Else
    If MsgBox("Are you sure you want to continue? Removing PM approval removes all branch approvals", vbOKCancel) = vbOK Then
        
        chkLB = False
        chkSMMB = False
        chkCSDAB = False
        chkTCB = False
        
        IsNull (SFLSApproveDate)
        tcbapprovedate = Null
        smmbapprovedate = Null
        csdabapprovedate = Null
        PMApproveDate = Null
                
        CSDABoff
        SMMBoff
        TCBoff
        LBoff
        
        If Forms!schedview!SchedStatus = "Approved" Then
            SchedRev = SchedRev + 1
            RevDate = Null
        End If
        
        Forms!schedview!SchedStatus = "Unapproved-PM"
    Else
        chkPM = True
    End If
End If
    
End Sub

To answer your question Ken, it is in the click event of "After Update".

My problem now is that it does not instantly put in today's date into the PMApproveDate field or the SFLSApproveDate field. When I close the form and come back to it, its there.

Also, it does not null the dates. Am I calling the field to null incorrectly?

Thanks, I appreciate your help
 
You must log in or register to reply here.

Similar threads

C
Tricky query Criteria, between dates if textboxes are null
Replies
6
Views
1,705
Galaxiom
G
C
IF Then Else VBA Access form
Replies
4
Views
1,414
cardonas
C
T
A Little Help With My IF Statements Please
Replies
3
Views
514
jdraw
jdraw
C
Conditional formatting and If statements in user form
Replies
11
Views
4,288
pbaldy
pbaldy
A
IF statement to test Null values in 2 different forms
2
Replies
28
Views
2,493
Anwar1968
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom