Referencing an unbound form control in a continuous form using recordsetclone

[dobseh]

I've got a continuous form that has three unbound textboxes, named "linked_swimmer1", "linked_swimmer2" and "linked_swimmer3" on. The form is a list of contact names and I am looking to display the people these contacts are linked to and but only show the unbound textboxes where there are matching records. I've cobbled together the below code from various things I've found on the intertubes, but what I am missing is how to reference those unbound textboxes on the form, based on the current record in the recordsetclone.

Private Sub Form_Load()

Dim db As Database
Dim rs As DAO.Recordset
Dim rsc As DAO.Recordset
Dim i As Long
Dim ctrl As Control
Dim strSQL As String
Dim IDX As Integer

' Empty and hide all of the linked_swimmer* text boxes
    
    For Each ctrl In Me.Controls
        If Left(ctrl.Name, 14) = "linked_swimmer" Then
            ctrl.Visible = False
            ctrl.Value = ""
        End If
    Next ctrl

' Because we are a continuous form we need to loop twice, once through the forms recordset as a clone (rsc) and then for each entry in the record itself (rs)
Set rsc = Me.RecordsetClone

rsc.MoveLast
rsc.MoveFirst

For IDX = 1 To rsc.RecordCount
' Debug.Print IDX
' Debug.Print rsc.Fields("contact_id")
'inner loop

    Set db = CurrentDb
    ' Get the swimmer names linked to that contact ID
    strSQL = "select swimmers.name from swimmers where contact_id = " & rsc.Fields("Contact_id") & ""
'   Debug.Print strSQL
    Set rs = db.OpenRecordset(strSQL)

    ' Check we aren't in an empty record or at the end of the recordset
    If rs.EOF And rs.BOF Then
    Else
    i = 1
        Do While Not rs.EOF
'        Debug.Print rs.Fields("name")
            'Set the value of each textbox to the swimmers name in the recordset
           ' Me.Controls("linked_swimmer" & i).Value = rs.Fields("Name")
            
            'unhide textbox
           '  Me.Controls("linked_swimmer" & i).Visible = True

            'iterate the counter that steps up to the next textbox
            i = i + 1
            rs.MoveNext
        Loop
    End If
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    ' end of inner loop
' goto next record in clone
rsc.MoveNext
Next IDX

End Sub

The various debug.print's shotgunned throughout that show that both loops are incrementing correctly and are returning the correct data for each iteration, so the only thing I am missing is how to actually get the data onto the form. Any help or pointers appreciated!

 

[theDBguy]

Hi. Unbound textboxes on a continuous form can only display one set of value. If you want multiple values, you'll have to bind them.

 

[dobseh]

Hi. Unbound textboxes on a continuous form can only display one set of value. If you want multiple values, you'll have to bind them.

I was aware of that, what I was hoping was that because I had a recordsetclone of the data used to build each row of the form that there was a way to get the unbound fields populated in the same way that Access itself uses to generate the bound fields.

 

[theDBguy]

I was aware of that, what I was hoping was that because I had a recordsetclone of the data used to build each row of the form that there was a way to get the unbound fields populated in the same way that Access itself uses to generate the bound fields.

If you can store those values from your recordsetclone somewhere that you can bind your textboxes to, then maybe. Otherwise, I don't think so.

 

[dobseh]

If you can store those values from your recordsetclone somewhere that you can bind your textboxes to, then maybe. Otherwise, I don't think so.

I'm not sure how that would help, because if I could bind the textboxes to the recordset, then I wouldn't need to loop through it in VBA.

I guess what I'm asking is that as Access knows it's absoluteposition in the recordset and that this is used to populate each rows bound fields on the form, so recordsetposition0.fieldname.value = formrow1.controlname.value, is there a way of making recordsetcloneposition0.fieldname2.value = formrow1.controlname2.value and referencing that in VBA. The same as the record pointer links the recordset position to a row on the form...

 

[theDBguy]

I'm not sure how that would help, because if I could bind the textboxes to the recordset, then I wouldn't need to loop through it in VBA.

I guess what I'm asking is that as Access knows it's absoluteposition in the recordset and that this is used to populate each rows bound fields on the form, so recordsetposition0.fieldname.value = formrow1.controlname.value, is there a way of making recordsetcloneposition0.fieldname2.value = formrow1.controlname2.value and referencing that in VBA. The same as the record pointer links the recordset position to a row on the form...

