• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

Use column property of combo box to update text box

MS_Access experts

I have 2 combo boxes: cboA and cboB. Depends on what vlaue is being selected in cboA, the values in cboB change dynamically. For instance, in the Kelly Blue Book when you choose "Toyota" in the "make" field, the "Model" field will dynamically get all the Toyota models (Camry, Corolla, Matrix.....).

cboB is based on a table with a bound column (id) and an unbound column (actual text). I have set them both visible but the bound column width to 0.01", so when users pull down the list they will only see the text.

This works for me fine. However, cboB doesn't keep the text once you move focus to another row, although the id (bound column) has been saved to the base table.

There is a MS Knowledge base article (93138) suggested using a text box to display the value. I tested it and the text box still won't keep the text once moved focus to another row. When come back to the original row the text box is empty.

The control Source property of the text box is:
=dlookup("[function]","[t_function]","[function_id]="&Me.function_id)

Need quick help. Thanks.
0
HKBoyz
Asked:
HKBoyz
4 Solutions
 
Steve BinkCommented:
When you say "move focus to another row", do you mean you are changing records?  If so, then that behavior is by design.  You can alter the behavior by removing the ControlSource property on the text box and setting its value when you want it to be set through VB code.  Otherwise, the text will be updated anytime the form is updated, which includes when you move to another record.

FYI, if you do not mind have the combo box's LimitToList property set to True, you can set the column width on the bound column to 0" without any ill effects.
0
 
jadedataMS Access Systems CreatorCommented:
Greetings HKBoyz!

  You need to keep the boxes "in sync" in VBA by using events like AfterUpdate and OnCurrent.

  The comboboxes MUST be considered in a specific order or relationship.  You should avoid trying to make the combos consider EACH OTHER when trying to determine a rowsource.  You could easily get caught in a "circular" relationship between the two.

  If you are going to shrink a column to 0.01 you may as well set it to 0.

  The only data to be displayed in a combo box is column 0 (first column in a zero based system)  move your bound column to the right in the order and leave the column you wish to display in the first (0) position.  This does not affect how you set your bound column.

  If you want both elements displayed and the text box that set the controlsource to =forms.formname.cboControlname.column(0)

 

regards
jack
0
 
gemostCommented:
What code did you have in cboB's afterupdate, or click event? ALso check your forms oncurrent action to see if there is anything that is blanking this field out. When I use the same procedure to bring information into a form my combos keep their values but I am ussually requerying the form the boxes are on or another control.

gemost
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
HKBoyzAuthor Commented:
Guys, thanks for the quick response. I want the text from cboB (ie. the unbound column(1)) to be displayed in the text box. I know when I move to another row (ie. lost focus) the text from both cobB and the text box will be lost, and that's the problem. I want to keep the text in the text box.

Jack, would appreciate some sample codes of how to keep the boxes "in sync". Do the events need to be applied to the form level too?
0
 
HKBoyzAuthor Commented:
gemost
I don't have any event in cboB. Nothinb in forms oncurrent that's blanking it out.


0
 
jadedataMS Access Systems CreatorCommented:
In the AfterUpdate event of the combo box:

  me("txtTextBoxName") = me("cboComboName").column(1)


me("txtTextBoxName") must be a BOUND control to retain the value past the form close.

"in sync" combos
You can have a reference to ComboA in the rowsource of ComboB
    SELECT Field1, Field2 FROM Tablename WHERE ([KeyField] = forms.formname.cboComboname);

with this you only need to make sure that you requery the comboB with each update of ComboA and at OnCurrent.
0
 
HKBoyzAuthor Commented:
Thanks, guys. I gave most points to jadedata since it's the actual solution I need.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now