Solved

Filling in a text box automatically based on a selection in a previous combo box

Posted on 2012-03-22
8
311 Views
Last Modified: 2012-08-14
I have 5 fields in a table: Consignee (to), Consignee street, Consignee city, Consignee state and Consignee zip.  All the information for each record has been entered into the table.  I then created a main table and created a field called Consignnee (to) in it and made it a combo box that points back the Consignee (to) field in the first table so that i have a drop down box for users to select from instead of typing everytime.  When a user selects a name from the Consignee (to) box on the form i need the other 4 fileds to be filled in automatically based on their selection in the Consignee (to) box.  I have the field in the table set to column 1 as containing the data for that field and the column count set to 5.  On the form i have all the fields showing and in Consignee Street  i have the control source set to me.consignee_to_.column(2).  When i open the form and select an answer in the Consignee (to) the Consignee Street just shows Name# in the field and not the address.
0
Comment
Question by:AisinAuto
  • 2
  • 2
8 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37757993
If you use a query or the entire Consignee table as the rowsource for your combo box, then you can use the AfterUpdate event of that combo to display the other information.  When I do this, I generally set the Locked property of those textboxes to Yes, to prevent the use from thinking they can be changed.  if the SQL looks like:

SELECT Consignee, [Consignee street], [Consignee city], [Consignee state], [Consignee zip]
FROM tbl_Consignees

Then the code would look something like:

Private sub cbo_Consignee_AfterUpdate

    me.txt_ConsigneeStreet = me.cbo_Consignee.column(1)
    me.txt_ConsigneeCity = me.cbo_Consignee.column(2)
    me.txt_ConsigneeState = me.cbo_Consignee.column(3)
    me.txt_ConsigneeZip = me.cbo_Consignee.column(4)

End Sub
0
 
LVL 17

Accepted Solution

by:
ramrom earned 500 total points
ID: 37758028
Your description is vague and unclear - raises many questions.
I don't have time or inclination to ask the questions.
Instead I offer a potential solition:

sub consignee_to__afterupdate() 
txt1 = consignee_to_.column(1)
txt2 = consignee_to_.column(2)
txt3 = consignee_to_.column(3)
txt4 = consignee_to_.column(4)
txt5 = consignee_to_.column(5)
end sub

Open in new window


I used "txt1" etc since I don't know the names of your text boxes.

I highly recommend avoiding blank and special characters in field and control names

Consignee_to, Consignee_street, Consignee_city, Consignee_state and Consignee_zip are much better choices for many reasons.

I see no purpose for the main table. Please elaborate.
0
 
LVL 17

Expert Comment

by:ramrom
ID: 37758041
As you might guess I was perfecting my reply while fyed was perfecting his. So you have 2 similar answers. Have fun with it.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37758612
Really?  

Answers were identical, one was submitted 10 minutes before the other.
0
 
LVL 1

Author Comment

by:AisinAuto
ID: 37758914
i chose the second one because the frst also had a query in it which i did not use.  I did only what the second response said and it worked.  If you want to split it evenly that is fine.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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 …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

757 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

20 Experts available now in Live!

Get 1:1 Help Now