Loop through record set to populate unbound form and unbound fields - only get one record 1

[waubain]

I have multiple forms that all do the same thing and I am trying to use one one unbound form with unbound controls instead. The form is set to a continuous form. The unbound form has 3 unbound fields.

The below code works but I only get the last record. I have tried several iterations, but I either get the first record or the last. I am trying to get the first form to work and then will adapt to all the forms.

Any suggestions would be appreciated.

Private Sub Form_Load()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strUsort As String
    Dim ctl As Control
    
    If Len(Me.OpenArgs) > 0 Then
        ' Position of the pipe
        intPos = InStr(Me.OpenArgs, "|")
        If intPos > 0 Then
           ' Retrieve Control Name from the first part of the string
            strSQL = Left$(Me.OpenArgs, intPos - 1)
            ' Retrieve Value to Assign from the end of the string
            strUsort = Mid$(Me.OpenArgs, intPos + 1)
        End If
       
        Set db = CurrentDb
        
        For Each ctl In Me.Controls
            Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
        
            rs.MoveFirst
            Do While Not rs.EOF
                If ctl.Name = "intRecordID" Then
                    ctl = rs!pk_DomainID
                    ctl.Visible = True
                End If
                If ctl.Name = "intStepNumber" Then
                    ctl = rs!DomainSort
                    ctl.Visible = True
                End If
                If ctl.Name = "txtDescription" Then
                    ctl = rs!DomainDescription
                    ctl.Visible = True
                End If
            rs.MoveNext
            Loop
            rs.Close
        Next ctl
     End If
    
    'Cleanup
    db.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

You don't know what you don't know...

 

[Andrzejek]

You may have a better luck asking this question in one of the Access forums.

Are you hoping to get your Form and be able to edit the data displayed?
If you just want to show data, why not use a Grid?

More about What is continuous form?


---- Andy

There is a great need for a sarcasm font.

 

[waubain]

Andy,

Thanks for the response. Yes, I need to be able to edit the data displayed. I need to be able to see all the rows at once for ranking, so a continuous for is required. I have this all working, but there are 4 separate forms for 4 different tables, but each form does the same thing. I was trying my luck at using 1 form and call the form up from another form.

I will give this a day or so, and then move it to an Access forum, if no response.

Thanks again.

You don't know what you don't know...

 

[waubain]

After further searching, it appears that a continuous form HAS to be bound to a table or query. So for now I will continue with the multiple forms.

You don't know what you don't know...

 

[strongm]

> HAS to be bound to a table or query.

So why not bind it to a query?

And instead of

[tt]Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
[/tt]
do something like

[tt]db.CreateQueryDef "qryTest", strSql
[/tt]
(there's a little bit more to it than that, but not much)

 

[waubain]

strongm,

Thanks for pointing me in a different direction. Since this db is for a friend with no vba experience (yet!), I went with the simplest idea.

Me.RecordSource = "SELECT * FROM [qryClass]"

To keep it simple, I made 4 queries and pass the name with OpenArgs. I gave the field aliases that matched the control source name.
Interestingly, the continuous form only showed two records, but if I dragged the form to a larger size, there were all there.

You don't know what you don't know...