Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Thanked 0 Times in 0 Posts

    Populating field (Access 2000)

    Populating a field in a table depending on another

    In my table customers i want to populate the field segmenid depending on the
    choice in the lookup field Typeid. Both fields, typeid and segmentid are placed in the

    table Customers. The field Typeid is a lookup field, looking in the table Type.
    If i choose Typeid 1,2,3,4, or 5, then i want to populate the field Segmentid as follows:

    segmentid = 1. In all the other cases the filed should be populated as SegmentID = 2
    How can i do it?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Mt Macedon, Victoria, Australia
    Thanked 45 Times in 44 Posts

    Re: Populating field (Access 2000)

    If the segmentID will always take the values you have listed, then you don't need it in the customers table at all.

    Simply create a segmentID field in the Type table, and retrieve the segment from there whenever you want it.

    If however, you really want to store it in customers, create an after update event for the TypeID field on the form you use to enter customers:
    <pre>if ( (me!TypeID = 1) or (me!TypeID = 2) or (me!TypeID = 3) or _
    (me!TypeID = 4) or (me!TypeID = 5)) then
    me![segmentID] = 1
    me![segmentID] = 2
    end if

    Edited by HansV to break long line that caused horizontal scrolling (remember, there is no automatic word wrap between <!t>[pre]<!/t> and <!t>[/pre]<!/t>)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Populating field (Access 2000)

    John has already posted a solution.

    If TypeID is always positive (greater than 0), you can also write the condition in the code he posted as If Me.TypeID < 6 Then.

Posting Permissions

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