Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Aug 2014
    Posts
    10

    Unanswered: Hide acces row when no value

    Hi,

    I'm new on this forum and just started to use Access. the last weeks I am working on a report for my work and I'm almost finished but am struggling
    with my report.

    In my report I have the fields txtRemarks & txtRemarksRed these fields are displayed below each other. the field txtRemarks always has information in it, but the field txtRemarksRed can be empty. In my report I want the complete row that contains the txtRemarksRed field to disappear when there is no text in it. Is there a VBA code or another function in Acces that can help me with this? I tried mulitple options by searching the web like:

    If Me.txtRemarksRed = " " Then
    Me.txtRemarksRed.Visible = False
    Else
    Me.txtRemarksRed.Visible = True
    End If

    As pictures say more then a 1000 words I attached a copy of the file. It concerns the report "rpt Quality control week overview" parameter is "31" for weekdisplay#

    I really hope some can help me with this issue.

    thanks a lot for the help

    Rik
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so in the reports on format event CANCEL the print of the row
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2014
    Posts
    10
    And how do I cancel the print of the row with no value?
    What VBA code do I use?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59

  5. #5
    Join Date
    Aug 2014
    Posts
    10
    I tried the code of below link (microsoft.public.access.reports) but I received a error when running the print preview. As I'm not that good in VBA I don't now what to adjust.

    ' Put values in text boxes and hide unused text boxes.

    Dim intX As Integer
    ' Verify that you are not at end of recordset.
    If Not rstReport.EOF Then
    If Me.FormatCount = 1 Then
    For intX = 1 To intColumnCount
    ' Convert Null values to 0.
    Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
    Next intX

    ' Hide unused text boxes in the "Detail" section.
    For intX = intColumnCount + 2 To conTotalColumns
    Me("Col" + Format(intX)).Visible = False
    Next intX

    ' Move to next record in recordset.
    rstReport.MoveNext
    End If
    End If


    Error = sub or function is not defined. I would think in some felds above I should mention the names of my fields, but which and where


    sorry but I'm just starting to understand it

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    If Me.txtRemarksRed = "" or isNULL(txtRemarksRed)  Then
      txtRemarksRed.visible = false
    Else
      txtRemarksRed.visible = true
    End If
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2014
    Posts
    10
    thanks a lot, the code seems to hide the empty values of the field, I tried this codes before but I guess in the wrong event.

    but unfortunatly the report still shows the empty row in the print preview, so I have a lot of blank rows in there. Is there a way to remove the row as well?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what is it to be
    do you want to suppress the printing of the whole row or just hide txtRemarksRed when txtRemarksRed is empty?

    if you want to suppress the row insted of setting the visible property to false for txtRemarksRed, then set cancel = true. cancel is a parameters passed back from the event handler. if its true then the row isn't printed

    if your txtremarksred sits underneath txtremarks and what you want to do is shift the space allowed for the detail then agian you'd need to change the code in the on format event handler

    Code:
    If Me.txtRemarksRed = "" or isNULL(txtRemarksRed)  Then
      me.height = txtremarks.top+txtremarks.height
    Else
      me.height = txtremarksred.top+txtremarksred.height
    End If
    you may also need to shift the txtremarksred location so its hidden behind other controls
    you could also try can shrink/grow properties
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Aug 2014
    Posts
    10
    I want the complete row to suppress when the txtRemarksRed field is empty.
    So that there is now empty row in between the data rows.

    or even better I want to switch the field txtRemarks and txtRemarksred so that the red text is presented first on the report and then when there is no value in the txtRemarksRed I want the txtRemarks to take first place and the txtRemarksRed is hidden (and no empty rows in between the data). but this seems impossible. so I'm more then happy with the first option.

    I tryed your last code but I received a error message:
    This property has the characterustuc read-only and therefore can not be set

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so if you want to suppress rows then set the cancel parameter = true

    if you want to juggle the order of columns with a report then don't use bound controls, instead set the controls value in the report details on format event
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Aug 2014
    Posts
    10
    I tried the cancel argument and it did something but now every row with
    remarksred value is gone. is there something wrong with the code?

    Private Sub Details_Format(Cancel As Integer, FormatCount As Integer)
    If Me.txtRemarksRed = "" Or IsNull(txtRemarksRed) Then
    Cancel = True
    Else
    Cancel = False
    End If

    End Sub

    the second part in don't get, you mean I need to empty the report and put everything in code? how do I do that?

    I also tryed to create a string: txtRemarksRed & " " & txtremarks and it worked as in all information was in one field and there are now empty rows between data. but I can get the txtRemarksRed to mark red with white letters and the txtRemarks as normal text in the same field. Also the txtRemarks doesn't start on a new line. So this didn't work either.

  12. #12
    Join Date
    Aug 2014
    Posts
    10
    Hi Healdem,

    Can you help me with the last steps? Or maybe someone else?

    thanks a lot

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you are using continuous forms not a report Im guessing
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Aug 2014
    Posts
    10
    No I'm using a standard report bases on a query.

    below code works, but still keeps the emty space of the row:

    If Me.txtRemarksRed = "" or isNULL(txtRemarksRed) Then
    txtRemarksRed.visible = false
    Else
    txtRemarksRed.visible = true
    End If

    The cancel code cancels also the row above with the data I want to keep.

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the row above?
    what fo you mean
    setting cancel=true stops printing of the whole detail row. its news to me if it can cencel a previous detail row

    you could try settign thre detail on format can shrink property to true
    failing that rejig your design
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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