×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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.

CODE --> vba

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...

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

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.

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

(OP)
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...

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

(OP)
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...

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

> HAS to be bound to a table or query.

So why not bind it to a query?

And instead of

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

do something like

db.CreateQueryDef "qryTest", strSql

(there's a little bit more to it than that, but not much)

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

(OP)
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.

CODE --> vba

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...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close