Not sure how many ways I can say it, unbound controls on a continuous form can only represent one value because there is really only one control on the form (even if it's showing multiple rows). For example, if the unbound textbox is called Textbox1, then using Me.Textbox1 = "something" will only assign that value to the one control called Textbox1. There is no such thing as Textbox1.Row10 or Textbox1.AbsolutePosition(3). Sorry. So, what you're trying to do with a recordset won't work, unless you use bound controls.

 

[CJ_London]

A recordsetclone is a clone of the recordset, not the form.

 

[dobseh]

Not sure how many ways I can say it, unbound controls on a continuous form can only represent one value because there is really only one control on the form (even if it's showing multiple rows). For example, if the unbound textbox is called Textbox1, then using Me.Textbox1 = "something" will only assign that value to the one control called Textbox1. There is no such thing as Textbox1.Row10 or Textbox1.AbsolutePosition(3). Sorry. So, what you're trying to do with a recordset won't work, unless you use bound controls.

Yes, like I said, I know this, you don't need to say it any other way, I already said if I could bind the textboxes then I wouldn't be here asking the question because I could just... bind the textboxes and it wouldn't be a problem. My point is that bound or unbound the record pointer points to one row and the absolute position in the recordset are both known by Access when the form loads which is why I am asking if it's possible to reference that pointer/position. This is also why I specifically didn't use me.textbox in my example.

 

[dobseh]

A recordsetclone is a clone of the recordset, not the form.

Yes, I know, I don't think I said it was otherwise?

 

[theDBguy]

Yes, like I said, I know this, you don't need to say it any other way, I already said if I could bind the textboxes then I wouldn't be here asking the question because I could just... bind the textboxes and it wouldn't be a problem. My point is that bound or unbound the record pointer points to one row and the absolute position in the recordset are both known by Access when the form loads which is why I am asking if it's possible to reference that pointer/position. This is also why I specifically didn't use me.textbox in my example.

Okay, so just to give you a final answer: You can't do what you're asking to do with Access using the recordsetclone or the absolute positions. Hope that's clearer.

PS. You may have not used Me.Textbox in your example, but how else were you thinking of assigning the value from the recordsetclone or the absolute position into those unbound textboxes? Just curious...

 

[dobseh]

PS. You may have not used Me.Textbox in your example, but how else were you thinking of assigning the value from the recordsetclone or the absolute position into those unbound textboxes? Just curious...

That was the point of my question and I gave the example of referring to the row in the form as the link to the recordset pointer in the clone. Access knows where the pointer is during and after the form has loaded, so it must have a reference that links the form row to the recordset to populate the bound fields. The nub of the question is; can that row to recordset reference be accessed? My example in reply #5 was:

recordsetposition0.fieldname.value = formrow1.controlname.value, is there a way of making recordsetcloneposition0.fieldname2.value = formrow1.controlname2.value

 

[CJ_London]

perhaps show an example of what you are talking about since it may be we are not understanding what you are trying to do.

 

[MajP]

That was the point of my question and I gave the example of referring to the row in the form as the link to the recordset pointer in the clone. Access knows where the pointer is during and after the form has loaded, so it must have a reference that links the form row to the recordset to populate the bound fields. The nub of the question is; can that row to recordset reference be accessed? My example in reply #5 was:

No. The reason is each row is just "paint" on the screen. When a continuous form is built each row is painted row by row. It does not really exist as an object or set of objects. So there is no way to refer to a row. It gets repainted continuously as you scroll or change values. Put a debug.print in the detail section onpaint and see how often that event fires. So even if you could reference the row you would have to cause it to change the value and repaint the form.

So I tried to see if in the OnPaint Event you can set a value in an unbound control. You can do other things like set colors per row. However, if you try to set a value in an unbound control (i.e. the linked swimmer) this puts the form in a continuous loop. In theory I would have thought this would be possible to do what you are asking by setting the values in the OnPaint event, but I think setting the values manually causes the record to continuously repaint and retriggering the onpaint event. Thus an endless loop.

There are plenty of better ways to do this. It would be trivial to show this is concatenated records in a single textbox. Or bring them in as three seperate fields from a linked crosstab query.

 

[dobseh]

perhaps show an example of what you are talking about since it may be we are not understanding what you are trying to do.

I think I'm just going to do this another way to be honest, I liked the concept of looping through the recordset as it felt like a nice clean way of doing it, but as pointed out by MajP, there are other ways of doing it.

 

[dobseh]

No. The reason is each row is just "paint" on the screen. When a continuous form is built each row is painted row by row. It does not really exist as an object or set of objects. So there is no way to refer to a row. It gets repainted continuously as you scroll or change values. Put a debug.print in the detail section onpaint and see how often that event fires. So even if you could reference the row you would have to cause it to change the value and repaint the form.

So I tried to see if in the OnPaint Event you can set a value in an unbound control. You can do other things like set colors per row. However, if you try to set a value in an unbound control (i.e. the linked swimmer) this puts the form in a continuous loop. In theory I would have thought this would be possible to do what you are asking by setting the values in the OnPaint event, but I think setting the values manually causes the record to continuously repaint and retriggering the onpaint event. Thus an endless loop.

There are plenty of better ways to do this. It would be trivial to show this is concatenated records in a single textbox. Or bring them in as three seperate fields from a linked crosstab query.

It is repainted, but that doesn't change the point that Access knows where the recordset pointer is and what data is in which row of the form. This is why you can do things like colours per row.

Anyway, as you say there are other ways of getting the data in, and I can potentially hide the empty swimmer names using conditional formating on the text box, so I guess I'll just approach it from that direction instead.

 

[MajP]

It is repainted, but that doesn't change the point that Access knows where the recordset pointer is and what data is in which row of the form. This is why you can do things like colours per row

No it absolutely does change the point, and obviously you are not listening. It only knows the "row" when it is painted, and you can only modify the "row" WHEN PAINTED. I can change colors of a control, but only at the time it is painted. Youi can wish all you want that Access can allow you to reference a virtual "ROW" of a form, but such thing does not exist. Look at the object model and show me a way to reference a Form's row or a control in a specific row. The fact that you can move around in the Form's recordset is irrelevant, because a recordset has a row. There then is a syncrhonization of the recordset to the repainting of the form. There is one and only one set of controls and each row is just paint on the screen.
So bottom line you can continue to wish that you can change the value of an unbound control per "row", but you cannot for the reasons explained. If it could be done, it would have to be done in the on paint event such as other properties. There is no way to navigate back to a row after it is painted and then repaint just that row. I have tried modifying the control values in the on paint, and you are more than welcomed to do the same. As far as I can tell, modifying the value of a control in the on paint will put it in a continuous loop. This is not the case for other properties. If you can find a solution so that it does not cause a looping then I will be proven wrong and you should theoretically be able to modify the value similar to some other properties that can be altered in the on paint.
Or better yet, you can stop spending your time by arguing the point and wishing Access had this capability, and solve this in lots of different ways. You will likely have to use conditional formatting to give the appearance of hiding the control. (background and foreground the same color.)

 

[The_Doc_Man]

So you've got three unbound text boxes and want things to go in them based on some other relationship. Well, DUH! Bind the text boxes and simply allow cases when they would be null if you don't have the contacts. Seems to me that you are going all around the mulberry bush, but when I read your original problem statement, it isn't clear why you wouldn't just bind the controls and let Access do its thing normally. At worst you are looking at a complex query involving one table with three aliased JOINs to get the three related swimmers.

 

[dobseh]

As far as I can tell, modifying the value of a control in the on paint will put it in a continuous loop. This is not the case for other properties

That was a lot of words to say you don't actually know...

As I have said, I'm going to do it another way, please carefully climb back down off of your high horse before you fall off and hurt yourself.

 

[dobseh]

So you've got three unbound text boxes and want things to go in them based on some other relationship. Well, DUH! Bind the text boxes and simply allow cases when they would be null if you don't have the contacts. Seems to me that you are going all around the mulberry bush, but when I read your original problem statement, it isn't clear why you wouldn't just bind the controls and let Access do its thing normally. At worst you are looking at a complex query involving one table with three aliased JOINs to get the three related swimmers.

Yep, that's pretty much what I am going to do(alias joins), I just liked the loop idea more and felt it was an idea worth following to see if it could be done.

 

[MajP]

Yep, that's pretty much what I am going to do(alias joins)

I am not sure if that can be done resulting in an editable form. That query will likely require and aggregate query or a subquery to get the three denormalized columns, making the recordset read only. If read only is fine, then simply do this in a report with Grouping. Then you can show the linked swimmers under the contact and only show the correct amount of contacts. But I do not know much. I would likely consider a temp table or disconnected ADO recordset, but that could require some code to push updates.