If Statement question

[jaad]

I have a very easy one for you guys:

I have a checkbox that is called [ProjectDetailAc tive]
it's default value is "true"

I would like to insert a code that would make it impossible to turn the checkbox to false if another field on the same form[ProjectDetailEn dDate] = null

A side Comment:
I don't know how you guys manage to find the correct statement for these codes. Where do you guys learn this stuff? I've read articles, books, visited web sites but nowhere yet in two years have I found a place that teach how to write codes - they only give you an examples on how a code might read for a function that I would never use in a million year and never close to what I need. is there like a secret Harry Potter school where they do teach you how to write codes?

I've asked this question hundreds of times all over the internet and nobody seem to know. All trial and error I hear all the time- is that true? there is nowhere to learn how to write codes? I'm doomed :o)

[patjones]

Hi jaad,

I would suggest using the After Update event for ProjectDetailEn dDate in conjunction with the "Enabled" property for ProjectDetailAc tive. In particular, you can perform an If test inside the After Update event and set the checkbox Enabled property to true or false depending on what the result of the test is.

In terms of learning how to code...you learn by teaching yourself general principles, and then applying those principles to your particular problem, often incorporating trial and error. If you go to sources of information looking for the exact code to solve your problem, your search will almost always be futile.

Pat

[jaad]

This is the code that I've picked up from a You tube video and adapted it to my need.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If ([PDEndDate] = Null) And ([PDActive] = False) Then
    MsgBox ("You cannot deactivate a record without first entering an End Date to the project")
    Cancel = True

End If
End Sub

Why would this not work?

I've tried to place it into the [PDActive] beforeUpdate and AfterUPdate and OnClick and none work.

[patjones]

What happens when you try to use it? Does it produce an error? Is there some sort of strange behavior? You need to explain more clearly what's happening.

Pat

[jaad]

Nothing happens. It's like it wasn't even there.

[patjones]

I tested this out and it seems to work fine:

Private Sub PDActive_BeforeUpdate(Cancel As Integer)
    
If ([PDEndDate] = Null) And ([PDActive] = True) Then
    MsgBox ("You cannot deactivate a record without first entering an end date for the project.")
    Cancel = True
End If

End Sub

Pat

[jaad]

I just copy and paste the code into the before update of PDActive and nothing happens. again its like nothing was there.

[patjones]

Are the controls actually named PDEndDate and PDActive? In your original post you referred to them as ProjectDetailEn dDate and ProjectDetailAc tive respectively.

Pat

[jaad]

yes they are named PDActive and PDEndDate I renamed them in the table in order to make it easier to write and they all have the same name throughout with queries,forms and report and everything works fine.

[pod]

The Null value can be very time consuming for beginners, ... and it still plays tricks on me :) but I learn a lot from errors in the code, so will you. Patience and time are required parameters for developers/programmers/coders.

zepphead80 suggestion is best

Try something like this:

Private Sub Check2_AfterUpdate()
    Dim val As Variant
    val = Trim(Me.Text0.Value)
    If IsNull(val) Or val = "" Or Len(val) = 0 Or Not IsDate(val) Then
        Me.Check2 = False
    End If
End Sub

[jaad]

Thanks for jumping-in Pod,

As you mentioned yourself "I am a beginner" and I unfortunately don't know what to do with the code that you wrote????? Where would I put that code? I don't have any control called Check2.


It's never easy to explain these thing but let me try again:

I have a form in which I can view and edit records for a Project table. This form's source is a query that filters out all records that are not active or [PDActive].

All project should have a starting date and an end date[PDEndDate] Once someone has worked with the record, added info and taken the project to completion you would then write the date at which you finished the project and then click the checkbox [PDActive] to deactivate the record so it doesn't show up in the form anymore.

The problem that I have is sometime people forget to write the end date [PDEndDate] and deactivate the project by clicking [PDActive].

What that does is I have to go back to the table and manually enter the date at which the project was close for every record that the end date wasn't entered. it also creates holes into my reports as I need that end date to show when the project was finalized and done with.

[pod]

For starters, let's put in the right names for the fields and correct the desired value for the checkbox, (I had it reversed)

Private Sub [B]PDActive_AfterUpdate[/B]() 
    Dim val As Variant 
    val = Trim(Me.[B]PDEndDate[/B].Value) 
    If IsNull(val) Or val = "" Or Len(val) = 0 Or Not IsDate(val) Then 
        Me.[B]PDActive [/B]= True
    End If 
End Sub

[jaad]

IT WORKS!!!!!!!!!! !!!!!!!!!!!!!!! !!!!

Thank you so much Pod... I have been trying to figure this one out for so long.

Can I put a msgbox()line before End iF that would read why people can't close the record?

[pod]

bien sur! You can also set the focus on the date field put in the same message in a msgbox and the datefield (which will disapear when the user clicks in the PDEndDate text box)

Private Sub PDActive_AfterUpdate()
    Dim val As Variant
    val = Trim(Me.PDEndDate.Value)
    If IsNull(val) Or val = "" Or Len(val) = 0 Or Not IsDate(val) Then
        Me.PDActive = False
        MsgBox "End date required"
        Me.PDEndDate.Value = "End date required"
        Me.PDEndDate.SetFocus
    End If
End Sub

Private Sub PDEndDate_Click()
    If Me.PDEndDate.Value = "End date required" Then
        Me.PDEndDate.Value = ""
    End If
End Sub