Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2004
    Posts
    85

    Talking Unanswered: OK Populate Question. still having trouble

    OK heres the deal

    TABLE1

    Name------address---------Suburb-------Country
    __________________________________________
    Bob...........5 fake st...........Geelong.........Australia
    Jane..........12 Mills rd..........Hampton.......England


    * 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)

    __________________________________________________ ______________
    |Combobox|*|----------| address |----------| Suburb |-------|Country|
    __________________________________________________ ______________
    Jane...........................populate.................populate.............popu late


    Thanks for your time..

  2. #2
    Join Date
    Dec 2003
    Posts
    172
    Jessy,

    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()

    With Me
    .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)
    End With

    End Sub

    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.

    Hope this helps.

    Joe G
    Attached Files Attached Files

  3. #3
    Join Date
    Apr 2004
    Posts
    85

    Error in my code somewhere

    Private Sub NAME_AfterUpdate()

    With Me
    .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)
    End With

    End Sub

    i get a error

    my combo box is called Name

  4. #4
    Join Date
    Aug 2004
    Posts
    40
    Another way to do it

    Code:
    Private Sub NAME_AfterUpdate()
    
    
    me!NAMES = NAME.Column(0) me!ADDRESS = NAME.Column(1) me!SUBURB = NAME.Column(2) me!POSTCODE = NAME.Column(3)
    End Sub

  5. #5
    Join Date
    Dec 2003
    Posts
    172
    look at the properties of your combo box

    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?

  6. #6
    Join Date
    Apr 2004
    Posts
    85
    Me!NAMES = NAME.Column(0)

    tells me NAME is an Invalid Qualifyer


    Must of failed the Drug test at the Olympics or something

  7. #7
    Join Date
    Aug 2004
    Posts
    40
    Take out name as this should already be retreaved for the field itself.

    Make sure that the bound column in the field properties is bound to the correct column

    eg if the NAME field is that first in your query then it is bound to column one

    One of the confusing things with Access is when you're dealing with the field properties the coloumn count starts at 1 but when you start using VBA then the column count starts from 0.

    Code:
    Private Sub NAME_AfterUpdate()
    
        me!ADDRESS = NAME.Column(1)
        me!SUBURB = NAME.Column(2)
        me!POSTCODE = NAME.Column(3) 
    
    End Sub

  8. #8
    Join Date
    Dec 2003
    Posts
    172
    Jessy

    Two more things to keep in mind...

    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

    Hope this helps!
    Last edited by JoeG; 08-27-04 at 23:44.

  9. #9
    Join Date
    Apr 2004
    Posts
    85

    here is a copy of my stuffed up database

    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.
    Last edited by jessy; 09-24-04 at 08:23.

  10. #10
    Join Date
    Dec 2003
    Posts
    172
    try this one
    Attached Files Attached Files

  11. #11
    Join Date
    Apr 2004
    Posts
    85
    \me whips out the jar of Gold Stars and licks one and after relising it was a sticker removes it from her toungue and slaps it on JoeG's Forehead Wunderbar!!!


    thanks *snaps for JoeG!

  12. #12
    Join Date
    Dec 2003
    Posts
    172
    glad to help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •