Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Use column property of combo box to update text box

Posted on 2004-08-19
7
Medium Priority
?
299 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 80 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 1840 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 80 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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 1840 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

636 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