Referencing unbound control values on a continuous form

[PeteG]

Hi,

I have a continuous subform bound to a control source (a table) and am displaying each field in a textbox. All fine so far. I've added an unbound text box that contains a formula along the lines of "if the checkbox bound to colA is ticked then display ColB + Col C, else display just ColB". This works fine as well. As I have four rows in the table, all with different values in them, I have four instances of this unbound textbox. As I tick or untick any of the check boxes, the value in the relevant unbound text box changes as per the formula I have used.

My question is, how can I reference these four different values? For example, how could I iterate through the rows in my continuous form in VBA and print out the value of that unbound text box in each case?

Thanks

 

[MajP]

Instead of an unbound textbox, but the formula as a calculated field in a query and make the query the recordsource of the subform. Then you can loop the recordset

Something like

dim rs as dao.recordset
dim strOutPut as string
'assume code on main form
set rs = me.subformcontrolname.form.recordsetclone
do while not rs.eof
  strOutPut = strOutPut & rs!calculatedfieldName & vbcrlf
  rs.movenext
loop
debug.print strOutPut

 

[PeteG]

That's great, thank you! That does just what I need. Out of interest (I've been away from Access for a while ), is there a way to iterate through the different values that would be contained in each instance of an unbound text box? Like, reference the form detail section and then for each row that is there, get the value of the unbound textbox?

 

[MajP]

To do this with an unbound control you would have to actually have to move to each record in the form and then reference the control by name, then ensure you return to the original location. The code would be more complex and problematic because you are physically moving the form records. If you could slow it down you would see each record on the screen.

 

[PeteG]

ok, thanks again

 

[MajP]

The recordsetclone makes a shallow copy of the recordset of the form and you can loop it without moving records in the form. If instead you use the recordset property
set rs = me.subformcontrolname.form.recordset
that is the actual recordset. As you loop the actual recordset the form records move. Then you can reference the control by name because the record of the form moved and the value returned is the current value in that control.

I have four instances of this unbound textbox

.
That is not a correct statement. On a continous form there is only one instance of each control, each rendering is "paint on the screen" (that is how I heard it described). So if you change a property of a control all renderings change. The values are painted when it is rendered.

 

[PeteG]

Thank you again - two solutions to choose from!