Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: how to use an If Else in Access 2007 report

    Hi - I don't have much experience writing VBA in reports, and I'm trying to execute a simple procedure where the value in a text box will display label1 or label2, dpending on the value.

    The Text box called CHANGES record source is from a query expression I built to identify if 2 fields are the same: txt1<>txt2. The values are either 0 or -1 for true or false.

    Now over in the report, I see my values (there is one of each for this test) for my 2 records. Created my labels and put this statement in the report OnActivate.......

    Private Sub Report_Activate()
    If Me!CHANGES = 0 Then

    Me.lbl1.Visible = True
    Me.lbl2.Visible = False


    Else

    Me.lbl1.Visible = False
    Me.lbl2.Visible = True

    End If


    End Sub

    I thought the Else would pick up on the -1 value, but for some reason I am only getting the lbl1 on the report.......even where the value is -1. Label2 stays invisible.

    I can't quite figure out exactly how to loop around the trues and false in this syntax. Can someone give suggestions?????

    Thanks in advance!

  2. #2
    Join Date
    Jan 2010
    Location
    Illinois
    Posts
    6

    Re: How to Use an If Else in Access 2007 report

    Try putting the parentheses and quotes around the If statements. Here is an example of some vba that I use for one of my databases at work. This opens up certain switchboards depending on the username's department, like If username's status is set at Loss Mitigation, then open LossMit Switchboard, and so on.

    If ([STAFF_STATUS] = "Loss Mitigation") Then
    DoCmd.Close
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frm_Switchboard_LossMit"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Maximize

    Else

    If ([STAFF_STATUS] = "Forceplace Insurance") Then
    DoCmd.Close
    Dim stDocName2 As String
    Dim stLinkCriteria2 As String

    stDocName2 = "frm_Switchboard_Forceplace"
    DoCmd.OpenForm stDocName2, , , stLinkCriteria2
    DoCmd.Maximize

    Else

    DoCmd.Maximize

    End If
    Last edited by Sangie; 05-06-11 at 18:59.

  3. #3
    Join Date
    Jun 2010
    Posts
    186
    Thanks for the tip - I have modified mine to :
    Private Sub Report_Activate()
    If ([CHANGES] = 0) Then
    Me.lbl1.Visible = True
    Me.lbl2.Visible = False
    Else
    If ([CHANGES] = -1) Then
    Me.lbl1.Visible = False
    Me.lbl2.Visible = True
    Else
    End If

    End Sub
    and get compile error block if without end if

    then both labels are visible

  4. #4
    Join Date
    Jan 2010
    Location
    Illinois
    Posts
    6

    Re: How to Use an If Else in Access 2007 report

    Looks like you had an extra "Else" at the end, Try this with the quotes too:

    Private Sub Report_Activate()
    If ([CHANGES] = "0") Then
    Me.lbl1.Visible = True
    Me.lbl2.Visible = False
    Else
    If ([CHANGES] = "-1") Then
    Me.lbl1.Visible = False
    Me.lbl2.Visible = True
    End If
    End Sub
    Last edited by Sangie; 05-06-11 at 19:17.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if this needs to change per record, rahter than per report then you probably need to shift it to the on detail format event

    if changes is a boolean value then you ought to use a boolean test in the if statement.
    for two reasons
    1) it reads better
    2) if you move the data to another storage mechanism there is a risk that coding to numbers rather than values may be broken.
    Code:
    Private Sub Detail_Format()
    If [CHANGES] = vbfalse Then
    Me.lbl1.Visible = True
    Me.lbl2.Visible = False
    Else
    If ([CHANGES] = vbtrue) Then 'if this IS a boolean test then the other if is uneccesary
    Me.lbl1.Visible = False
    Me.lbl2.Visible = True
    End If
    End Sub
    assuming the test is for boolean values then you can short circuit the logic to
    Code:
    me.lbl1.visible = not [CHANGES]
    me.lbl2.visible = [CHANGES]
    alternatively why have two labels
    Code:
    If ([CHANGES] = "0") Then ' OR If [CHANGES] = vbtrue
    Me.lbl1.caption = "blah di blah"
    Else
    Me.lbl1.caption = "hot de hoot"
    End If
    Id also suggest giving your controls more meaningful names in place of lbl1/ bear in mind the real cost of software is the maintenance issue so giving it a really standout clear name makes it easier to understand code. I don't go as far as some proponents that code should read claerly in English but it does make it easier for others (or even yourself) to understand what is going on.

    also no bad thing to stuff in a comment so you can describe why you want to set a value for lbl1.. again so its easier on you or others to understand why you do soemthing. there's no point commenting the code if you can understand what is being done from the code itself.

    eg
    Me.lbl1.Visible = True 'set label visible if changes are false
    ..is irrelevant, anybiody who knows VB/VBA will read that in the code
    Me.lbl1.Visible = True 'show the 'No Changes made' label
    ..is a bit better
    ..y'get the picture
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2010
    Posts
    186
    healdem - thank you for this reply.......so to go through with your suggestions I gave the label a more detailed name, use one label, in the Detail Format.

    so I start in writing (this is the only code for this report):
    Private Sub Detail_Format()
    If [CHANGES] = vbFalse Then
    ' Access 2007 wants a DoCmd here, which I can't figure out what DoCmd will take me to my label ?

    I have a previous example from Sangie with DoCmd......and I didn't even realize that Access just wasn't picking up the Me. I was using

    Maybe I can write a Macro for label caption, then DoCmd****nMacro here?

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    public sub detail_format()
    If [CHANGES] = vbtrue
    mylabel.caption = "blah di blah"
    Else
    mylabel.caption = "hot de hoot"
    End If
    end sub

    this assumes
    the label you wish to set is called mylabel.. you should change it to whatever your label is called
    the correct property for a lable is called caption. its a while since Ive done any development in Access so it may not be called caption, if so change caption to the appropriate property (it may be text, value or so on). heck for all I know it may be the default property for a label so mylabel = "hoot de hoot" may be correct.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jun 2010
    Posts
    186
    healdem and Sangie - thank you both for your insight to my problem. I always appreciate the coding techniques I gather from this forum.

    After plugging away for most of the day with variations of quotes and brackets and everything else and it not working....I decided to step back to the query.

    I reformated CHANGES to read true or false, and in the format property on the report I was able to show the text I wanted for true and for false separated by ; worked like a charm, and since the "labels" I was originally trying to create didn't need to repeat themselves per record - I created a grouping level for CHANGES, so the report will show all the true "NO CHANGE" up here - and all the false "CHANGED" down there

    I hope you both will be available for future assistance, as your response was most appreciated!

    Have a super day
    Last edited by Foskbou; 05-10-11 at 14:36. Reason: typed quote in the 2nd paragraph instead of query

Posting Permissions

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