Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Populating a form via combo box (2000)

    Hi Guys,
    I'm having this little problem with autopopulating the access form. I have two tables, one
    called tblZoneList and tblServiceZone, I'm trying to have it so that when the data from the combo box is selected (eg "Australia, Sydney" or "USA, Miami" etc etc), it will
    populated the State, Terminal and Zone fields.
    So in a nutshell, the data would be retrieved from tblZoneList and stored to the fields with the exact name in tblServiceZone.
    I've attached the db, its a very simple one, as you can see, I've attempted it but with little success. (See frmServiceZone).
    If someone can help me out on this it would be greatly appreciated.

  2. #2
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating a form via combo box (2000)

    sorry, here is the attachment
    Attached Files Attached Files

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

    Re: Populating a form via combo box (2000)

    This is not the way to do it. You are trying to store duplicate data. Instead, assign a unique ID (for instance an AutoNumber) to each location (record) in tblZoneList, and store this ID in tblServiceZone. You can use a query to look up the other fields.

  4. #4
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating a form via combo box (2000)

    Hi Hans,
    I'm a little bit confused on this. Its the structure of the query that i need help on.

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

    Re: Populating a form via combo box (2000)

    I am confused too. I don't understand the purpose of your tables, nor the relation between them,

  6. #6
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating a form via combo box (2000)

    Oh ok, basically what i want to do is quite simple.
    I have a table for storing transactions (tblServiceZone). A user has to select a location (eg Australia, Sydney) from a combo box, and that would automatically fill in the fields. These fields are Terminal and Zone in tblServiceZone. Such data would be retrieved from tblZoneList, but stored in the fields in tblServiceZone.
    In the Access help, my problem comes under "About AutoLookup queries that enter data automatically",,,i've tried using that method, but i'm having trouble in applying it.

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

    Re: Populating a form via combo box (2000)

    But why do you want to store all those fields in tblServiceZone? Once you know the location, you know the rest. And where does the 'State' field come in?

  8. #8
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating a form via combo box (2000)

    Because tblServiceZone is where the data would be retrieved in future. I have to do an autopopulate, so the user doesnt have to type in all the details. State is an optional field, its hardly used but I have to put it there. (ie state is not different from terminal, zone and Town.

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

    Re: Populating a form via combo box (2000)

    See attached modified database. I removed the Town, Terminal and Zone fields from tblServiceZone, and replaced them with an ID field that is linked to the new ID (AutoNumber) field in tblZoneList. Instead of populating these fields, they are retrieved in the query; the query acts as record source of the form. Here is the SQL for the query:

    SELECT tblServiceZone.TownID, tblServiceZone.ID, tblServiceZone.State, tblZoneList.Town, tblZoneList.Terminal, tblZoneList.Zone, tblServiceZone.Status
    FROM tblZoneList RIGHT JOIN tblServiceZone ON tblZoneList.ID = tblServiceZone.ID;

    As you can see, ID comes from tblServiceZone, but Town, Terminal and Zone come from tblZoneList. The combo box for town now has ID as Control Source, and tblZoneList is its Row Source; the Column Count has been set to 2, and the first column (ID) is hidden by setting its width to 0.
    Attached Files Attached Files

  10. #10
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating a form via combo box (2000)

    Thankyou so much Hans, this is what i was after. Your help once again is greatly greatly appreciated.

Posting Permissions

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