Populate report with VBA query problem

[Webkins]

I am in need of some advice please. I am using MSAccess 2003 SP2 for this example. I am trying to create a report using the following query in VBA:

Option Compare Database: Dim Completed As Integer: Dim FA As Date

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Completed = 1 Then Exit Sub
Completed = 1
Nombre = "Rene Torres"
Dim rs As DAO.Recordset: Dim db As Database: Set db = CurrentDb()
strsql = "SELECT Tecnico, Problema, Mantenimiento, Proyecto, Sum(horas + minutos) AS Tiempo, [Fecha actual], OT FROM TB_Eventos GROUP BY Tecnico, Problema, Mantenimiento, Proyecto, [Fecha actual], OT, Horas, Minutos HAVING Tecnico = '" & Nombre & "'"
Set rs = db.OpenRecordset(strsql)
[Reports]![rep_Reporte_Tecnico]![Tecnico] = rs!Tecnico
rs.MoveFirst: Do Until rs.EOF
[Reports]![rep_Reporte_Tecnico]![FechaActual] = rs![Fecha actual]
[Reports]![rep_Reporte_Tecnico]![Tiempo] = rs![Tiempo]
[Reports]![rep_Reporte_Tecnico]![Problema] = rs![Problema]
[Reports]![rep_Reporte_Tecnico]![Mantenimiento] = rs![Mantenimiento]
[Reports]![rep_Reporte_Tecnico]![Proyecto] = rs![Proyecto]
[Reports]![rep_Reporte_Tecnico]![OT] = rs![OT]
rs.MoveNext: Loop: rs.Close: Set rs = Nothing: DoCmd.Echo True
End Sub

The report and query appears to be working just fine, it is looping through the records and returning 11 records. The problem is that there is only 1 record showing up on the report which so happens to be the last record returned. Any ideas what I might be doing wrong, I have been working on this for days and am getting nowhere. Thanks a bunch for any suggestions.

 

[PWise]

what are you trying to do why dony you just put the uery as the recordsourse of the report

 

[Webkins]

Because it is a query written in VBA and does not appear in the Queries window. Also I want to create a form with multiple selection Combo boxes with data to select from and then execute the query and populate the report. I don't know how to do this with a regular Access query. I don't want the users to be able to type the information into the standard query window that pops up when the query is executed.

I hope this all makes sense. Thank you for your reply.

 

[PWise]

either create the sql as an access query or use the sql string as the recordsourse of the report (copy the sql string and paste in the recordsource of the report on the data tab of the report propties)

 

[dhookom]

I agree with pwise. You are making this much harder than it needs to be. There are at least 3-4 solutions that use a record source for the report and bind controls to the fields in the record source.

Duane
Hook'D on Access
MS Access MVP

 

[FancyPrairie]

Pwise and dhookum are correct. Use a query as your recordsource for the report. Define the query so that it pulls all records. Then open the report from code like this: DoCmd.OpenReport "ReportName",acViewPreview,,BuildWhere(Me)

Note the BuildWhere function. It will scan through all of the controls on your form (including the multiple select combo box) and return the Where clause for you. The BuildWhere function can be found here faq181-5497. The header of the BuildWhere function contains directions on how to use it.

Basically, you simply copy the code from the FAQ and paste it into a new module. Then you define the tag property of the control(s) you want included in your report criteria.