Access Combo box for data entry

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

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

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.




 

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

<<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.    

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

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

Thanks for your advice