Solved

Use column property of combo box to update text box

Posted on 2004-08-19
7
284 Views
Last Modified: 2012-06-27
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
Comment
Question by:HKBoyz
7 Comments
 
LVL 50

Assisted Solution

by:Steve Bink
Steve Bink earned 20 total points
ID: 11842498
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
 
LVL 32

Assisted Solution

by:jadedata
jadedata earned 460 total points
ID: 11842500
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
 
LVL 3

Assisted Solution

by:gemost
gemost earned 20 total points
ID: 11842514
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:HKBoyz
ID: 11842568
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
 

Author Comment

by:HKBoyz
ID: 11842634
gemost
I don't have any event in cboB. Nothinb in forms oncurrent that's blanking it out.


0
 
LVL 32

Accepted Solution

by:
jadedata earned 460 total points
ID: 11842677
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
 

Author Comment

by:HKBoyz
ID: 11842825
Thanks, guys. I gave most points to jadedata since it's the actual solution I need.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now