Report based on single field 1

[Drake12]

Pretty new to Access so bare with me. Have a table of fields and I need the form to ask the user to print a report based on the "BLOCK" field. I would like a pull down type box that makes the user select from all the different blocks. I have created what I thought would work but it does not just gives me a compile error. Maybe someone could decipher this for me. Here is the event proceedure:

Private Sub Command0_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "Block Summary Report"
strField = "BLOCK"



' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
Me.Visible = False
If Not IsNull(Me.cboBlock) Then 'block combo box
strWhere = " AND [BLOCK] = """ & Me.cboBlock & """"
End If
End Sub

If there is a better way to do this that would be fine. I just thought a combo type box worked the easiest. I do not have a very through manual.

Thanks

 

[dhookom]

You have to set the strWhere prior to opening the report. Also, remove the " AND "

Private Sub Command0_Click()
  Dim strReport As String 'Name of report to open.
  Dim strField As String 'Name of your date field.
  Dim strWhere As String 'Where condition for OpenReport. 
  Const conDateFormat = "\#mm\/dd\/yyyy\#"
  strReport = "Block Summary Report"
  strField = "BLOCK"
  ' Debug.Print strWhere 'For debugging purposes only.
  If Not IsNull(Me.cboBlock) Then 'block combo box
    strWhere = "[BLOCK] = """ & Me.cboBlock & """"
  End If

  DoCmd.OpenReport strReport, acViewPreview, , strWhere
  Me.Visible = False
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]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Drake12]

Very nice, thank you. It would be nice if only one instance of each block showed up in the combo box. The table might have 200 A1's and 300 B1's etc... Is there a way just to show A1, B1, C1, and so forth so there will not be such a long list?

Thank You

 

[dhookom]

Change the row source of the combo box to a totals query. If you can't figure this out then come back with your combo box's row source property.

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]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Drake12]

SELECT [Fruit Table].BLOCK FROM [Fruit Table] ORDER BY [BLOCK];

That is my row source propery. I was reading about DISTINCT statement, but really did not know if that would help or hurt in the future.

Thanks

 

[dhookom]

Try
SELECT DISTINCT BLOCK FROM [Fruit Table] ORDER BY BLOCK;
Or
SELECT BLOCK FROM [Fruit Table] GROUP BY BLOCK ORDER BY BLOCK;


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]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Drake12]

Yeah that does the trick thanks so much.