Consulting

Results 1 to 10 of 10

Thread: If statement inside With block not executing

  1. #1
    VBAX Regular
    Joined
    Oct 2013
    Posts
    17
    Location

    If statement inside With block not executing

    Hi,
    I have a database of songs on MS Access which then produces a PowerPoint presentation of the chosen songs with each verse on a different slide. This all works fine but now I would like to add in an option in the database to tick a check box if the words are part of the song chorus which should then make that slide in italics. I have tried this:
    For intVerse = 16 To 1 Step -1        
    If IsNull(rs.Fields("Song " & intItem & " chosen_Verse " & intVerse).Value) Then
          '------
    Else        
        With .Slides.Add(rs.AbsolutePosition + 1, ppLayoutLargeObject)
               .FollowMasterBackground = False
               .Background.Fill.Solid
               .Background.Fill.ForeColor.RGB = RGB(0, 0, 0)
    
               With .NotesPage.Shapes.Placeholders(2)
                      .TextFrame.TextRange.Text = rs.Fields("Song " & intItem & " chosen.Notes")
               End With
    
               With .Shapes(1).TextFrame.TextRange
                      .Text = CStr(rs.Fields("Song " & intItem & " chosen_Verse " & intVerse).Value)
                      .Characters.Font.Color.RGB = RGB(255, 255, 255)
                      .Characters.Font.Size = 30
    
                      If (rs.Fields("Song " & intItem & " chosen.Chorus" & intVerse).Value) = True Then    '-- THIS STATEMENT NOT WORKING
                          .Characters.Font.Italic = True
                      Else
                           .Characters.Font.Italic = False
                      End If
    
                      .ParagraphFormat.Bullet = False
                      .ParagraphFormat.Alignment = ppAlignCenter             
                End With
                    
                If IsNull(rs.Fields("Song " & intItem & " chosen_Verse " & intVerse + 1).Value) Then
                     With .Shapes.AddTextbox(msoTextOrientationHorizontal, Left:=46.5, Top:=474, Width:=627, Height:=51).TextFrame.TextRange
                          .Text = rs.Fields("Song " & intItem & " chosen.Copyright Info")
                          .Characters.Font.Color.RGB = RGB(255, 255, 255)
                          .Font.Size = 14
                          .ParagraphFormat.Alignment = ppAlignRight
                      End With
                Else
                End If
           End With
        '--------------------------------------------------------------------------------------------------------------------
    End If
    
    Next intVerse
    Next intItem
    But this code seems to be ignoring the IF statement and just putting everything into italics even if the checkbox (Chorus1) is not ticked. Any ideas?
    Last edited by SamT; 06-03-2022 at 08:12 AM. Reason: Reformatted White Space

  2. #2
    obviously you can't create a field with dot (.) on it.
    neither with query, so, in the below expression:

    If (rs.Fields("Song " & intItem & " chosen.Chorus" & intVerse).Value) = True Then

    you will get away with this in the code if you Disable Error handling (On Error Resume Next).

    always add Option Explicit on your module declaration so you will know which variables you have not declared or you are assigning a wrong value (datatype).

  3. #3
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    @arnelgp, Its possibly just a typo I believe, given that earlier its constructed as
    If (rs.fields("Song " & intItem & " chosen_Chorus " & intVerse).Value
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    What is the Field Type of the rs.Fields("Song 42" & " chosen.Chorus42") Field? (Numbers made up for example.) Should be Boolean Type.
    Last edited by SamT; 06-03-2022 at 08:19 AM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Regular
    Joined
    Oct 2013
    Posts
    17
    Location
    Thanks for replies.

    To give some more context to the fields used - the user of the database chooses which songs they want in the PowerPoint, each chosen song is then in a query with each verse, copyright info, etc. This might not be the best way to do this but there is then another query that pulls all of these individual queries together to give all the data required for the PowerPoint in one place. The fields are then in the format 'Song 1 chosen.verse 1' or 'Song 1 chosen.notes' etc. Looking back at my code (I wrote most of this a few years ago) I have used an underscore instead of a dot for the verses but have left it as a dot for the notes - both of these seem to work fine.
    I will try renaming the fields to make sure I don't use any dots. Should the if statement then work?

  6. #6
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Quote Originally Posted by fkneg1 View Post
    Should the if statement then work?
    One step at a time.... No point putting a roof of a house if the walls are suspect.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  7. #7
    VBAX Regular
    Joined
    Oct 2013
    Posts
    17
    Location
    I have now updated all the fields to not include any dots so the line causing issues is now:
    If (rs.Fields("Song " & intItem & " Chosen_Chorus" & intVerse).Value) = True Then
    I am getting an error on that line saying Runtime error 3265 - Item not found in this collection
    I have checked the query where the data is coming from and the field is there with the right name but doesn't seem to be finding it. The field is a checkbox - I wondered if that was something to do with it as I haven't used checkboxes in Access before.

  8. #8
    check the field name specially for "spaces":

    Song X Chosen_ChorusY

    where x and y are integers.

    do you have this as fieldname?

  9. #9
    VBAX Regular
    Joined
    Oct 2013
    Posts
    17
    Location
    Yes -the SQL on the query shows:
    [Song 2 Chosen].Chorus1 AS [Song 2 Chosen_Chorus1], [Song 3 chosen].Chorus1 AS [Song 3 Chosen_Chorus1], [Song 1 chosen].Chorus1 AS [Song 1 Chosen_Chorus1]
    At the moment I have only put the checkboxes on the first verse of the form - when it works I will duplicate to all verses

  10. #10
    just for Info, a Yes/No field will only hold True (-1) or False (0).
    it will not hold Null, so there is no point testing it for Null.
    also can you just use the Alias field as 001001 (for song 1 chorus 1), 001002 (song 2 chorus 2), etc.
    instead of having a Column name with "space".
    you then modify your code to:

    sField = Format$(intItem, "000") & Format$(intVerse, "000")
    If (rs.Fields(sField)) Then
    ...
    ...

Posting Permissions

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