?
Solved

Access Combo box for data entry

Posted on 2011-04-27
10
Medium Priority
?
466 Views
Last Modified: 2012-05-11
I want to create a combo box to fill or update record
I have table fields "Supplier Name" and 'Supplier Address'
Want to create a combo box using combo box wizard which has both Supplier Name and Supplier address list. While I choose a Supplier Name the Supplier Address of that Supplier will be fill the next text box field of the data entry form and whatever choosen it will be entered to the record field Supplier Name and Supplier Address of the table.
 Thanks for you co-operatioin in advance.
0
Comment
Question by:alam747
  • 5
  • 4
9 Comments
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 35483073

1. Create a query for your combo based on the supplier table.  Include the supplier name and address only.

2. Create the combo control.

3. Set the following:

Control Source:  Should be set to the field in your record that will store the supplier name
Row Source Type: Table/Query
Row Source: Name of the query you created above.  You can use the drop down to the right to pick it.
Number of columns: 2
Bound column: 1
Column Widths: 1.5";1.5"
Limit To List: Yes
Auto Increment: Yes

 In the AfterUpdate event of the comb control

  me.<name of control that holds address> = Me.<name of combo control>.Column(1)

  And a note; not sure what your design is, but you should not be storing the name and address of the supplier in another table, but rather the key that points to that supplier.  You can then fetch the supplier name and address (and any other info) as needed.

  Data that belongs to a "supplier" (Name, Address, phone number etc), belongs in only one place, which is the supplier table.  

HTH,
JimD.

 

0
 

Author Comment

by:alam747
ID: 35486768
Would you explain more little detail of
me.<name of control that holds address> = Me.<name of combo control>.Column(1)

Its not clear to exactly what I need to replace, if possible please expain with exact code to write.

Thanks
0
 

Author Comment

by:alam747
ID: 35488434
My table name is tblSupplier and the fields are Supplier Name and Supplier Address
Please advice me if I want to create the combo box, if I choose from drop down list Supplier Name it will bring the Supplier address display beside the combox , if not exist allowed me to enter new Supplier name and supplier address in that box which save to supplier table.

Thanks
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58
ID: 35490839
Sorry, I somehow missed your first comment from yesterday

<<
me.<name of control that holds address> = Me.<name of combo control>.Column(1)
>>

  Somewhere on your form, you have a control to hold the supplier address.  If you right click on that control and select properties, you will get the property sheet.  In that list of properties, take a look at the name property.  Let us say it simply says "supplieraddress".  Now do the same for the combo control.  Let us say that this one says "combo3"

  Now that you have both names, bring up the property sheet for the combo control and click on the event tab.  Find the AfterUpdate event.  Click the drop down to the right and select [Event Procedure].  Then click the builder button (...) off to the right.

  A code window will pop up.

 You would enter:

  Me.supplieraddress = me.combo3.Column(1)

  and close the window.  What this does is after a new value is selected in the combo (the Afterupdate event "fires" and executes the code you just entered), it takes the 2nd column in the combo and places it in the supplies address control.

<<if not exist allowed me to enter new Supplier name and supplier address in that box which save to supplier table.>>

  Let us get this first part working, then we can do a Not In List event.

JimD.




 
0
 

Author Comment

by:alam747
ID: 35736531
I did the following but yet to get result I am expecting..
I create the Supplier combo using a query (Supplier Query) which contain table (tblSupplier) fields Supplier Name and Supplier Addressé
Combo box name is Combo55
Control Source   =[Supplier Query]![Supplier Name]

Text Box name is SupAddress
Control Source =[Supplier Query]![Supplier Address]

After update even of the combo box are as below;
Private Sub Combo55_AfterUpdate()
Me.SupAddress = Me!Combo55.Column(1)
End Sub

But when I choose a name from the combo box it does not pull the supplier address on the text box.
Even when I open the form I saw #Name? on the text box.Seems somthing wrong in expression.
Would you please advice how to fix it.

Thanks
0
 
LVL 58
ID: 35737266
<<Combo box name is Combo55
Control Source   =[Supplier Query]![Supplier Name]

Text Box name is SupAddress
Control Source =[Supplier Query]![Supplier Address]
>>

  This is incorrect.   The controlsource for the combo should be set to the field name in the *forms* rowsource.  If you are just using this combo to pick a supplier, then it should be "unbound" (no controlsource set at all).

  SupAddress should be unbound as we are simply using it to display the data from the combo and not storing it.

With combo's and list boxes, keep in mind the following:

1. When you drop a combo down (or have a list box), the row source type and row source properties determine where the list data comes from.  In this case, Row Source Type should be set to table/query, and the Row Source should be set to 'Supplier Query'

2. The number of columns available in the combo or list box are controlled with the Column Count property.  In this case it would be 2, as the Supplier Query has Supplier Name and Supplier Addressé.   You do not have to include all the fields from a query, but in general if your query has more columns then you need, you should write another query.

  For example, if you used a query that had selected all fields from the supplier table, you could get just the first two columns for the combo.  However getting all the rest of the columns then is simply a waste.

3. The column width property determines the width of each column.  It is possible to hide a column by setting its width to 0.   So for this combo, if I wanted the address hidden, I would set the column widths property to:

    1.5";0"

   The column is still there and the data available, but it is just hidden from display

4. Now when you choose from that list, the value for that row is stored in the field named in the combo's controlsource property.  The column of the list that the value comes from is determined by the BOUND COLUMN property.  In this case, we want it to be column 1, which is the supplier name.

 Now you will find that this works:

Private Sub Combo55_AfterUpdate()

  Me.SupAddress = Me!Combo55.Column(1)

End Sub

  One note here; when using the .Column property in code, it is 0 based, so our first column, supplier name, would be 0, and our second column in the control, supplier address, would be 1.

Hope that helps.  If not, I'll work up a small example for you.

JimD.

PS.  And by the way, it is a bad idea to have spaces in any type of object name.    


0
 

Author Comment

by:alam747
ID: 35739152
Thanks a lot for your advice.
I changed the text box as unbound(just delete =[Supplier Query]![Supplier Address]) from text box control source property.
When I choose from the drop down menu of the combo box nothing chosen and it showing the drop down list of supplier name if I choose one it not choosen and therefore nothing pull to the supplier address text too.
Please advice where need to check to fix it.

Thanks
0
 
LVL 58
ID: 35741863
A sample DB might be helpful.  Please download the attached and open frmPO.  Then choose a supplier from the drop down.  

  In this case, I am storing the supplier ID with the PO record, which the form is based on, but the form is also displaying the supplier name and address, which was gotten from the combo after choosing a supplier.

JimD.
combo-example.mdb
0
 

Author Closing Comment

by:alam747
ID: 35868409
Thanks for your advice
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

850 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