Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002
    Posts
    157

    Unanswered: Checking for a null field

    I can't seem to get my coding to check for a null field to work correctly.

    'If the [PlacementRequestID] field already has a requestID entered, then I want to check why they are changing the record. (I don't want to stop them from making the change but I want them to think about why they are making the change).

    If the RequestId field is showing as null and I select a RequestID number from the select list then I do not want it to give me a message.

    On testing it is giving me the message whether or not the RequestID field is null.


    The requestID is a combo field with the following SQL code :
    SELECT eEmployer3RequestDetails.RequestID, eEmployer2SiteDetails.SiteName, eEmployer3RequestDetails.Category, eEmployer3RequestDetails.ActivityID, eEmployer3RequestDetails.[Activity/ProjDesc] FROM eEmployer2SiteDetails INNER JOIN eEmployer3RequestDetails ON eEmployer2SiteDetails.SiteID = eEmployer3RequestDetails.SiteID ORDER BY eEmployer2SiteDetails.SiteName;


    ' I have tried all off the follow but they do not do what I want (it gives me the message whether or not the filed is null).
    'If (IsNull(Me![PlacementRequestID].Value) Or Me![PlacementRequestID].Value = "") Then

    'If (IsNull(Me![PlacementRequestID]) Or Me![PlacementRequestID].Value = "") Then

    'If Me.[PlacementRequestID].Value = Null Or Me![PlacementRequestID].Value = "" Then

    I even tried testing for the field to see if it had data in it, but again it gives me the message whether or not the field is null
    'If (Not IsNull(Me.[PlacementRequestID].Value) Or Me.[PlacementRequestID].Value <> "") Then

    What am I doing wrong?

    ******************************************
    Private Sub PlacementRequestID_Click()
    On Error GoTo Err_PlacementRequestID_Click

    'If the record is a new record then skip all the following checks
    If Me.NewRecord Then
    Me![SkillCategory].Value = Me![PlacementRequestID].Column(2)
    GoTo Exit_PlacementRequestID_Click
    End If

    'If the [PlacementRequestID] field already has a requestID entered, then check why they are changing the record

    If Me.[PlacementRequestID].Value = Null Or Me![PlacementRequestID].Value = "" Then

    'Do nothing

    Else

    Select Case MsgBox("NOTE: When a Job Seeker's placement details change you must click on the 'ADD NEW PLACEMENT DETAILS' button to create a new placement record." _
    & vbCrLf & "" _
    & vbCrLf & "DO NOT CHANGE the current placement Request ID or the Activity Project ID (unless you had made a data entry error)." _
    & vbCrLf & "Changing the Request ID or Activity Project ID of a Job Seeker will result in historical data being lost." _
    & vbCrLf & "" _
    & vbCrLf & "Are you sure you want to change the 'Request ID' or the 'Activity Project ID'?" _
    & vbCrLf & "" _
    & vbCrLf & "Select NO to cancel or select YES to continue with changing the Request ID." _
    & vbCrLf & "" _
    , vbYesNo Or vbCritical Or vbDefaultButton2, "CHANGE OF REQUEST ID OR ACIVITY/PROJECT ID DETAILS")

    Case vbNo
    'abort
    DoCmd****nCommand acCmdUndo
    Me![PlacementRequestID].SetFocus

    Case vbYes
    Me![SkillCategory].Value = Me![PlacementRequestID].Column(2)

    End Select

    End If



    Exit_PlacementRequestID_Click:
    Exit Sub

    Err_PlacementRequestID_Click:
    MsgBox Err.Description
    Resume Exit_PlacementRequestID_Click


    End Sub

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    try nz(<expression>, "") <> ""

    Also, I'd try setting a breakpoint and poking around with the intermediate window to find out what value(s) you're really dealing with. It's much easier to find out what a value is than to guess what the value isn't...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I will sometimes use the LEN command - ie. if len([MyField]) > 0 then....
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    May 2002
    Posts
    157
    Thanks Teddy and pkstormy for replying. I am very grateful.
    Thanks heaps to both of you.

    Your replies got me thinking.

    I created a unbound combo check field to test out what the value of the field was. And it told me "No Value".

    **************
    Private Sub PlacementRequestIDCheck1()
    'CheckValue
    Dim frm As Form, ctl As Control
    Dim varResult As Variant

    ' Return Form object variable pointing to usysbJobSeekerPlacement form.
    Set frm = Forms!usysbJobSeekerPlacement
    ' Return Control object variable pointing to the [PlacementRequestID] field.
    Set ctl = frm![PlacementRequestID]
    ' Choose result based on value of control.
    varResult = IIf(Nz(ctl.Value) = "", _
    "No value", "Value is " & ctl.Value)
    ' Display result.
    MsgBox varResult

    End Sub
    ***************


    But still my If... then... statements were still not working.

    I then realised that by using the on click event of the [PlacementRequestID] field I was actually changing the value of the field before the If ... then... check occurred.

    So I created another unbound field and added the following to the On Enter Event Procedure for the PlacementRequestID.
    What this does is record what the PlacmentRequestID.value is before the on click event.
    ********
    Private Sub PlacementRequestID_Enter()
    'Required as part of the PlacementRequestID_Click Sub
    Me.[PlacementRequestIDCheck].Value = Me.[PlacementRequestID].Value
    End Sub
    **********

    Now I can check what the current value of the [PlacementRequestID] is before the value is changed on the onclick event.

    So my code now reads as follows (and it works):

    *********************************
    Private Sub PlacementRequestID_Click()
    On Error GoTo Err_PlacementRequestID_Click

    'If the record is a new record then skip all the following checks
    If Me.NewRecord Then
    Me![SkillCategory].Value = Me![PlacementRequestID].Column(2)
    GoTo COPY_ACTIVITYID 'Exit_PlacementRequestID_Click
    End If

    '[PlacementRequestIDCheck] is an unbound hidden field which holds the value of the the [PlacementRequestID] before the on click event.
    If IsNull(Me.[PlacementRequestIDCheck].Value) Then

    'Do Nothing
    Else

    Select Case MsgBox("NOTE: When a Job Seeker's placement details change you must click on the 'ADD NEW PLACEMENT DETAILS' button to create a new placement record." _
    & vbCrLf & "" _
    & vbCrLf & "DO NOT CHANGE the current placement Request ID or the Activity Project ID (unless you had made a data entry error)." _
    & vbCrLf & "Changing the Request ID or Activity Project ID of a Job Seeker will result in historical data being lost." _
    & vbCrLf & "" _
    & vbCrLf & "Are you sure you want to change the 'Request ID' or the 'Activity Project ID'?" _
    & vbCrLf & "" _
    & vbCrLf & "Select NO to cancel or select YES to continue with changing the Request ID." _
    & vbCrLf & "" _
    , vbYesNo Or vbCritical Or vbDefaultButton2, "CHANGE OF REQUEST ID OR ACIVITY/PROJECT ID DETAILS")

    Case vbNo
    'abort
    DoCmd****nCommand acCmdUndo
    Me![PlacementRequestID].SetFocus

    Case vbYes
    Me![SkillCategory].Value = Me![PlacementRequestID].Column(2)

    End Select

    End If


    Exit_PlacementRequestID_Click:
    Exit Sub

    Err_PlacementRequestID_Click:
    MsgBox Err.Description
    Resume Exit_PlacementRequestID_Click

    End Sub

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Thanks for posting your solution! It will also help others.
    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
  •