Item not appearing in report 1

[ZOR]

I have a listbox that fills. It shows the field Keyword.
I use the rowsource to drive a report.

Me.LP1.RowSource = "SELECT TXMASTERS.Barcode, TXCLIPS.NNAME AS Name, TXCLIPS.Comments, " _
& "TXCLIPS.Start AS TimecodeIn, TXCLIPS.Duration, TXMASTERS.SportorSports AS Sport, " _
& "TXCLIPS.StarRating, TXCLIPS.Shot, KEYWORDS.Keyword, TXMASTERS.SeriesName AS Programme, " _
& "TXMASTERS.EpisodeTitle AS Episode, TXMASTERS.Competition" _
& " FROM (TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1)" _
& " INNER JOIN KEYWORDS ON TXCLIPS.Comments Like '*' & KEYWORDS.Keyword & ' *' " _
& "WHERE TXCLIPS.NName Like '*" & Replace(Me.LNAME11.Caption, "'", "''") & "*' " _
& "ORDER BY 1, TXMASTERS.BARCODE, TXCLIPS.Start"

On the report I have a textbox, with its source set as Keyword. However the box does not get filled. Anyone know why, thanks

 

[ZOR]

Appologies, quite desperate to solve problem, this is posted on Access Queries forum as this site very quiet. Thanks

 

[dhookom]

How do you "use the rowsource to drive a report"?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ZOR]

Thanks. I call the report from the form with:

DoCmd.OpenReport "COMPETITION", acViewPreview


In the report I have:

Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
Me.RecordSource = Forms!Newqueryform3!LP1.RowSource
End Sub

Many thanks

 

[dhookom]

I would add a line of code to put the SQL into the debug window. This would allow you to copy the SQL into a new, blank query and determine the error.

Private Sub Report_Open(Cancel As Integer)
  DoCmd.Maximize
  Me.RecordSource = Forms!Newqueryform3!LP1.RowSource
  debug.Print Forms!Newqueryform3!LP1.RowSource
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ZOR]

Many thanks. The immediate window is blank?

 

[ZOR]

Thanks further. I added the word Hello to get printed in the debug window, the report open event never opened, the wrong report was getting opened. I am following things through, should be back to say alls well I hope. Thanks

 

[ZOR]

No, report does not show keyword. Result in debug window is:

SELECT TXMASTERS.Barcode, TXCLIPS.NNAME AS Name, TXCLIPS.Comments, TXCLIPS.Start AS TimecodeIn, TXCLIPS.Duration, TXMASTERS.SportorSports AS Sport, TXCLIPS.StarRating, TXCLIPS.Shot, KEYWORDS.Keyword, TXMASTERS.SeriesName AS Programme, TXMASTERS.EpisodeTitle AS Episode, TXMASTERS.Competition FROM (TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1) INNER JOIN KEYWORDS ON TXCLIPS.Comments Like '*' & KEYWORDS.Keyword & ' *' WHERE TXCLIPS.NName Like '*dalgl*' ORDER BY 1, TXMASTERS.BARCODE, TXCLIPS.Start


Regards

 

[dhookom]

What happens when you paste the results from the debug window into the SQL view of a query?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ZOR]

Thanks. It fills LP1 okay, the column Keyword is there?

Regards

 

[dhookom]

Are you suggesting there are records displayed and the Keywords column is blank?

Are there any records in KEYWORDS where the KEYWORD field is blank?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ZOR]

No, only records with keywords are sent to the report. Regards

 

[dhookom]

I don't understand how your reply answers my questions.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ZOR]

Sorry if I confused you. The form allows a user to select keywords from a list. A list LP1 fills showing the records they have selected having the keywords they have chosen. When they are happy with the list, they can then print a report out. So the records held in LP1 all contain entries for the field Keyword.

I replaced the SQL I had in the forms code/SQL for LP1 with the code obtained in the debug window, and it behaves the same as the original code, with LP1 showing a column named Keyword.

Does that help? Regards

 

[ZOR]

I just tried a further test. I copied the SQL from the debug window into the click event of a button. When I click the button LP1 fills okay with the Keyword column.

In my report, the controlsource of the textbox is just the word Keyword. Is that correct? Thanks

 

[dhookom]

If your report's record source query contains a column named Keyword and the column has data displayed in it then your text box on the report with a control source of:
[Keyword]
should display the values from the records.

It may be an issue that Keyword is a reserved word. You may want to alias the column name in your sql to MyKeyWord or something and then bind the text box to
[MyKeyWord]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ZOR]

Thanks. I changed my SQL but it keeps popping up with a parameter for Keyword??

Me.LP1.RowSource = "SELECT TXMASTERS.Barcode, TXCLIPS.NNAME AS Name, TXCLIPS.Comments, " _
& "TXCLIPS.Start AS TimecodeIn, TXCLIPS.Duration, TXMASTERS.SportorSports AS Sport, " _
& "TXCLIPS.StarRating, TXCLIPS.Shot, KEYWORDS.Keyword AS MyKeyword, TXMASTERS.SeriesName AS Programme, " _
& "TXMASTERS.EpisodeTitle AS Episode, TXMASTERS.Competition" _
& " FROM (TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1)" _
& " INNER JOIN KEYWORDS ON TXCLIPS.Comments Like '*' & KEYWORDS.Keyword & ' *' " _
& "WHERE TXCLIPS.NName Like '*" & Replace(Me.LNAME11.Caption, "'", "''") & "*' " _
& "ORDER BY 1, TXMASTERS.BARCODE, TXCLIPS.Start"


Regards

 

[ZOR]

I removed the textbox on the report, and it still asked for a parameter value of Keyword even though previously I had changed its source to MyKeyword?? I added a new textbox on the report. When I run the form and select the report, it still has a popup asking for a parameter value of Keyword, but if I leave it empty and click okay, the Keyword appears in the report. If I can find where this popup and why it appears, then it might work. Somewhere its rembering something??? Thanks

 

[ZOR]

Many thanks Duane for hanging in there. I deleted the report and modified the original copy, and it works, Keyword is in there. I am leaving it as MyKeyword in case it returns to a problem condition. Have a star for help. Regards

 

[dhookom]

I expect your previous prompt for KeyWord was caused by sorting and grouping on Keyword.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]