Results 1 to 5 of 5
  1. #1
    Sinclair is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2022
    Posts
    2

    Conditional Visible textbox

    I am creating a database to log maintenance on our inventory of items. Each time maintenance is completed or the status of the item changes, a new record is created to develop a history of maintenance for each item. I have created a report showing a log for each item and I have a "status" field showing whether the item is in stock, issued out, damaged or disposed.



    On my report I have created a label in red in the header saying "disposed." I am trying to use the code builder to make the disposed label appear only when the most recent record has a disposed status. While testing my code, I have 2 records showing up in the report. both in the Query and in the report, they are sorted so the most recent record at the top of the list. When I run my code, the label appears only when the second(or older) record has a the correct status. I thought maybe it would only appear if every record in the report had the same status based on my code but when the first row shows disposed and the second does not, the label does not appear. When the second row shows disposed but the first does not, the label does appear.

    Here is the simple code I've built into the report onload event:

    Private Sub Report_Load()

    If Me.Status = "6 - disposed" Then
    Me.disposedlabel.Visible = True
    Else
    Me.disposedlabel.Visible = False
    End If
    End Sub

    I can't figure out how to get the report to look at the most recent record in the report. I know the answer has got to be simple but I've searched for a couple of days through google and haven't come across an answer that fits my question.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    does the report have sorting? note: the report sort overrides the query sort.

    and:
    Me.disposedlabel.Visible=Me.Status = "6 - disposed"

  3. #3
    Sinclair is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2022
    Posts
    2
    So I got rid of the sorting on the report. The label is correctly showing up only when the most recent record is set as disposed. Even though my query sorts the entries in descending order by record date, my report is still showing the most recent record at the bottom of the report where I would prefer the most recent be at the top. When I sort the report in descending order, the label still only reads the last record in the report instead of the most recent.

    Also, thank you for that code. Way less complicated than the way I was writing it!

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Reports take NO NOTICE of source sorting.
    It has to be done in the report.

    Plus I would have thought the Load event would not be a suitable place for the code anyway?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    As suggested in the previous post to dynamically modify the visibility of the label for each record you need to move the code to the Format or Print event of the report section (most likely the Detail section) containing the control (label).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Use option button to make textbox not visible
    By Stephenson in forum Programming
    Replies: 4
    Last Post: 10-05-2015, 03:40 PM
  2. Replies: 4
    Last Post: 06-12-2014, 03:06 PM
  3. textBox Value making label visible
    By barkly in forum Forms
    Replies: 5
    Last Post: 07-24-2013, 07:05 PM
  4. Textbox/label Visible
    By prawln in forum Programming
    Replies: 6
    Last Post: 05-20-2011, 01:57 PM
  5. Replies: 2
    Last Post: 01-06-2011, 04:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums