Results 1 to 7 of 7
  1. #1
    Join Date
    May 2006
    Posts
    386

    Unanswered: Can someone help please? MsgBox does not fully work

    I have finally managed to create a Message Alert on the Click Event Procedure where IF an Employee is Not Qualified the user should be given a warning message. It works fine but only if the Yes/No field is 0 and not No

    When I click on the employee name and if his/her qualification is selected as No the the message does not appear. However, if nothing is selected i.e. neither Yes Nor No then I get no message.

    Basically I want the MsgBox to flag for both options ie. No and 0

    This is the code I have used, am I missing anything here? Any help would be much appreciated

    Private Sub InterpreterID_Click()
    Debug.Print "InterpreterID_Click"

    If Form_Booking.Qualified.Value = False Or 0 Then
    'If Form_Booking.Qualified.Value = No Then
    MsgBox "This Employee is Not Qualified"
    Exit Sub

    End If
    End Sub
    Many thanks.
    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    How about this:

    If isnull(me!Qualified) then
    msgbox "Qualified has no value!"
    exit sub
    end if
    if me!Qualified = False Or me!Qualified = 0 Then
    'If Form_Booking.Qualified.Value = No Then
    MsgBox "This Employee is Not Qualified"
    Exit Sub

    End If
    End Sub

    Also keep in mind that you may want to also do this kind of check in the OnCurrent event (of the form itself) versus just the OnClick event of the field. You may also need a "refresh" command in the code above (it would be the 1st line of code.)
    Last edited by pkstormy; 09-17-09 at 21:27.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    MsgBox does not fully work
    LOL!

    I always laugh when someone complains that there is something about access that doesn't work properly... like it's somehow Access' fault when some code doesn't work

    Sorry, but I just find it funny!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    May 2006
    Posts
    386
    Hi StarTrekker,
    On reflection, you are right, my thread does sound as if I am accusing access of not working properly, in fact i was seeking help whether I am missing anything in the code.

    I will give Pkstormy's advice and see what happens.
    Emi-UK
    Love begets Love, Help Begets Help

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    How about explicitly convert (cast) the value of the control?
    (here assuming that Null, if allowed, yields zero)
    Code:
    If CBool(Nz(Me!Qualified , 0)) = False
    Have a nice day!

  6. #6
    Join Date
    May 2006
    Posts
    386
    I still have the same issue. I used the code and it works IF nothing is selected/ticked (meaning, when I enter new employee on the DB and I dont select "No" against the Qualified field, then when I click on the employee name, no message box appears.

    However, IF I leave the Qualified field blank and choose nothing (null) it works.

    I want the MsgBox to appear IF the Qualified field isNull and No but at the moment it only appears IF the Qualified field isnull.

    Any help would be much appreciated
    Emi-UK
    Love begets Love, Help Begets Help

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Again, VERY IMPORTANT - If your Qualified field is a Yes/No field type, your test against a null value isn't really doing anything and your either getting the message box due to it being a False (0) or True (-1) value (or you've setup an option group trying to save a null value to a Yes/No field type which is just going to cause you problems.) You're not really testing against a null value being saved to the Yes/No field! For example, even if you had code which forcibly tries to "set" the Qualified to Null, it's NOT setting it to null but instead False. (try designing a query showing all records where Qualified is null).

    If the Qualified field is a Yes/No field type, it again, usually defaults to No for new records. Thus, when you enter a new record (and an autonumber value is created), when you do your test against the Qualified field you'll most likely get the message that Qualified is No since you're testing against either a True or False (Yes or No) value on a record (and null isn't a possibility!)

    You'll only keep confusing yourself by testing for a blank isnull value on a Yes/No field. You NEED to understand that a Yes/No field only has 2 values, Yes (True or -1) or No (False or 0) which is stored in the table. If you're trying to manipulate and store a null value by using an option group, combobox or other method for the Qualified field on the form, it won't work and what you "think" are null values being stored in the table are really False values!!

    You need to change your Qualified field type to an integer field type if you want to test against null values. You can't do it for a Yes/No field type!

    Hence...
    Quote Originally Posted by Emal
    I still have the same issue. I used the code and it works IF nothing is selected/ticked (meaning, when I enter new employee on the DB and I dont select "No" against the Qualified field, then when I click on the employee name, no message box appears.
    Reason - You're qualified field is probably False (0 or No) since this is the default value for new records.
    Quote Originally Posted by Emal
    However, IF I leave the Qualified field blank and choose nothing (null) it works.
    Reason - Again, Qualified would not return a null value and it's either 0 or False where your code to test for =False or =0 or =No is firing, not the isnull. Again, be careful because if you're using an option box or some other method to try and test against a Null value, you "may" be only testing against that option box and not the REAL underlying value in the table.
    Quote Originally Posted by Emal
    I want the MsgBox to appear IF the Qualified field isNull and No but at the moment it only appears IF the Qualified field isnull.
    Reason - NO - Testing against a Null value on a Yes/No field won't give you the results you want since again, Yes/No fields have either a Yes or No value (not a null value.) I'll repeat, be careful about using an option box or some other method to try and test/write Null values for a Yes/No field as you "may" be only testing against that option box on the form and not the REAL underlying value in the table.


    See the attachment as an example. (it's in MSAccess 2007 though).

    Also try adding in a simple msgbox to show what the value of Qualified is:

    msgbox "Qualified = " & me!Qualified

    You'll notice that you'll never get a message that Qualified is Null! You'll most likely get a = 0 message (the default value).

    Or design a simple query and put criteria in for the Qualified field that shows records where Qualified IsNull. Then change it so the criteria for Qualified is = 0. Notice the results.

    Now if you've set up an option group or used some other method for the Yes/No field on the form (versus just a checkbox), you may get different results which aren't reflective of what value is ACTUALLY being stored in the table and you NEED to understand the underlying value in the table of a Yes/No field is either True/Yes/-1 or False/No/0! (NOT null!) Be careful about designing a form to try and store or test for a null value on a Yes/No field (it ain't going to happen and will just cause problems.)

    If you upload your mdb, we can see what you're trying to do and guide you in the right direction.
    Attached Files Attached Files
    Last edited by pkstormy; 09-19-09 at 18:16.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •