Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Nov 2004
    Location
    London, Gtr London, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup Query (Access 2003)

    Hi,

    I have two tables Tab_Target_Control_Record and tab_objectives. I want to set up a lookup field in the first table (Tab_Target_Control_Record), so that when a particular field is entered (TCR_1a), the next field (TCR_1b) is automatically completed on the basis of data held in the second table (tab_objectives). The table tab_objectives has only the two fields TCR_1a and TCR_1b.

    Does anyone have any suggestions how I set this up?

    (The table Tab_Target_Control_Record is also the basis of a form. )


    Thanks.

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

    Re: Lookup Query (Access 2003)

    If the value of TCR_1b is completely determined by that of TCR_1a, i.e. the user shouldn't change the value of TCR_1b, you don't need TCR_1b in the table Tab_Target_Control_Record, since it is derived information. You can create a query based onTab_Target_Control_Record and on tab_objectives linked on the appropriate field, and get the derived field from tab_objectives.

    If the user should be able to change the value of TCR_1b, you do need this field in the Tab_Target_Control_Record table, and you can set its value in a form based on this table, in the After Update event of the control (text box or combo box) bound to TCR_1a. There are at least two ways to do this, more details would help deciding the best option.

  3. #3
    New Lounger
    Join Date
    Nov 2004
    Location
    London, Gtr London, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Query (Access 2003)

    There may be instances when the user should be able to change the value of TCR-1b so the later option sounds better. Do you need more information to be able to outline the two options?
    Thanks,

    Katya

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

    Re: Lookup Query (Access 2003)

    Let's try this first:
    - Create a query based on the two tables, joined on TCR_1a in Tab_Target_Control_Record and the corresponding field in tab_objectives.
    - Double click the join line and select the option to display *all* records from Tab_Target_Control_Record.
    - Add TCR_1a and TCR_1b form Tab_Target_Control_Record to the query grid, as well as the field from tab_objectives that will correspond to TCR_1b.
    - Save the query.
    - Create a form in design view based on this query.
    - Create a combo box bound to TCR_1a.
    - Set the Column Count property of the combo box to 2.
    - Set the Column Widths property of the combo box to 1";0".
    - Set the Row Source property of the combo box to tab_objectives or to a query based on tab_objectives that returns two fields - those corresponding to TCR_1a and TCR_1b.
    - Name the combo box TCR_1a.
    - Place a text box bound to TCR_1b on the form, name it TCR_1b.
    - Create an After Update event procedure:

    Private Sub TCR_1a_AfterUpdate()
    Me.TCR_1b = Me.TCR_1a.Column(1)
    End Sub

  5. #5
    New Lounger
    Join Date
    Nov 2004
    Location
    London, Gtr London, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Query (Access 2003)

    Hans,

    Thanks for the instructions. I've tried to follow them but I'm not sure I've understood them/ how they apply to my form. I have already built the form based on the table Tab_Target_Control_Record. If I delete the fields TCR1_a and TCR1_b and replace them with fields from the query, will the lookup function that currently exists for TCR_1a cease to function? And will the data entered in the form be saved in the table Tab_Target_Control_Record.
    Thanks,
    Katya

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

    Re: Lookup Query (Access 2003)

    If you add controls based on Tab_Target_Control_Record to the form (whether they're directly from the table or from the query based on the table), the values will be stored in that table. The combo box will provide the lookup.

  7. #7
    New Lounger
    Join Date
    Nov 2004
    Location
    London, Gtr London, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Query (Access 2003)

    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
  •