Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    37

    Question Unanswered: Edit Combo Box Values and Save Changes

    Hi,

    I have a combo box on a form that lists Item Classifications:

    Math
    Science
    Writing

    I want to be able to select, say math, and be able to edit it and then save the changes.

    Here's the row source of the combo box:

    SELECT DISTINCT [tblICL 1].[ICL 1], [tblICL 1].ItemID2 FROM [tblICL 1] WHERE [ICL 1] Is Not Null And [ICL 1]<>"None";

    Here's its control source:

    ItemID2

    Here's its other information:

    Column Count: 1
    Column Widths:
    Bound Column: 2
    List Rows: 8
    Limit to List: Yes

    How can I edit values in this combo box and then save the changes?

    Thank you

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    You will have to unbound the combo box and use code in an afterupdate event to perform the update back to the table. After updating the table, you will have to requery the combo box to reflect the new data.

    You should be able to perform the update using DoCmd.RunSQL or a DAO.Recordset object. The latter doesn't trigger a warning message box.

    Have fun
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  3. #3
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Todd Sutay's solution sounds fine but if you don't want to unbind your combo box there is a programmatic solution, albeit somewhat messy. I should add that if your form (as well as the combo box) is bound to tblICL1 then I would not use my solution since you will possibly experience record updated by another user and other fun messages. You can avoid these messages by issuing a me.undo but of course that simply resets any changes the user may have made elsewhere on the form.

    OK. Choose a way to invoke the edit process. This could be by trapping the double-click event for the combo box itself or by covering the left side of the box with a transparent command button. When the event occurs use an input box to prompt for the edited value, using the current value as the default. If the user changes the value then perform all necessary validation checks before pasting the new value to tblICL1 via an update SQL query. Finally requery the combo box.

    If you want to be really clever then set the combo box to the new edited value as this puts the user back where he/she started but with the change shown.

  4. #4
    Join Date
    May 2004
    Posts
    37

    Cool

    Rod,

    The form is not bound to tblICL1 so I guess your method would work. I don't quite understand what you are saying. Would it be possible if you attached an example of this so I could look over it or explain it a little further?

    Thank you

  5. #5
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    infinitx,

    The best I can do is construct an example. If you send an email to fe_rod@hotmail.com or rod@alrabie.com I will send you an .mdb file as a reply. However I use Access 2002, so if you use an earlier version I'm not sure if you can open it.

    Failing that I can post or attach some event code for you.

    One final question: do you use much VBA code? If so do you use DAO (Data Access Objects) or ADO (Active X Data Objects)? You of course may use neither and communicate with the Jet database through macros, queries, etc.

  6. #6
    Join Date
    May 2004
    Posts
    37
    Rod,

    I sent you an e-mail to your hotmail address. Waiting for a reply!

    Thank you

Posting Permissions

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