Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Combo Box Update (2003 SR2)

    I have just appended a new set of records to tblContacts. One of the fields in frmContacts is a ComboBox whose Row Source Type is Value List. All of the new records have the same choice of text. How do I do a mass update?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Combo Box Update (2003 SR2)

    Use an update query.

    UPDATE tblContacts SET controlname = "whatever"

    Where "whatever" is the text you want to set into controlname for every record in tblContacts.

    Is this what you meant?

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Combo Box Update (2003 SR2)

    I think you need to explain more clearly what you want.

    If you want a combo box to have values that depend on the content of a table, don't use a value list. Instead use a table/orquery to connect the combo box to the relevant table.

    From what you have said the combo is on a form. You only have one form so you only need to change it in one place anyway.
    Regards
    John



  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Combo Box Update (2003 SR2)

    I'll try that and report back in the AM.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Combo Box Update (2003 SR2)

    Let me clarify. Form frmContacts is based on table tblContacts (a membership list). The form has has a control called Event which is a combo box with only five choices, hence the value list. It is NOT dependent on another table.The form has been working fine, with the user choosing the Event when entering a new record, usually one or two at a time. We just had a big event, with 35 new records entered in an Excel sheet. That sheet was imported into an Access table, tblNewnames, with matching fields to tblContacts. I used an Update query to populate the Event field (which was blank) in tblNewnames with the value "Cstone". This is one of the values in the combo box on frmContacts. Table tblNewnames was updated correctly. However, when I used an Append query to append tblNewnames to tblContacts the Event field for these 35 appended records was blank. (Why?) So now I want to use an Update query to put "Cstone" in the Event field. I will try Pat's solution (see previous post) and report back.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Combo Box Update (2003 SR2)

    As I read it now, your question has actually got nothing to do with the combo box at all.

    If you append new records to a table, fields will remain empty unless you explicitly put data into them, or they have a default value.

    If the event field does not have a default value, new records will not have a value for Event unless you explicitly put it there in the Append query. It sounds like something was wrong with the append query - you wanted it to put a value in Event but it did not for some reason.

    So to now put the value in, yes use an update query. You can design it in the query grid, but make sure you only update the rows where event is Null. (otherwise it will change all records, not just the new ones.)
    Regards
    John



  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Combo Box Update (2003 SR2)

    Yep, you are correct. I screwed up the original append query by leaving the Event field out of the query grid! Fixed that and all is well. Thanks for your help.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Combo Box Update (2003 SR2)

    Thanks, Pat. That did it.
    Howard

Posting Permissions

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