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

x
?
Solved

Form text boxes should update when the combo box on the form changes

Posted on 2016-08-11
6
Medium Priority
?
63 Views
Last Modified: 2016-08-11
I don't know why I can't get this to work the way it should.  It seems like it should be simple.

I have a form with a combo box that lists client names. There are two text boxes on the form that should show the client's city and state once the client has been selected in the combo box.

What I've been trying is

1.  In the txtClientCity Control Source:  =DLookUp("[CCity]","tblClients","[ClientID] = Form![ClientID]")
     In the txtClientState Control Source: =DLookUp("[StateCode]","qryClientStateCode","[ClientID] = Form![ClientID]")

The above produces the correct results but the information only shows in the text boxes AFTER the record is saved.  I'd like it to show as soon as the combo box selection is made.

I've also tried using the same code in the After Update property of the combo box and now I'm trying it in the On Change property.

2.  
Private Sub cboClientID_Change()

Me.txtClientCity.Value = DLookup("[CCity]", "tblClients", "[ClientID] = Form![ClientID]")

Me.txtClientState.Value = DLookup("[StateCode]", "qryClientStateCode", "[ClientID] = Form![ClientID]")

End Sub

Open in new window


The problem with the second attempt is that it modifies the City and State text boxes for ALL records instead of the current record being created.

In case it's pertinent:
The combo box has Control Source ClientID from the form's source tblJobOrders.  Combo Box Row Source: SELECT tblClients.ClientID, tblClients.CName FROM tblClients; Bound column: 2 and Column Widths:  0";2"

Can anyone either fix my code or suggest an alternate way of populating two text boxes based on the selection of a combo box on a form?  I'd like them to populate as soon as the selection is made in the combo box (instead of doing so after the new record is saved).

Thanks so much!
0
Comment
Question by:fabi2004
[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
  • 2
  • 2
  • 2
6 Comments
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 41752866
Me.txtClientCity.Value = DLookup("[City]", "tblClients", "[ClientID] = " & Form![ClientID])

and

Me.txtClientState.Value = DLookup("[StateCode]", "qryClientStateCode", "[ClientID] = " & Form![ClientID])

<<The above produces the correct results but the information only shows in the text boxes AFTER the record is saved.  I'd like it to show as soon as the combo box selection is made.>>

 Execute the statements in the AfterUpdate event of the combo and leave the text boxes unbound.   You'll also need to execute the same two statements in the OnCurrent event.

  A better approach would be to modify the forms recordsource and add the customer table along with a join.   As soon as you have a valid custID in the record, the city/state would then display because of the join.

Jim.
0
 
LVL 58
ID: 41752872
OnChange BTW fires for every keystroke in a control. This occurs before the BeforeUpdate and AfterUpdate events.   Typically you want AfterUpdate for something like this, when you want to react to a change value.

OnChange would only be used if you somehow wanted to validate a value as it was being entered character by character (ie. check the length).

Jim.
1
 
LVL 39

Expert Comment

by:PatHartman
ID: 41752949
Jim's "better approach" is the best way to do this, especially on a continuous form.  For a bound control, which this will become, the continuous form will show the value that is in each record.  For an unbound control, which it is now, Access can store only a single value at a time and that is why you see the same value on every row.

One warning though, when you use this technique, make sure to set the "lookup" controls' Locked property to Yes to avoid accidental updating.  A user may think if he updates the value here it affects only a single record and that is not the case at all.
1
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 1

Author Closing Comment

by:fabi2004
ID: 41753044
Big sigh of relief.  I knew I was doing something dumb, but I'd stared at it so long that I kept missing the obvious.

I added the Client and States table along with the Job Order table to the form's source and it works like a charm.  The way it's supposed to.  

Thank you so much!

BTW  If I "lock" the combo box then I can't select from it.  Is that what you were referring to?  I've set it up so that the Control Source is tblJobOrders.ClientID and the Row Source is SELECT tblClients.ClientID, tblClients.CName FROM tblClients;  with Bound Column 1.  This combo box also allows a user to add a new client if it's not on the list via a dialog and pop-up form, so I don't know is locking it will break that part of it.  Sorry, I can open another question for this.  I closed the previous one before I started typing.
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 41753060
You don't lock the combo, you lock the city and state controls.  Those are the "looked up" values.
1
 
LVL 1

Author Comment

by:fabi2004
ID: 41753089
Ah!  That makes sense.  Thank you so much.  I've never done that before and I'm sure I've dodged a bullet not having the underlying tables changed by accident before.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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 …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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. …

618 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