Report Generation- Placing field names once

[stalk]

I need to generate a report with field names and 3 records values at a time. What changes should I make to place the field name once and then copy all the 3 records values to the report?
Private Sub FillInData()
On Error GoTo Err_FillInData
Dim DB As DAO.Database


Dim rsDataPutIn As DAO.Recordset
Dim SqlStr As String
Set DB = CurrentDb
SqlStr = "SELECT tblRptDataOneField.lngRptDataOneField, tblRptDataOneField.FieldName, "
SqlStr = SqlStr & "tblRptDataOneField.FieldVaue FROM tblRptDataOneField;"
Set rsDataPutIn = DB.OpenRecordset(SqlStr, dbOpenSnapshot)
If (rsDataPutIn.EOF And rsDataPutIn.BOF) = False Then
If (IsNull(rsDataPutIn!lngRptDataOneField) = False) Then
Me.lblOne.Caption = rsDataPutIn!FieldName
Me.txtOne.SetFocus
Me.txtOne.Text = rsDataPutIn!FieldVaue
rsDataPutIn.MoveNext
End If
If (rsDataPutIn.EOF) = False Then
If (IsNull(rsDataPutIn!FieldName) = False) Then
Me.lblTwo.Caption = rsDataPutIn!FieldName
Me.txtTwo.SetFocus
Me.txtTwo.Text = rsDataPutIn!FieldVaue
rsDataPutIn.MoveNext
End If
End If
If (rsDataPutIn.EOF) = False Then
If (IsNull(rsDataPutIn!FieldName) = False) Then
Me.lblThree.Caption = rsDataPutIn!FieldName
Me.txtThree.SetFocus
Me.txtThree.Text = rsDataPutIn!FieldVaue
rsDataPutIn.MoveNext
End If
End If
Exit_FillInData:
Set rsDataPutIn = Nothing
Set DB = Nothing
Exit Sub
Err_FillInData:
MsgBox Err.Number & Err.Description & vbCrLf & "Error in report_rpt_test Private Sub FillInData"
Resume Exit_FillInData
End Sub


Thank you in advance

[ranman256]

instead of using all this code, cant you just print a report using a query of TOP 3?

[stalk]

I have to generate this kind of report every now and then based on the filter criteria. Most of the time my criteria pull 3 records.
Also the field names change each time so based on the field names and their values my report is dynamically generated.

FYI. Report works perfectly for 1 records. But I am not sure how to change the code to print 3 records

[Gicu]

Can you upload a sample db to have a look?

[stalk]

Excel attached

[June7]

Would prefer to have database with table, query, report, code.

You have Results as value in two records but then Result in the third.

Please post code between CODE tags to retain indentation and readability.

[stalk]

Private Sub FillInData()
 On Error GoTo Err_FillInData
 Dim DB As DAO.Database
 Dim rsDataPutIn As DAO.Recordset
 Dim SqlStr As String
    Set DB = CurrentDb
    SqlStr = "SELECT tblRptDataOneField.lngRptDataOneField, tblRptDataOneField.FieldName, "
    SqlStr = SqlStr & "tblRptDataOneField.FieldVaue FROM tblRptDataOneField;"
    Set rsDataPutIn = DB.OpenRecordset(SqlStr, dbOpenSnapshot)
          If (rsDataPutIn.EOF And rsDataPutIn.BOF) = False Then
               If (IsNull(rsDataPutIn!lngRptDataOneField) = False) Then
                Me.lblOne.Caption = rsDataPutIn!FieldName
                Me.txtOne.SetFocus
                Me.txtOne.Text = rsDataPutIn!FieldVaue
                rsDataPutIn.MoveNext
            End If
            If (rsDataPutIn.EOF) = False Then
                If (IsNull(rsDataPutIn!FieldName) = False) Then
                    Me.lblTwo.Caption = rsDataPutIn!FieldName
                    Me.txtTwo.SetFocus
                    Me.txtTwo.Text = rsDataPutIn!FieldVaue
                    rsDataPutIn.MoveNext
                End If
            End If
            If (rsDataPutIn.EOF) = False Then
                If (IsNull(rsDataPutIn!FieldName) = False) Then
                    Me.lblThree.Caption = rsDataPutIn!FieldName
                    Me.txtThree.SetFocus
                    Me.txtThree.Text = rsDataPutIn!FieldVaue
                    rsDataPutIn.MoveNext
                End If
            End If
 Exit_FillInData:
    Set rsDataPutIn = Nothing
    Set DB = Nothing
    Exit Sub
 Err_FillInData:
    MsgBox Err.Number & Err.Description & vbCrLf & "Error in report_rpt_test Private Sub FillInData"
    Resume Exit_FillInData
 End Sub

[davegri]

          If (rsDataPutIn.EOF = False) And (rsDataPutIn.BOF = False) Then

[June7]

Code is missing an End If and should not execute at all. Running Debug Compile should reveal that.

That is still just Excel file in the attachment.