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?

 

[Pat Hartman]

Your code is simply overlaying one record with the next. You don't have access to events that build multiple instances of a form so you can see it in continuous form view. Access populates the form behind the scenes and creates enough instances to fill the current form size. I don't believe that you will be able to create an unbound continuous form. Access forms just don't work that way and the rest of us don't want them to. If you don't want to use bound forms, you'll be much happier writing hundreds or thousands of lines of code for each form in VB.

You could build an unbound form in single form view. If you want to show multiple records on the one form, you would need to create a separate set of controls for each record that you want to show. So if you want to show 10 records, you'll need 10 sets of controls and your code would need to put the first record in set1, the second record in set2, etc.

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

- you don't need to use an unbound form to do this. You can replace the form's RecordSource when you open the form. I would recommend using querydef's with parameters rather than embedded SQL strings. Querydef's are more efficient since they are bound when they are saved. Your SQL strings need a lot of pre-processing EVERY time they execute. This adds overhead (small) and causes bloating (can be significant).

 

[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

 

[Pat Hartman]

In the Form's Open event:

If Forms!YourForm!SomeField = "SomeValue" Then
    Me.RecordSource = "query1"
Else
    Me.RecordSource = "query2"
End If

 

[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