* On a form i have a Combo Box with the Field NAME where the user chooses a name (eg. Jane)
~~What i want to happen is text boxes Populate on the form the rest of the fields (eg. Address,Suburb and Country)
Is this what you are looking to do? (see the attachment)
In the demo, I created a form and two tables. One table is used to provide the addresses for the combo box and the other table is simply used to save the results of the selected address on the form.
When you change the combo box, an event behind the form fires off and this code updates each of the form fields:
Private Sub cmbSelectAName_AfterUpdate()
.txtName = Me.cmbSelectAName.Column(0, Me.cmbSelectAName.ListIndex)
.txtAddress = Me.cmbSelectAName.Column(1, Me.cmbSelectAName.ListIndex)
.txtSuburb = Me.cmbSelectAName.Column(2, Me.cmbSelectAName.ListIndex)
.txtCountry = Me.cmbSelectAName.Column(3, Me.cmbSelectAName.ListIndex)
In this example I just made the combo box carry the other fields but hide them in the combo box (go to the combo box properties and you will see 1,0,0,0 for the column widths - I only show the first column - the Name column to the user).
There are other ways to get this same effect. You could run a little code that uses ADO or DAO to look up the address information from the table directly whenever the combo box name is clicked, changed or updated.
.NAMES = Me.NAME.Column(0, Me.NAME.ListIndex)
.ADDRESS = Me.NAME.Column(1, Me.NAME.ListIndex)
.SUBURB = Me.NAME.Column(2, Me.NAME.ListIndex)
.POSTCODE = Me.NAME.Column(3, Me.NAME.ListIndex)
are there any differences between the demo and your form? for example, I used 4 columns in my combo box.
hpicken is right - you can shorten the format for the reference to the column objects. I kept them long because, as you type, if your object and form names are correct, VBA should help you fill in the properties of the combo box as you type the names of the field after the ME object.
what error # are you getting?
can you take your database and extract just a small sample of the MDB project containing just the tables and form with the combo box so we can see it here?
First, remember there is a distinction between Me.txtFirstName and Me.FirstName. The first item references the current value of the text box (which you may or may not have bound to an underlying recordsource). The second item references the actual field in the recordsource. There are times when one might be preferable over another, although I don't have an example off the top of my head.
Second, get in the habit of using good naming conventions for your controls. For example, use txtFirstName for a text box, or cmbPickAName or cboPickAName for a combo box.
I've found sometimes (in addition to reserved words) that not distinguishing between the control on the form and the field name underneath may cause Access to give you bizarre errors.
Besides it's a nightmare to debug something called TextBox100 or just a control called generically Name.
For my own projects I spell out everything to the point of sillyness. For example, I would name a query, qryCustomerOrders_Open so my clients know what the query is supposed to do. If I write a custom VBA function to get the order date for a particular customer order, I might call the function, GetCustomerOrderDate(). Six months later, I don't have to remember what the heck I did then
yeah i know my names are real bad and i was going to do a fresh one as this is only a small database. and after all my changes and all i think i have stuffed it all up. here is a copy if any one wants to have a look for me.. my calculator in the orange box works well. i have more things i will add later like a subform and some query buttons but one thing at a time i guess.