Autoform Tabular subform: how to reference controls in a certain row?

[cadoltt]

Hi Everybody,


My main form has a subform, which I created using the Autoform Tabular option. The main form and the subform are underlined by two tables havind one (Main form) to many (subform) relationship. The subform has two text fields, one of which is bound to a table field, the other is unbound. I've also added a button to the form so now it has totally three controls (lined up horizontally - the usual arrangement of Autoform Tabular option). Clicking the button should do some calculation and put the result into the unbound text box.

The subform can have multiple rows of these three controls. Now the question: how can I refer to the button (or to the unbound field) in a certain row, when doing the VBA coding?


Thanks in advance,

Alex

 

[MajP]

You most likely can not do what you are saying that way. There are only one instance of a control. So if you set the value in code for an unbound control, all other depictions of that control will change in value as well. Same thing if you changed the color or other properties; all will change.

The way around this is to build a calculated control or custom function. Tell us what you want calculated. You probably want this to happen automatically not require the user to hit a button. A calculated control would do this.

 

[TheAceMan1]

How are ya cadoltt . . .

[blue] . . . how can I refer to the button (or to the unbound field) in a certain row, when doing the VBA coding?[/blue]

One way would be to set focus to the record in question then hit the button. However for full automation (no button necessary) [blue]MajP's[/blue] method is the way to go! . . .

See Ya! . . . . . .

Be sure to see thread181-473997

 

[cadoltt]

MajP, TheAceMan1,

Thanks for joining. Actually I need both prepopulated unbound boxes and a possibility to change the value in a certain box after hitting the corresponding button. Yes, I am thinking about a function for the calculation because I will need, using the record values, query some other tables for the right info to put into the unbound box.

But let's for simplicity sake say I need to prepopulate unbound boxes with values from the bound box which is (again putting simply) the record number.


So far I didn't do anything better than this:

Private Sub btn_Click()
  txb_unbound.Value = txb_bound.Value
End Sub

Private Sub Form_Load()
  txb_unbound.Value = txb_bound.Value
End Sub

The btn_Click() sub works somehow but in the way MajP foresaw: all depictions of that control change into the same value. For example, if I hit the button at the second record all unbound boxes get filled with '2'. (BTW, I was looking for the right term, now I know, it's 'depictions' ).

The Form_Load() sub doesn't do any [visible] job at all - the boxes stay clear.

So your ideas, guys, for a solution will be greatly appreciated.


Thanks,
Alex

 

[MajP]

Lets say you have a bound text box "txtBxBound" and you want to do some calculation on it and show it in an unbound text box. Lets say you want to multiply it by 50. Then the control source for the unbound text box would be something like

= [txtBxBound] * 50

lets say it is more complicated and you built a function that takes in the value of txtBxBound and does a very complicated calculation

public function someCal(varVal as variant) as double
do something complicated
someCal = something
end function

Then in your calculated control the control source would be
= someCal([txtBxBound])

In these cases you can get unique values in each unbound text box because it uses the value of the bound textbox to calculate the value.

 

[cadoltt]

MajP,


Thank you, it works. The boxes get prepopulated all right. But how about the second thing, the button? Now when I hit it I get an error message "You can't assign a value to this object". Similar thing happens if I try to change the value in the box manually. Seems assigning the control source to a function blocks it from other ways to enter data.

More ideas?

Thanks again,
Alex

 

[MajP]

Yes that is correct. But is in an unbound text box. An unbound text box is for display purposes, why would you want to enter data into it, it will not get saved anywhere. Normally a calculated value is not saved in the database. If you can calculate the value on the form then you can calculate the value anywhere in your database.

If you need to save this value for some reason, and you want to either automatically calculate or change the value manually then this field will have to be bounded.

If you want to prepopulate a field with a calculated value you can set in code its default value. So when you enter that control it can get its calculated value based on the value of the other text boxes, but then you can edit it. Again it would need to be bounded. If it is unbounded with out a calculated control, any change in one changes all.

 

[cadoltt]

MajP,

It's getting clearer and now I almost see how to handle this.

Thanks a lot!
Alex