If statement inside With block not executing

[fkneg1]

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?

[arnelgp]

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).

[Aussiebear]

@arnelgp, Its possibly just a typo I believe, given that earlier its constructed as


If (rs.fields("Song " & intItem & " chosen_Chorus " & intVerse).Value

[SamT]

What is the Field Type of the rs.Fields("Song 42" & " chosen.Chorus42") Field? (Numbers made up for example.) Should be Boolean Type.

[fkneg1]

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?

[Aussiebear]

Should the if statement then work?

One step at a time.... No point putting a roof of a house if the walls are suspect.

[fkneg1]

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.

[arnelgp]

check the field name specially for "spaces":

Song X Chosen_ChorusY

where x and y are integers.

do you have this as fieldname?

[fkneg1]

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

[arnelgp]

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
...
...