Results 1 to 6 of 6
  1. #1
    Nico80000 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    3

    SetProperty for OnFormat in Report


    I am very new and inexperienced with macros so bear with me.
    I am trying to dynamically hide the details section of a report if the group is equal to particular criteria.

    I know enough to get to the point where i realize how to get to the macro event builder for sections of the report by clicking on the details section. Once I am in the macro builder my thought was that it would essentially be...

    If [State Groups] equals "Other" Then
    SetProperty
    Control Name (Not sure for this)
    Property Visible
    Value No
    End If

    I am not sure what I am doing wrong with the If part of the macro because it never gets past this part.
    Long story short, in order to hide the details section of the report for a particular group, from what I've read online, it seems that the OnFormat event is my best bet. Then my guess is that it would have to be some type of If statement where I would input my criteria for the macro to run. Then I think I would use the SetProperty action to select the Visible property to "No" so that it would still display the group footer but not the detail section.
    Again, I have no idea what I am doing so any help would be amazing. For any potential necessary details, the name of my report is "County Concentrations Report" the field I am trying to test for criteria is "State Groups" and my criteria would be "Other" for that field.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,757
    your best bet might be to resort to code. Most experienced help here kinda disavows macros so we're not that knowledgeable with the finer points. You could use the macro to run this code but I think it would be easier/best to let the report OnFormat event take care of it. It would probably be easy enough to find code you can copy and adapt.

    If you want to stick with the macro route for now, then I think your control name line would be Reports!rptNameOfYourReport.Section(i) where i is the number of the section. Detail section is 0 IIRC (or you could probably use acDetail instead of 0). You could also post a zipped copy of your db and someone could likely figure out the macro route or just provide you with the code version.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  3. #3
    Nico80000 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    3
    I mean if there is another way to go about this rather than a macro, I'm all ears. I figured I had to use a macro because when you go to the OnFormat event, you need to choose between a macro, vba, or expression builder and I figured they were basically all the same. Again, complete novice to this area, as you can tell so any advice is welcome.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,757
    No, macro is what you already know about. Expression builder, well you could click on that just to see what it looks like but what you need is not an expression (e.g. SomeThing = Forms!myform.SomeControl). Worth playing with or researching as it may help you one day. VBA is code only. I have my default set to code so when I click the ellipses I don't see the other options. Choose code and it will probably create an event with

    Code:
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    
    End Sub
    Put something like this in between:
    If Me.[FieldName] = "abc" Then Me.Detail.Visible = False

    where FieldName is the name of the control that contains the data you want to base this on and abc is the value you want to be the trigger for the decision. Note that if just one record contains that value, none of the other records will show. Also, this event only fires for print preview.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  5. #5
    Nico80000 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    3
    That worked! The only thing is that I need the details for the other sections to show, but only hide the details section where my criteria is met.
    Thanks btw, I'm very new to this and any help like this makes learning macros/ vba so much easier.

    Edit: I actually managed to figure it out... As you can probably manage to guess, I would have to add an Else line with detail.visible set to true

    Thanks again!

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,757
    That's great! Means you learn quickly and can adapt. That will serve you well.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

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

Similar Threads

  1. Replies: 9
    Last Post: 07-03-2017, 11:24 PM
  2. Replies: 4
    Last Post: 10-08-2013, 02:42 AM
  3. Replies: 2
    Last Post: 09-13-2012, 12:25 PM
  4. Report load events - OnFormat???
    By weasel7711 in forum Reports
    Replies: 1
    Last Post: 04-20-2011, 09:33 PM
  5. Correct way to SetProperty with Macro Builder
    By jasonbarnes in forum Forms
    Replies: 4
    Last Post: 02-25-2011, 02:37 PM

Tags for this Thread

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 - Senior Forums