Solved

Use column property of combo box to update text box

Posted on 2004-08-19
7
294 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 51

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

696 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