Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Data source (Access 2000)

    I have created a simple form based mostly on one table, so I have selected that table as the data source in the form properties. However, I want to add some fields from another table to the form, but I can't seem to set the field properties to enable this. How can I do this?

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data source (Access 2000)

    My best suggestion without know what exactly you're trying to do is create a subform. I'm assuming the data you're entering into the text boxes will be kin to the record selected on your main form, just stored in a separate table. In that case, create another form, with the data source as your 'other' table, and then on your main form, insert a subform, and follow the wizard to link your subform's key to the PK on your main form.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

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

    Re: Data source (Access 2000)

    You haven't really provided enough information for us to provide a good answer.

    If there can be multiple records from the other table that correspond with a record in the main table, Jeremy's suggestion of using a subform is the way to go.

    If you merely want to look up some values in the other table, there are several possibilities:
    <UL><LI>Use the DLookup function in the control source of a text box. The general syntax is

    =DLookup("fieldname", "tablename", "where-condition")

    but we'd have to have detailed information if you want specific help with that.
    <LI>Add the main table and other table to a query, join them on the appropriate field(s), and add the fields you need to the query grid. Use the query as record source for the form. The link field should be the primary key in the "other" table.[/list]HTH

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Data source (Access 2000)

    Thanks for your reply. I've now created a query with all the fields I need from both tables, but now there is a problem in that no records are being displayed because the field in the main table that I am looking up in the other table is currently blank.

    The main (client) table has various client details and the other (type) table has four types (1,2,3,4) but currently the type field in all the records in the main client table is blank (it will be filled in later).

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

    Re: Data source (Access 2000)

    Open the query in design view.
    Double click the line joining the main table and lookup table.
    Select the option to display ALL records from the main table and only related records from the lookup table.
    Click OK.

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Data source (Access 2000)

    Yes all fixed thanks, Hans.

Posting Permissions

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