=> Continuous Form to Display output of a recordset

[Rhonda Fischer]

Hello,

I have a form with properties of
=> Default View: Continuous Form
=> Views Allowed: Form

I would like to use my code, as below, to loop through
and display to the form the records in my recordset.

However only the last line is displayed and no more
text boxes are added to the form as the code loops
through. How can I achieve this?

Your assistance would be most appreciated.

Thank you kindly
Rhonda

'*******************************************************
Sub displayTescoLoads()
On Error GoTo Err_displayTescoLoads

'Declaration
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset

'Open the connection
Set cnn = CurrentProject.Connection

'Set up the Command objects's Connection, SQL and
parameter types
With cmd
.ActiveConnection = cnn
.CommandText = "SELECT [Del Date], [Wave Number],
RDC, [Veh Reg], [Trailer No], " & _
"[Coll Point], [Coll Point2], [Coll
Point3], [Book Time], TotalPallets " & _
"FROM [Deliveries Made] " & _
"WHERE [Deliveries Made].[Del Date]
= ? AND RDC LIKE 'c%' ORDER BY [Deliveries Made].RDC"

.CreateParameter , adDate,
adParamInput 'Delivery Date
End With

cmd.Parameters(0) = [Forms]!
[FrmTescoOrderPlannerSelectDate]![ViewDate]

Set rst = cmd.Execute

Do Until rst.EOF
'Display records to the subform
[Forms]!FrmTescoOrderPlanner!FrmTescoLoads!
txtDelDate = rst![Del Date]
[Forms]!FrmTescoOrderPlanner!FrmTescoLoads!
txtWaveNumber = rst![Wave Number]
[Forms]!FrmTescoOrderPlanner!FrmTescoLoads!txtRDC =
rst![RDC]
[Forms]!FrmTescoOrderPlanner!FrmTescoLoads!txtVehReg
= rst![Veh Reg]
[Forms]!FrmTescoOrderPlanner!FrmTescoLoads!
txtTrailerNo = rst![Trailer No]
[Forms]!FrmTescoOrderPlanner!FrmTescoLoads!
txtCollPoint = rst![Coll Point]
[Forms]!FrmTescoOrderPlanner!FrmTescoLoads!
txtCollPoint2 = rst![Coll Point2]
[Forms]!FrmTescoOrderPlanner!FrmTescoLoads!
txtCollPoint3 = rst![Coll Point3]
[Forms]!FrmTescoOrderPlanner!FrmTescoLoads!
txtBookTime = rst![Book Time]
[Forms]!FrmTescoOrderPlanner!FrmTescoLoads!
txtTotalPallets = rst![TotalPallets]

rst.MoveNext

Loop

cnn.Close
Set cnn = Nothing
Set cmd = Nothing

Exit_displayTescoLoads:
Exit Sub

Err_displayTescoLoads:
MsgBox Err.Description
Resume Exit_displayTescoLoads

End Sub

 

[Kevin]

Rhonda,

Just set the Record Source for the form to the SQL String.
Then Me.Requery
That should do it for you. You do not need the ADO
recordset code.

Hope that helps!

Kevin