Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    code to display or not display text box control (2

    On a form, there is a multi-select list box. The Row Source for the list box is...
    <code>SELECT CustomerID,FullName, ProjectNbr, Format(Balance,"currency") AS [Balance Owing], DateDiff("d",[CompletionDateActual],Date()) AS [Days Outstanding] FROM qryBalance WHERE (((qryBalance.Balance)>0)) UNION SELECT "0","<ALL>",'','','' FROM qryBalance
    ORDER BY FullName;</code>

    Behind the Preview button is the following code...
    <code>Private Sub cmdStatement_Click()
    On Error GoTo Err_cmdStatement_Click

    Dim ndx As Integer
    Dim strList As String
    Dim strWHERE As String
    Dim stDocName As String

    stDocName = "rptStatement"

    'Default where-condition is empty
    strWHERE = ""

    'Test if "<ALL>" is selected
    If Me.lstBalance.Selected(0) = False Then
    'Loop through items
    For ndx = 0 To Me.lstBalance.ListCount - 1
    If Me.lstBalance.Selected(ndx) = True Then
    strList = strList & Me.lstBalance.ItemData(ndx) & ", "
    End If
    Next ndx
    ' Get out if nothing selected
    If strList = "" Then
    MsgBox "Please select one or more items.", vbExclamation
    Me.lstBalance.SetFocus
    Exit Sub
    End If
    strList = Left(strList, Len(strList) - 2)
    strWHERE = "[CustomerID] IN (" & strList & ")"
    End If

    DoCmd.OpenReport stDocName, acPreview, , strWHERE

    For ndx = 0 To Me.lstBalance.ListCount - 1
    Me.lstBalance.Selected(ndx) = False
    Next
    Me.txtSelected = Null
    Me.Text19 = Null

    Exit_cmdStatement_Click:
    Exit Sub

    Err_cmdStatement_Click:
    MsgBox Err.Description
    Resume Exit_cmdStatement_Click

    End Sub</code>

    In the ProjectID Group Footer is a control called [txtOwing] for which the Control Source is...
    <code>="Days since invoiced: " & DateDiff("d",[CompletionDateActual],Date()) & "
    Please remit outstanding balance. Thanks!"</code>

    On the OnFormat event for the ProjectID Group Footer is the following code, to display or not display [txtOwing] depending on the DateDiff value...
    <code>
    Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
    Dim daysTemp As Integer
    daysTemp = DateDiff("d", [CompletionDateActual], Date)
    Me.txtOwing.Visible = (daysTemp >= 30)
    End Sub</code>

    All of this works fine, no matter how many selections are made in the list box...UNLESS you select <All>. If <All> is selected the code faults out with an Invalid use of Null error on the line
    daysTemp = DateDiff("d", [CompletionDateActual], Date)

    Any suggestions as to how I fix this would be appreciated.

    Thanks.

    Tom

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

    Re: code to display or not display text box control (2

    An Integer cannot be Null. Change the declaration

    Dim daysTemp As Integer

    to

    Dim daysTemp As Variant

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code to display or not display text box contro

    That results in run-time error 13, data type mismatch.

    I also tried changing the last column in the SQL statement from '' to "0" but that doesn't help either.

    Tom

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

    Re: code to display or not display text box contro

    The row source of the list box doesn't have anything to do with this. Try this:

    Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
    Dim daysTemp As Integer
    If IsNull([CompletionDateActual]) Then
    Me.txtOwing.Visible = False ' or True, whichever you prefer
    Else
    daysTemp = DateDiff("d", [CompletionDateActual], Date)
    Me.txtOwing.Visible = (daysTemp >= 30)
    End If
    End Sub

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code to display or not display text box contro

    Well, that doesn't work either. What happens is that code results in a total report error.

    The CompletionDateActual will never be Null because it is set at the time of an Invoice being created.

    What I am trying to do now is develop a monthly statement form, and on it to show a text box if there is a balance owing that is 30 days or over.

    The reason I was trying to do something in the list box was that in all of the other list box rows there is a value in the Days Outstanding column, and one can select as many as one likes and get an accurate report. It's only when one selects the <ALL> row, which has a value only in Column(0), the CustomerID column, that the code faults out.

    Tom

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

    Re: code to display or not display text box contro

    I don't think I can offer further help without seeing a stripped down copy of the database.

  7. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code to display or not display text box contro

    I would have a hard time stripping the database down to a size small enough to send.

    I have tried everything I can think of. I am going to abandon the <ALL> capability in the list box, and just leave it as a multi-select list box. Perhaps the number of times that the user will need to send statements of accounts to everybody is limited.

    Thanks for trying.

    Tom

Posting Permissions

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