Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264

    Unanswered: Grouping on forms

    Is there a way of creating a form that will group similar to a report would?

    I have two tables, parts & features. There are 0 to 10 features per part.

    I want to display a list of several parts at one time with a list of available features (if any) without duplicating the part information for every line.

    Something like this:

    ABC 123 firstPart
    firstPartFeature
    another feature
    CDC 245 secondPart
    Feature for secondPart
    What I end up with now is more like:
    ABC 123 firstPart firstPartFeature
    ABC 123 firstPart another feature
    CDC 245 secondPart Feature for secondPart

    and it is very confusing to view or edit the information with the duplication

    The important part is I do not want to view one part at a time, I need a view of all the parts with their associated features in order to compare one part with another.

    The query I was using is:
    SELECT part.supplier, part.partNum, part.partDesc, feature.featureDesc
    FROM part LEFT JOIN feature ON part.partIndex = feature.partIndex

    Any ideas?

    Steve

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Is this view only? Or do you want to let the user update something?

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Steve,

    You can use a list box for that. Control the entries into the list box with VBA, and when your user clicks an entry you can run an AfterUpdate event in VBA.

    Hope this helps,

    Sam

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    MSAccess Forms do not allow you to have a continuous form within another continuous form like you can do in the Reports so a good workaround is what Sam Landy suggested.
    Last edited by pkstormy; 07-26-07 at 21:05.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Steve,
    After reading what Paul Kohn had to say, I just wanted to add that if it is for display only, it can be done. If it is for update, it can still be done, but it will be harder to do. So, you asked the question, and if you can answer the questions and statements already posted, then help will be very close behind.

  6. #6
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    After update is like after I click some value on a list, something changes. So I could click an R in an address book and it jumps to the R's.
    Ryan
    My Blog

  7. #7
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Yes.

    I am still wondering what the answers are the the questions. Or is this all the help you need?

  8. #8
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Sorry for my slow response, I had an unexpected meeting Thursday afternoon and then I was out of town Friday.

    I would like the form to be updatable but only the part description. The features are static.

    Ideally I would like to be able to have a couple command buttons to perform specific actions on that record (ex: append the selected feature to the current part description seperated with a dash and then delete the feature)

    ---

    I did not understand the response of using a list box. The purpose of this form is to allow the users to edit and update the part descriptions using the information that is contained in the features and make all the part descriptions uniform among similar parts.

    I have a form that they use now where they can get to one part number at a time, what they want is the ability to view all the related parts at one time.

    Thanks for all of the great responses.

    Steve

  9. #9
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Steve, based on what you have requested, and the answers you have just provided, I would suggest creating a work table (non linked) that can hold what you want to display to your users. Put the data fields you want into this table, and include an autonumber field, so that as you add records to this table in the order you want them, the order can be maintained by this autonumber field. After adding the record you want to display, then run an update procedure, which would have to be a read and update loop to remove the redundant data from records in the list. Then, when a user clicks on any of these records, you can present them with a popup window that allows them to edit the real record from the real table.

  10. #10
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Part of the original problem was that Steve wanted to limit the display info, not every bit of data on every line. This is impossible to do when displaying a table. Hence the idea of a list box.

    Using a loop in VBA, you can "see" which 'master' data has already been displayed, so it can be concealed in future lines, and replaced by an indentation of so many blank spaces to indicate the redundance, without displaying it. VBA can assign the actual value to the listbox (Me.MyListBox.Value = " " & Some Data) to display the way Steve wants it. A VBA AfterUpdate event is needed, so when the user clicks on a line in the list box - which corresponds to a record in the table - VBA can provide the update utility, and make the change, instead of having the user manipulate the data himself. Also, that way, Steve can provide code for testing the data prior to actually doing the update.

    It's always a good idea to insulate table data from users trying to update same, and to allow VBA to do some work also.

    Sam

  11. #11
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Sam,
    Your solution and mine are virtually the same, except I'm suggesting using a "work" table, one created for this purpose and this purpose only. A form would still be needed, but it could be a datasheet form which, in my humble opinion, is easier for both the users and the developer to deal with than a list box. Rather than adding spaces where the duplicate data would be, just clear out the data in the work table. Here is an example of one way I have done this in a time keeping application. In this case I did use blanks in front of the sub-category title to move the sub-category to the right a little bit. These were not separate fields, but it was still not the way the actual data is stored in the tables. Just manipulated here for the display of the data.

  12. #12
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Sam & GolferGuy

    Thank you both for the responses, I really appreciate the input. Unfortunately, I have not figured out how either of these solutions would work. How does having a list box or a work table allow me to create dynamic forms?

    It sounds like I need to know all of the data to be displayed before I can build the form.

    It did not seem like an unusual requirement to me. I have many situations where I want to display a one to many view of the data with more than one record displaying at a time

    Can you provide a quick example on how to use your idea?

    Thanks!

    Steve

  13. #13
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Lets say you have 3 orders and several items per order, and you want to list all of these on the form at one time. The fields for the order to be displayed are Customer, OrderNumber, and the fields to be displayed for the items in the order are ItemNumber, ItemDescription, and Quantity. Therefore, your worktable, and your form, will need these 5 fields plus the AutoNumber field I talked about before to keep the records in the correct sequence. As a separate possibility would be to have the OrderNumber and ItemNumber fields duplicated (for sorting purposes rather than the autonumber). These fields could be called OrderNumberSort and ItemNumberSort (or whatever you would want). Once these records are loaded into the work table, you could then read through the records in the correct display order and blank out OrderNumber and Customer until there is a change from the previous record. On the form, when an ItemNumber record is clicked, you can pop up a form that has that complete item number record available for editing. Or if you click on a record that has the OrderNumber and Customer fields filled in, you could open the Order header record for editing.

  14. #14
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Vic - and Steve, of course - the reasons I'm going list box vs. a table in datasheet view are:

    (1) As Paul Kohn stated back in post #4, a continuous form display in a form is disallowed (I myself did not know that, BTW).

    (2) Perhaps more to my point, though, is if you do use a table, then you have to tell the user which field to click in, as you don't want to have to program an AfterUpdate event on each field you display. In a listbox, there is only one place to click. Also, if there is duplicate detail data for different master data, how are you going to know which record in the 'real' table is the correct one?

    For example:

    Code:
    Screw, flat-head, #6-32
              #8-32
    .
    .
    .
    
    Screw, round-head, #6-32
              #8-32
    I challenge you to find the correct "#8-32" using a work table and a search. Of course, you could use a primary key to keep track of your data. But why bother? If you use the list box, each line is tied directly to the table, only the display is modified, and pulls the correct record.

    Sam

  15. #15
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Paul Kohn stated correctly that a continous record form is not allowed inside another continous record form. That is not what I was suggesting. I'm suggesting just one form, or a single form with a datasheet view subform. Within the datasheet form (or subform) the table will be the work table. The records in the table will have any and all necessary data to "find" the correct record in the "real" table. That is, all key field data will be in each record in the work table. This is the same type of thing that would be necessary within the List Box too. That is some fields that will not display, but can be used to retrieve the real record. As for writting an AfterUpdate, or OnClick event for each field, I would never do that. But I would write one function that I could then use in each field's AfterUpdate or OnClick event property. All one need do is select all the fields on the form, open the property window, Event tab, then put the name of the function into the AfterUpdate or OnClick line like this: =MyOnClickEventFunction()
    Then, when any of those fields are clicked, the "MyOnclickEvenFunction()" is executed.

    I'm really not trying to stress that the work table and datasheet form is the best solution. I'm just trying to show, at least for me, it is no harder than the List Box, and that I personally prefer to deal with records in a datasheet view rather than a list box.

    One advantage to the datasheet view is that for the record that has the "header" information in it, the user can click on any of the header fields that have data in them, and the OnClick event can be tailored to open the Header table record rather than the Detail record if the detail data field(s) are clicked.

Posting Permissions

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