Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    39

    Question Unanswered: Drop Down Menu Selections not Saving

    Hey,

    I have a form with a bunch of textboxes and 2 drop down menus. All the entries in the textboxes save correctly in the respective table, however the drop down menu selections doesn't at all.

    It is also important to note that when I make selections on the 2 drop down menus on the form, all of the other records in the form take on those same values. Anyone know why?

    These are the select statements that the 2 drop down menus depend on.

    • SELECT Codes.CID, Codes.User FROM Codes ORDER BY Codes.User;
    • Private Sub UserType_AfterUpdate() ' Update the row source of the Products combo box ' when the user makes a selection in the UserType ' combo box. Me.Products.RowSource = "SELECT Products.PID, Products.Product, Products.CID FROM " & _ "Products WHERE Products.CID = " & _ Me.UserType & _ " ORDER BY Products.Product" Me.Products = Me.Products.ItemData(0) End Sub

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    "the 2 drop down menus on the form, all of the other records in the form take on those same values"

    i guess you mean "combo box" rather than "drop down menu".
    and i guess you mean "2 per record in a continuous form" rather than "2 ... on the form"

    if so: that's just the way unbound combos behave on continuous forms!
    ...one very good reason not to use the visual nightmare that is a continuous form.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jul 2009
    Posts
    39
    Yes, 2 combo boxes, with one being dependent on the other one. So when a selection is made on the first one, the second takes on a value as well.

    But those 2 values are then applied to all 32 records of the form.

    However in the actual table that stores the results of the form, none of the combo box selections even appear in their respective columns.

    So there's no resolution?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes
    don't use bound combo or list boxes in continuous forms
    EITHER
    drop the continuous forms
    OR
    don't use bound controls
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2009
    Posts
    39
    What do you mean by drop the continuous forms? Can you explain that?

    And by bound controls, are you referring to the row source queries that control the combo box?

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hi healdem,

    in my terminology Me.Products.RowSource= makes the combo unbound.
    if it was a bound combo then you wouldn't need to fill the combo explicitly with .RowSource

    hi queenz

    three ways to go from here:

    1. at the risk of your sanity, use the wretched, awful, disgusting, disgraceful <add many additional disparaging adjectives here to suit your taste/vocabulary> lookup "feature" in Access table design. upside is that your continuous form will work exactly as you want it to. downside is that your application/db instantly becomes un-maintainable and you *WILL* need psychiatric help within a few days. i'm not joking! the lookup feature is a real mindf***. if this is a quickie application for great-aunt Edna and you never plan to update or maintain it - check out the lookup feature. if this is in anyway meant to be a serious application - don't.

    2. accept reality
    what you want to do with continuous forms will not work AND keep you happy/sane! if you have somehow become addicted to the visual clutter of continuous forms you can still keep them to show/edit the bound controls, but get rid of the unbound combos embedded in the continuous form.
    ...perhaps make the continuous form a subform and display the combos on the mother form (updating display each time the selected record changes in sub(continuous)form).
    ...perhaps a little button (...which will become many clones of an identical button, one for each record, just like your combos) on the continuous form that pops up a window with the combos for edit (...and has code to display values for the currently selected record and more code to save any edits to the appropriate record).

    3. bite the bullet and go unbound.
    sooner or later Access' bound form approach gets in the way of a relational database. stuff you want to present to the user as a unified whole for editing just can't get done with bound forms when it comes from several tables. the solution is unbound forms: you own the display; you are responsible for the updates. it's more work but unbound will do anything you tell it to.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Jul 2009
    Posts
    39
    Thank you for that clear assessment.

    I'm trying to avoid anything too tedious.

    So you're saying I'd be better off placing the 2 combo boxes on the main form and then have a sub-form for all of the other information (Textboxes)?

  8. #8
    Join Date
    Jul 2009
    Posts
    39
    Thanks a lot, I took the unbound route and it works!

Posting Permissions

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