Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Field Lookup in Table (XP 2000)

    Good morning all,

    I have a field in a table where i am currnetly entering data. I want to set it to lookup current values already in the field, and also be able to add new values. It is for names, many of which reoccur so I think this will be helpful.

    I have the following settings, but it is not doin what i want.

    Display Control : ComboBox
    Row Source Type : Value List
    Row Source : <table1>
    Bound Column : 4 (The field is the 4th in my table)
    Column Count : 4
    Col hds : no
    col widths : 0;0;0;2
    ....
    Limit to list : no

    is there a way of doing this or do i have to put all the names in a seperate table that is linked?
    Thanks,

    pmatz

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Field Lookup in Table (XP 2000)

    You don't need to do this in the table; I would do it in the form you use to enter/edit data in the table. Put a combo box bound to the name field on the form, and set its properties as follows:

    Row Source Type : Table/Query
    Row Source : SELECT DISTINCT [Field_Name] FROM [Name_of_Table]
    Bound Column : 1
    Column Count : 1
    Col hds : No
    Col widths : leave blank or set one width
    Limit to list : No

    Note: replace Field_Name and Name_of_Table by the actual names. It would be even better to create a query with this SQL, and set the Row Source of the combo box to the name of this query.

    If you enter a new name and move to another record, the list part of the combo box will not display the new name automatically; the combo box has to be requeried in the On Current event of the form for this:

    Private Sub Form_Current()
    Me.[ComboBox_Name].Requery
    End Sub

    where ComboBox_Name must be replaced by the name of the combo box.

  3. #3
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Lookup in Table (XP 2000)

    Thanks, thats great. the DISTINCT part just gives the one occurence of multiple entries - just what i needed!

    I have done it in the table just as a temp measure because i am just doin data in oput at the mo [img]/forums/images/smilies/smile.gif[/img]
    Thanks,

    pmatz

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Field Lookup in Table (XP 2000)

    Be aware, though, that the list part of the combo box will not be updated automatically during data input in the table. You have to do a Records | Refresh to update it (or close the table and open it again.)

Posting Permissions

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