Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update field from Combo or List box (Access 2000)

    Hello:

    My question concerns a database among, which are these tables and fields:

    [Names].[ID]; (etc)
    [Attend].[ID];[Attend].[EventID];[Attend].[Event]
    [Events].[EventID];[Events].[Event];[Events].[Date]

    Primary keys are:
    [Names].[ID]
    [Attend].[ID]
    [Events].[EventID]

    The [Events].[Event] is updated periodically as events are added. This is accomplished from a small form. The idea is to use [Events] as a lookup table to supply the [Events].[Event] to the [Attend] table.

    When a [Names].[ID] attends an event, the [Attend].[Event] field should be updated to reflect this attendance.

    So, here

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

    Re: Update field from Combo or List box (Access 2000)

    I don't quite understand the structure you describe.
    <UL><LI>I assume that the Attend and Events tables are related through the EventID field. If that is correct, I don't understand why you need and Event field in the Attend table - it seems to be duplicate information, since you can look it up in the Events table.
    <LI>How are the Names and Attend table related? From the field names, the ID field seems to be a candidate, but that is impossible, since you write that ID is the primary key in both tables.[/list]Please explain. Thank you.

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update field from Combo or List box (Access 20

    Doug,

    I have built dynamic lists for selection within a form using either listboxes or combo-boxes. The choice, in my opinion, comes down to how big the list may become. For reasonable sized lists, the combo-box is a quick and easy solution. If the list can grow to dozens, or hundreds of entries, I tend to favor a listbox.

    Normally, in either case, I begin by letting the forms control wizard build the "prototype" properties, usually referring to a predefined query that provides the necessary display fields. Then I can tweak the control properties manually to get what I really want, in terms of which columns are 0 width, and desired widths of the remaining columns, as well as which column is bound. In some cases of fairly short lists in a combobox, I'll even dynamically build the display list (Row Source Type=Value List) and assign it to the RowSource property.

    The big difference, of course, is how you handle the "click" event on the listbox or combobox. If you're not familiar with that process, I'd suggest Woody's Access 2000 book <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23> .

    Hope that helps.
    -- Jim

  4. #4
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update field from Combo or List box (Access 2000)

    Hans:
    OK, I can look up the name of the event from the table Events using EventID. I just thought it would be easier to pull the event name from the Attend table in my reports.

    The Primary Key in table Names is ID. In table Attend it's now EventID. What should the key be in table Events if any?

    Thanks,
    Doug

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

    Re: Update field from Combo or List box (Access 2000)

    ID as Primary Key in Names and EventID as Primary Key in Events is fine. I'm not sure about Attend, since you haven't answered my question about the relationship between Names and Attend. My guess would be that there is a many-to-many relationship between names and events: several persons can attend one event, and one person can attend several events. If that is correct, the Attend table would function as intermediate table to implement this many-to-many relationship. Attend would be linked to Names by ID and to Events by EventID, and the combination of these fields would be the Primary Key in Events: there can be several records with the same ID (but with different EventID's), and also several records with the same EventID (but different ID's.) Assuming that ID and EventID are AutoNumber fields in Names and Events, respectively, they should not be AutoNumber fields in Attend, but Number (Long Integer) fields. See the attached screenshot of the relationships. Does this describe your situation correctly? If not, please provide details.
    Attached Images Attached Images
    • File Type: png x.png (3.2 KB, 0 views)

  6. #6
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update field from Combo or List box (Access 2000)

    Hans:
    The diagram you have above is the way I want my relationships to appear. In my Relationships window I can't find the way to make the arrows show one-to-many like yours. For example, my diagram shows an arrow from the Attend.EventID pointing toward the Events.EventID. It doesn't show the "many" symbol.
    From the relationships window the relationships are just a straight link. How do I make the link one-to-many in the right direction?
    Thanks,
    Doug

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

    Re: Update field from Combo or List box (Access 2000)

    Doug,

    You can edit the properties of a relationship by double clicking the line between the tables in the Relationships window. The Edit Relationships dialog contains 3 check boxes:
    - Enforce relational integrity
    - Cascade update fields
    - Cascade delete records
    It is always a good idea to tick the first two; this ensures that you won't be able to create/edit records that violate the relationship. In your situation, I also recommend ticking the third one; if you delete a person or event, all associated attendance records should be deleted too. Do this for both relationships.

  8. #8
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update field from Combo or List box (Access 2000)

    Hans:
    Thanks, that works fine. Now, the 1-to-many symbols show in my relationships window as in your diagram.

    Now to the real meat of my question, how do I get the Event I highlight in the combo box to update the Event field in the Attend table? This combo box is on a form built from the Names table.

    Thank you,
    Doug

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

    Re: Update field from Combo or List box (Access 2000)

    Doug,

    Since one person can attend several events, you need a subform for the events associated with one person. The main form is bound to the Names table, the subform to the Attend table. On the subform, the user can select an event from a combo box whose Row Source is the Events table.

    I have attached a rough example; I didn't pay attention to naming conventions etc.; it is just meant to show how you could set up the form and subform.
    Attached Files Attached Files

  10. #10
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update field from Combo or List box (Access 2000)

    Thank you, Hans.

    What if there will be dozens of events?

    Doug

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

    Re: Update field from Combo or List box (Access 2000)

    What do you mean? As I wrote, my demo is just a rough version. You can increase the height of the subform on the main form, etc.

  12. #12
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update field from Combo or List box (Access 2000)

    Hans:
    So you don't think it will be cumbersome to show a few dozen events in the combo box's drop down box?

    Also, I copied your subAttend form to my Names form and got it working with my Events table. I see you've made two primary keys in the Attend table. Why?

    Thank you,
    Doug

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

    Re: Update field from Combo or List box (Access 2000)

    It might be a good idea to make the Row Source of the combo box a query that sorts the events by description or by date, whichever you prefer. If the number of events becomes very large (many hundreds or thousands), you should set up categories, and use two combo boxes: the first to select a category, the second to select an event within the category.

    The Attend table has only one primary key (a table cannot have two primary keys), but it is a composite primary key consisting of two fields. See <post#=346194>post 346194</post#> higher up in this thread for an explanation.

  14. #14
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update field from Combo or List box (Access 20

    Thank you, Jim. My Events table will have 20-24 events in it, so I decided to use a combo box that Hans helped me with. I think I will base the box on a query as you and Hans have suggested. Right now, I'm just happy the thing works!

    Doug

  15. #15
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update field from Combo or List box (Access 2000)

    Hans:
    Thanks for working through this with me. Your help has resulted in a great little combo-box that works just like the users will want it. I added the query as the rowsource in order to sort by date as you and Jim suggested. It works fine, too.
    Thanks,
    Doug

Page 1 of 2 12 LastLast

Posting Permissions

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