If Null Error 2

[janetlyn]

I have a bound date textbox and a bound checkmark on a form. If someone types in a date and then tries to put in a checkmark on the same record, a message should come up telling them they can't do that and the same if they have a checkmark in and try to add a date on the same record. My code is not doing what I want.

'If a checkmark is selected, it checks to make sure Date is null; if not shows message.
Private Sub NoDate_LostFocus()
If Me!Date <> Null Then
MsgBox "A date has been put in above. Verify that there really is not a date for this section. If there is no date, you must erase the date above before putting in a check mark for no date."
End If
End Sub

'If a date is entered, it checks to make sure NoDate is null; if not shows message.
Private Sub Date_LostFocus()
If Me!NoDate <> Null Then
MsgBox "You must take out the check mark from 'No Date Shown' before you can enter a date here."
End If
End Sub

Can someone please tell me why it is not working? Thanks for the help, JL

 

[mgolla]

Have you tried this?

Private Sub NoDate_LostFocus()
    If [red]Not IsNull([/red]Me!Date[red])[/red] Then
        MsgBox "A date has been put in above.  Verify that there really is not a date for this section.  If there is no date, you must erase the date above before putting in a check mark for no date."
    End If
End Sub


Private Sub Date_LostFocus()
    If [red]Not IsNull([/red]Me!NoDate[red])[/red] Then
        MsgBox "You must take out the check mark from 'No Date Shown' before you can enter a date here."
    End If
End Sub

HTH
Mike

Dooobie...Doobie......Dooo

 

[KenReay]

Hi

Null is a strange beast, it is not equal to anything including itself !

try the IsNull() function so:

If Not IsNull(Me!Date) Then


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[janetlyn]

Thank you both. That did work. However, once the message came up and you select OK, it allows you to proceed even with info in both. So, how would I tell it to give the message if someone tries to enter info when there is data in the other box and not allow input until the data is taken out? Thanks for the help, JL

 

[janetlyn]

Also, I noticed when I am entering data now, that when I "Enter"-key past the date to put a checkmark in the box, it gives me the "You can't have a checkmark in the box" message even though there is not a checkmark in the box. Ideas?

 

[KenReay]

Hi

I would say you should put the code in the before update event of the control. This event has a cancel option so

Private Sub NoDate_BeforeUpdate(Cancel As Integer)
If Not IsNull(Me!Date) Then
Cancel = True
MsgBox "A date has been put in above. Verify that there really is not a date for this section. If there is no date, you must erase the date above before putting in a check mark for no date."
End If
End Sub


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[janetlyn]

Okay, I changed the code. Went to a new record. Typed in data for two textboxes and get to the date textbox and type in a date. When I hit enter, tab, "go-to-code" icon, anything, it gives me the

"You must take out the check mark from 'No Date Shown' before you can enter a date here." message and will not let me out of it. I finally just had to close the screen.

I have checked my names and code for spelling errors and could not find a problem there. Do you see anything obvious wrong?

Thank again Ken, JL

 

[mgolla]

Janet,

Do you still have the code on Lost Focus of Date field?

Try changing it to:

Private Sub Date_LostFocus()
    If Not IsNull(Me!NoDate) Or Me!NoDate = True Then
        MsgBox "You must take out the check mark from 'No Date Shown' before you can enter a date here."
    End If
End Sub

Just incase the value is set to false instead of a null value


HTH
Mike

Dooobie...Doobie......Dooo

 

[janetlyn]

No, I moved it to before update as suggested by Ken.

If I go to a record already created that has the date in it, and try to check the box it gives me the proper message and allows me to continue.

When I try putting data in a new record, when I get to date and type in the date, then enter to go to the checkbox, if gives me the message that I cannot have the checkbox checked. And, it locks me in and no matter where I try to go, it just keeps giving me that same message.

So we took one step forward and one step back. Anymore ideas please? JL

 

[LarryDeLaruelle]

Private Sub Date_LostFocus()
If Me!NoDate Then
MsgBox "You cannot enter a date with 'No Date' checked."
Date = Null
NoDate.SetFocus
End If
End Sub

Try this for the NoDate test.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[janetlyn]

Larry, the code did not work. May I ask if the "If" statement is correct, because it does not say anything like Me!NoDate = or Me!NoDate IsNull. If it is correct, what if the IF line signifying.

Thanks for the help, JL

 

[LarryDeLaruelle]

I probably should have use the value.

