Results 1 to 6 of 6

Thread: form question

  1. #1
    Join Date
    Aug 2006
    Posts
    2

    Unanswered: form question

    I have a db with 2 tables 1- a parts list of unique items (~100) and
    2- a list of manuals for these parts (~10).

    Each part gets a manual, but approx every 10 parts uses the same
    manual.

    In order to assign a manual to each part, my form was going to have
    2 list boxes - one to choose the single manual, and the other to
    choose the multiple parts to assign the manual to.

    Q1- Any better way to set his up? I'd rather not select each part
    and assign it a manual individually.

    Q2- I've read about the ItemsSelected property and that seems
    to be the direction to go, but my VBA skills blow - but not for
    want of trying.

    Thanks ahead of time for any help.

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    A main form with the manual containing a subform for the parts may be worth trying.

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You can also do it with a combo box and a subform. The combobox would show all of the manuals and the subform would be all the parts. When you add the subform (the Deault View should be Continuos and you should allow the user to Add or Delete data, Allows Additions and Deletions on Data tab).

    Then when you put the subform on the main form make sure the Master Field (for the subform) links to combobox and the child links to the Manual ID.

    I am not sure how familiar you are with subforms, so ask if you have any questions.

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Yep, DCKunkle is right on the money.

    Think of it this way: your data says that you have 1 Manual to Many Parts. So, you setup the forms the same way.

    A) On the parts data entry screen, simply have a combo box that allows you to choose which manual works for it. This is the "individual part record edit" screen.

    B) On a manuals data entry screen, add a subform where the manual key is the Master/Child link. This is the "individual manual record edit" screen with the added bonus of being able to edd/remove all the parts that manual covers in one place.

    In an address book, it would look like this:
    A) Each house, which includes a street name in it's records.
    B) Each street, which has a list of houses on it.

    tc

  5. #5
    Join Date
    Aug 2006
    Posts
    2
    Thanks for posts-

    The manual form with parts subform seemed to be what I want, but
    how would I add the # representing the manual ID key to the ~10
    parts the manual goes to, in 1 step, on this form? Wouldn't this form
    just show me the parts per selected manual that already have the key?

    Which was why I was thinking the multiselect list box for selecting all the parts that get a single manual, press a button to assign, and done.

    I did some digging on the web and found a page on Martin Green's website
    "http://www.fontstuff.com/access/acctut11.htm" that had some code
    I did a little tweaking to get my list boxes to work just like I wanted.
    (I attached an example db)

    But on that page he also states, "I might use a list box in multi select mode to allow a user to make more than one choice for multiple data entry, but I can't think of an example that wouldn't represent poor database design (would the choices all go in the same field... or into different fields... how would it decide?)."

    Any input on that? I don't know. It seems right now like it will save me time -
    which is what I'm going for.

    Thanks again for the advice. Cheers!
    Attached Files Attached Files

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    The manual form with parts subform seemed to be what I want, but
    how would I add the # representing the manual ID key to the ~10
    parts the manual goes to, in 1 step, on this form? Wouldn't this form
    just show me the parts per selected manual that already have the key?
    Yes, you will have to enter the data initially, although even with the list boxes you will have to enter all of the data to start with. Personally, I would go into the raw tables and "seed" the correct data to start with.

    Which was why I was thinking the multiselect list box for selecting all the parts that get a single manual, press a button to assign, and done.
    Ummm, see previous response.

    "I might use a list box in multi select mode to allow a user to make more than one choice for multiple data entry, but I can't think of an example that wouldn't represent poor database design (would the choices all go in the same field... or into different fields... how would it decide?)."
    well, I didn't check the site posted, but to answer your question: YES, putting multiple fields in a single record to handle multiple values of the same data (condensing the One-to-Many relationship to a single record with many fields) is poor database design.

    It seems right now like it will save me time -
    which is what I'm going for.
    ok, add a field to your parts table that is a number to hold the key value of the manual it goes with. Make the field a combo box that selects the manual key and manual name. Run down the table and select the manula for each part. If there's a lot of parts, do the update in Excel and copy it back to Access. Sems like it would be a 10 minute operation.

    By the way, a few minutes saved now during the design phase could end up costing you hours (or days) down the road when you try to make a change to the design.


    For your question, when you setup a Parent/Child using a subform, then any time a record is added to the subform, the linked child key field inherits the correct key value from the parent key field. So, as you add items to the sub form, they get the correct key linking them to the record on the main form.

    To use the multi-select list box, each item in the list box has a "Selected" property, and there's a collection of selected items called "ItemsSelected" (gotta love VBA for it's generally obvious naming conventions). You can get heads up on using those in the help files.

    Using a list box for this where you show every part in the list box and allow the user to select each part the current manual covers would be a bit heavy on the code, as you would have to cycle through all the records each time a manual is selected (probably using the On Current Event) to determine which ones to select, and then would have to manage updating the tables as the user selects and unselects individual items. The best way to approach that would be to use recordsets - something you will defiinetly want to learn, but is a bit heavy for a beginner.

    Now, if you want to use a list box but have much simpler code, create a query that adds a field that displays text to matching records ("X", "*", "COVERED" or something else obvious). Then, your list box simply has a query rowsource and the query shows the text field for each item included. You would need to dynamically update the rowsource statement or use a public function as a parameter in the query so that the query knows which manual you are showing. You would still need to handle selecting and unselecting individual items as they are clicked, however, which is a bit chunky on the code.

    The Parent/Child form was invented for exactly this situation and Access handles all of the data management for you without any code, so after you have "bit the bullet" and made the initial updates to the data tables, I recommend using the sub form.

    good luck,
    tc

Posting Permissions

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