Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IsNull or Not Inull. (A2000)

    Fields:-
    txtUnitID
    txtDateFrom
    txtDateThru
    txtColor
    txtClientSelect
    txtJobSelect

    In the following code, if all or one or any combination of fields are empty, my msgbox states "Invalid Entry" and returns the user to start the selection again.
    But.
    If all texts are filled in, other than txtClientSelect and txtJobSelect then I will open a popup form where "ok" is.

    The code is cancelling its self out. Is their any way to make this work correctly.




    If Not IsNull(Me!txtUnitID) And Not IsNull(Me!txtDateFrom) And Not IsNull(Me!txtDateThru) And Not IsNull(Me!txtColor) And IsNull(Me!txtClientSelect) And IsNull(Me!txtJobSelect) Then
    MsgBox "ok"
    GoTo SkipCode
    End If
    If IsNull(Me!txtUnitID) Or Me!txtUnitID = "" Or IsNull(Me!txtDateFrom) Or IsNull(Me!txtDateThru) Or IsNull(Me!txtColor) Or Me!txtColor = "" Or IsNull(Me!txtClientSelect) Or Me!txtClientSelect = "" Or IsNull(Me!txtJobSelect) Then
    MsgBox "Invalid Entry, Please Check Your Selection Again"
    GoTo SkipCode
    End If

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IsNull or Not Inull. (A2000)

    Dave,

    In the first part
    <hr>If Not IsNull(Me!txtUnitID) And Not IsNull(Me!txtDateFrom) And Not IsNull(Me!txtDateThru) And Not IsNull(Me!txtColor) And IsNull(Me!txtClientSelect) And IsNull(Me!txtJobSelect) Then<hr>
    shouldn't you have Not before *EACH* IsNull ? Now, when the first four are filled, but the last two (txtClientSelect and txtJobSelect) are empty, neither condition is True.

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IsNull or Not Inull. (A2000)

    Hans
    I see what you mean here.

    My two conditions are:-

    If all fields are populated other than txtClientSelect and txtJobSelect, this is exceptable. A popup takes over the rest to fill in these two.
    If any other combination, this not excepable, the user is kicked out.

    Do you think a selectcase statement would remedy this.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IsNull or Not Inull. (A2000)

    Dave,

    If I understand you correctly, you don't need to check txtClientSelect and txtJobSelect at all. You might use

    If Not IsNull(Me!txtUnitID) And _
    Not IsNull(Me!txtDateFrom) And _
    Not IsNull(Me!txtDateThru) And _
    Not IsNull(Me!txtColor) Then
    MsgBox "ok"
    Else
    MsgBox "Invalid Entry, Please Check Your Selection Again"
    End If

    or, stated in a different but equivalent way:

    If IsNull(Me!txtUnitID) Or _
    IsNull(Me!txtDateFrom) Or _
    IsNull(Me!txtDateThru) Or _
    IsNull(Me!txtColor) Then
    MsgBox "Invalid Entry, Please Check Your Selection Again"
    Else
    MsgBox "ok"
    End If

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IsNull or Not Inull. (A2000)

    Thanks Hans
    Option two is better for me, I can now insert popup code instead of the "ok" message.

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IsNull or Not Inull. (A2000)

    Hans
    I've tried both methods and both allow 3 out of 4.
    ie
    'If IsNull(Me!txtUnitID) Or_
    'IsNull(Me!txtDateFrom) Or_
    'IsNull(Me!txtDateThru) Or_
    'IsNull(Me!txtColor) Then
    ' MsgBox "Invalid Entry, Please Check Your Selection Again"
    ' GoTo SkipCode
    'Else
    ' Dim stDocName As String
    ' Dim stLinkCriteria As String
    ' stDocName = "frmClientSelect"
    ' DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog
    'End If
    If Not IsNull(Me!txtUnitID) And _
    Not IsNull(Me!txtDateFrom) And _
    Not IsNull(Me!txtDateThru) And _
    Not IsNull(Me!txtColor) Then
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frmClientSelect"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog
    Else
    MsgBox "Invalid Entry, Please Check Your Selection Again"
    GoTo SkipCode
    End If

    if txtUnitID and txtDateFrom and txtDateThru contain data and txtColor does not, the code continues through the code.
    Vica versa dependant on which option to use.

    I must be missing something simple here.

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IsNull or Not Inull. (A2000)

    On the attachment, you see I have selected two dates from the toggles at the top.
    The text's are at the bottom of the form. this the data that will be passed into the grid.
    the popup as you see has popped (for use of a better word) where it should,nt until all other text's are populated.
    And this is only two selections.
    Vehicle= txtUnitID
    Date From = txtDateFrom
    Date To = txtDateThru
    Criteria = txtColor

    These must be populated "Before" the popup is allowed to pop.
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IsNull or Not Inull. (A2000)

    Dave,

    Some questions:

    1. Does the code accept *ANY* combination of three text boxes filled out and one empty, or just if txtColor is empty?
    2. Are the text boxes bound, i.e. is the Control Source a field name?
    3. Where is your code located or called from? For instance, in the BeforeUpdate or AfterUpdate event of one of the text boxes (if so, which one?) or of the form as a whole, or in the OnClick event of a command button, or somewhere else?

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IsNull or Not Inull. (A2000)

    Hans
    All the fields are unbound.
    They just hold the data temporarily until the DrawGrid button calls on them via a SQL insert into.

    Private Sub cmdDrawGrid_Click()

    '================================================= =====
    'If IsNull(Me!txtUnitID) Or_
    'IsNull(Me!txtDateFrom) Or_
    'IsNull(Me!txtDateThru) Or_
    'IsNull(Me!txtColor) Then
    ' MsgBox "Invalid Entry, Please Check Your Selection Again"
    ' GoTo SkipCode
    'Else
    ' Dim stDocName As String
    ' Dim stLinkCriteria As String
    ' stDocName = "frmClientSelect"
    ' DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog
    'End If
    If Not IsNull(Me!txtUnitID) And _
    Not IsNull(Me!txtDateFrom) And _
    Not IsNull(Me!txtDateThru) And _
    Not IsNull(Me!txtColor) Then
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frmClientSelect"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog
    Else
    MsgBox "Invalid Entry, Please Check Your Selection Again"
    GoTo SkipCode
    End If

    Dim strSQL As String
    Dim CourtesySelect As Integer
    CourtesySelect = DCount("*", "tblPeriod", "UnitID=" & Me!txtUnitID & " And " & _
    BuildCriteria("FromDate", dbDate, "<=" & Me!txtDateThru) & " And " & _
    BuildCriteria("ThruDate", dbDate, ">=" & Me!txtDateFrom))
    If CourtesySelect > 0 Then
    ' Warn the user
    MsgBox "This Vehicle Is Already Booked"
    ' Cancel the update
    GoTo SkipCode
    End If
    strSQL = "INSERT INTO tblPeriod ( UnitID, FromDate, ThruDate, colorkey, client, job) VALUES ( [Forms]![frmCourtesyCarPlanner]![txtUnitID], [Forms]![frmCourtesyCarPlanner]![txtDateFrom], [Forms]![frmCourtesyCarPlanner]![txtDateThru],[forms]![frmCourtesyCarPlanner]![txtcolor],[forms]![frmCourtesyCarPlanner]![txtclientselect],[forms]![frmCourtesyCarPlanner]![txtJobSelect] )"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    DrawGrid
    Untoggle
    SkipCode:
    Me.txtUnitID = ""
    Me.txtDateFrom = Null
    Me.txtDateThru = Null
    Me.txtVehicleID = ""
    Me.txtClientSelect = ""
    Me.txtJobSelect = ""
    Me.txtColor = ""
    Me.txtColorDesc = ""
    Untoggle
    End Sub

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IsNull or Not Inull. (A2000)

    Dave,

    Thank you for answering 2. and 3. That limits the possible causes. Can you check 1. too?
    <hr>1. Does the code accept *ANY* combination of three text boxes filled out and one empty, or just if txtColor is empty?<hr>
    I'm curious to see if it is specific for one text box.

  11. #11
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IsNull or Not Inull. (A2000)

    It doesn't seem to be specific to txtColor.

    I tried the following selections.

    txtColor on its own = msgbox
    txtColor + txtUnitID = msgbox
    txtColor + txtDateFrom or txtDateThru = popup form (notice no txtUnitID)

    txtDateFrom on its own = popup

    This seems to be where the problem is.
    The property for the date fields is not Date/Time but purely text !!

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IsNull or Not Inull. (A2000)

    Dave,

    I notice that in the code after the SkipCode label<pre>Me.txtUnitID = ""
    Me.txtDateFrom = Null
    Me.txtDateThru = Null
    Me.txtVehicleID = ""
    Me.txtClientSelect = ""
    Me.txtJobSelect = ""
    Me.txtColor = ""
    Me.txtColorDesc = ""</pre>

    you set the date text boxes to Null and the others to "". This might cause the IsNull test for the text boxes other than the date text boxes to return False. What happens if you set them all to Null?

    Another test you might use is<pre>If txtUnitID & "" <> "" And _
    txtDateFrom & "" <> "" And _
    txtDateDate & "" <> "" And _
    txtColor & "" <> "" Then
    DoCmd.OpenForm ...
    Else
    MsgBox "Error!"
    End If</pre>

    This way, it doesn't matter whether a text box contains Null or "".

  13. #13
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IsNull or Not Inull. (A2000)

    Hans
    The date fields "Must" have been numeric or Date/Time.

    Heres what works.

    If Me.txtUnitID = "" Or IsNull(Me.txtDateFrom) = True Or IsNull(Me.txtDateThru) = True Or Me.txtColor = "" Then
    MsgBox "Invalid Entry, Please Check Your Selection Again"
    GoTo SkipCode
    Else
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frmClientSelect"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog
    End If

    Thanks for prodding my brain once again.

Posting Permissions

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