What I put up was short hand; if you are testing for a true value it does not need to be stated (it is assumed); so, If Me!NoDate Then is the same as If Me!NoDate = True Then.

You're right that I did miss the date test. How's this:

If Me!NoDate = True And Not IsNull(Date) Then
MsgBox "You cannot enter a date with 'No Date' checked."
Date = Null
NoDate.SetFocus
End If

I assumed that NoDate was the check box and that if the user entered a value in Date when the check box value is true, you want to prevent that from happening.

The code tests NoDate for true and for any value in Date. If both conditions are met the user is prompted, the value of date is to Null/Empty and the focus is set to the NoDate check box.

Personally, I would use After Update events because it will trigger only when a change has been made. Using Lost Focus will trigger the code whenever the user tabs to/off the field.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[KenReay]

Hi

Having the code in before update is preferable to lost focus

We need to see the code as you have it now

also can I ask, have you tried the simple expedient of using the debugger to step through the code and inspect the variables to determine which of the values in the If is causing it to behave differently to expected

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[janetlyn]

Below is the code. When I go to test the code, if gives me the following error: "The expression After Update you entered as the event property setting produced the following error: Procedure declaration does not match description of event or procedure having the same name."

I have verified names, and that [Event Procedure] is in the After Update of both textboxes. I was not able to step through the code because of the error. Larry, you are correct in your assumptions.

Private Sub Date_AfterUpdate(Cancel As Integer)
If Me!Date = True And Not IsNull(NoDate) Then
MsgBox "A date has already been entered."
Date = Null
NoDate.SetFocus
End If
End Sub


Private Sub NoDate_AfterUpdate(Cancel As Integer)
If Me!NoDate = True And Not IsNull(Date) Then
MsgBox "A date has already been entered."
Me!NoDate = Null
Me!Date.SetFocus
End If
End Sub


Thank you both for working on this problem. Janet Lyn

 

[Edski]

janet,
The afterUpdate Event does NOT have a Cancel argument. I suspect you put it in yourself.
Cheeers

 

[KenReay]

Hi

you need BEFORE UPDATE not AFTER UPDATE

From my earlier post

Private Sub NoDate_BeforeUpdate(Cancel As Integer)
If Not IsNull(Me!Date) Then
Cancel = True
MsgBox "A date has been put in above. Verify that there really is not a date for this section. If there is no date, you must erase the date above before putting in a check mark for no date."
End If
End Sub


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[janetlyn]

Okay, it works on the Date but not the NoDate. So if I have a checkmark and try to put in a date, it gives me the message, allows me to erase the date, take out the checkmark and then enter date. If I have a date entered and put in a checkmark, it allows that with no message. I am not good with debugging so I use a suggested Msgbox method. It does get to the code but does not go beyond the If statement. The code that is not working is listed below first (with all my debugging Message boxes) and the code that is working is below that. I do not see any differences that would make one work and not the other. However, I do believe it must have something to do with it being a checkbox as to why it is not responding to the code.

CODE NOT WORKING
Private Sub NoDate_BeforeUpdate(Cancel As Integer)
MsgBox "I am at the code"
If Me!Date = True And Not IsNull(NoDate) Then
MsgBox "Date is true and NoDate has a checkmark"
Cancel = True
MsgBox "Cancel the true"
MsgBox "A date has been put in above. Verify that there really is not a date for this section. If there is no date, you must erase the date above before putting in a check mark for no date."
End If
End Sub

CODE WORKING
Private Sub Date_BeforeUpdate(Cancel As Integer)
If Me!NoDate = True And Not IsNull(Date) Then
Cancel = True
MsgBox "Backspace to take out date, take out No Date checkmark, and then input date."
End If
End Sub

Thanks for being patient Ken. Janet Lyn

 

[KenReay]

Hi

I think your problem may be that you are testing the check box for null. By default a check box is two states true or false, so unless you have amended the properties of the check box to make it tripple state True/FAlse/Null, it will never be null, plus you are testing date as if it is a boolean, surely it is a date/time?

Private Sub NoDate_BeforeUpdate(Cancel As Integer)
If Not IsNull(Me!Date) And NoDate = True Then
Cancel = True
MsgBox "A date has been put in above. Verify that there really is not a date for this section. If there is no date, you must erase the date above before putting in a check mark for no date."
End If
End Sub



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[janetlyn]

Ken, thank you so much. I figured out about the checkbox never in the Null state, but still didn't have a clue what to do about it. Thanks for taking the time to help me. Works perfectly. Pink star for you, Janet Lyn