Solved

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

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This collection of functions covers all the normal rounding methods of just about any numeric value.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

815 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