Displaying a recordset on a continuous form

[Matt Brown]

I have the following code in a continuous form.
What i would like the form to do is loop through and display all of the records on the form.
As you can see i have placed a loop in the code thinking that this would then display all of the records in the recordset, but it still only pulls out one.

What am i actually missing to acheive this?

Thanks

Matt

Private Sub Command11_Click()
Dim db1 As DAO.Database
Dim rs1 As DAO.Recordset
Dim SQL As String

Set db1 = CurrentDb()

Select Case Me.Frame2

    Case 1
    SQL = _
    "SELECT tblCustomerXRefComponents.[Part No], tblCustomerXRefComponents.[Component Part No]," & _
    "tblCustomerXRefDescription.Description1, tblCustomerXRefDescription.Description2, " & _
    "tblCustomerXRefSuppliers.[Approved Supplier]" & _
    "FROM (tblCustomerXRefSuppliers INNER JOIN tblCustomerXRefDescription ON tblCustomerXRefSuppliers.[Component Part No] = tblCustomerXRefDescription.[Component Part No]) INNER JOIN tblCustomerXRefComponents ON tblCustomerXRefDescription.[Component Part No] = tblCustomerXRefComponents.[Component Part No]" & _
    "WHERE tblCustomerXRefDescription.[Component Part No] = '" & txtbxSearchCriteria & "' "

Set rs1 = db1.OpenRecordset(SQL, dbOpenSnapshot)

With rs1
   .MoveFirst
      Do Until .EOF
        txtbxComponentPartNo = rs1.Fields("[Component Part No]")
        txtbxComponentDesc1 = rs1.Fields("[Description1]")
        txtbxComponentDesc1 = rs1.Fields("[Description2]")
        txtbxComponentApprovedSupp = rs1.Fields("[Approved Supplier]")
        .MoveNext
      Loop
End With
rs1.Close
End Select
End Sub

 

[Rich]

Why would you need code to display records?

 

[Matt Brown]

I take it you are reffering to using a form with a query attached to it instead of hard coding it?

I basically don't want to use this method, i would prefer having an unbound form that would allow me to use the case statement to select what the user would be searching on.

What is wrong with coding it anyway?

 

[Matt Brown]

Thanks Pat, i now understand where you are coming from and why Rich questioned why i was doing it that way.

Your statement

"You can replace the form's RecordSource when you open the form. "

has interested me, can you give me an example using querydef's in this situation?

Many thanks

Matt

 

[Matt Brown]

Thanks Pat,

I appreciate your time on this, i take it using the Case switch statement would also be okay to use rather than if statements?

I suppose both will allow user selection to select which query to append to the forms recordsource.

Select Case Me.Frame2

    Case 1
    Me.RecordSource = "query1"

    Case2
    Me.RecordSource = "query2"

etc etc...

End select

I would then place the Parameter in the criteria row of the query.

Sorry thinking a bit outload here.

Many thanks

Matt