Solved

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

Posted on 2012-03-22
8
312 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
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 …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

895 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

13 Experts available now in Live!

Get 1:1 Help Now