Solved

Use column property of combo box to update text box

Posted on 2004-08-19
7
283 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

708 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

15 Experts available now in Live!

Get 1:1 Help Now