Group Datasheet Records

[Mr.LuckyMe]

I am using Access 2003 and I have created a datasheet subform where I
included fields like product, quantity and comments. What I want to do is to
be able to include some kind of grouping depending on the user input. e.g.
the user can input Group A as a header in my datasheet form then populate the
fields according to the information he has, when he finishes he could be able
to input Group B, etc. Something similar to what you can do in Excel with
grouping

Product Quantity Comments
****Group A*****
Product 1 12 Comments 1
Product 2 13 Comments 2
Product 3 14 Comments 3
****Group B*****
Product 4 15 Comments 4
Product 5 16 Comments 5
Product 6 17 Comments 6
****Group C*****
Product 7 18 Comments 7
Product 8 19 Comments 8
Product 9 20 Comments 9

Any ideas.

Thanks

 

[Arvin Meyer [MVP]]

You must use either a report, or Excel to display your data like that. With
a datasheet, (or any form) you are limited to:

Group A Product 1 12 Comments 1
Group A Product 2 13 Comments 2
Group A Product 3 14 Comments 3
Group B Product 4 15 Comments 4

etc.

 

[Mr.LuckyMe]

Thank you for your answer, if I understood correctly, there is no way the
user can input just ONCE the group in a datasheet. Am I right? What
suggestions do you have?

Thanks

 

[John W. Vinson]

I am using Access 2003 and I have created a datasheet subform where I
included fields like product, quantity and comments. What I want to do is to
be able to include some kind of grouping depending on the user input. e.g.
the user can input Group A as a header in my datasheet form then populate the
fields according to the information he has, when he finishes he could be able
to input Group B, etc. Something similar to what you can do in Excel with
grouping

Product Quantity Comments
****Group A*****
Product 1 12 Comments 1
Product 2 13 Comments 2
Product 3 14 Comments 3
****Group B*****
Product 4 15 Comments 4
Product 5 16 Comments 5
Product 6 17 Comments 6
****Group C*****
Product 7 18 Comments 7
Product 8 19 Comments 8
Product 9 20 Comments 9

If you have a one to many relationship (each group can have many products,
each product belongs to only one group) then you should consider two tables,
related one to many, with a form for the group and a subform for the product.
Even that won't give you this exact kind of interlineated header though.

An alternative would give you

Group Product Quantity Comments
A Product1 12 Comments
A Product2 12 Comments
A Product3 12 Comments
B Product4 12 Comments
B Product5 12 Comments
B Product6 12 Comments

Data entry can be eased by putting a line of code in the Group textbox's
AfterUpdate event;

Private Sub txtGroup_AfterUpdate()
Me!txtGroup.DefaultValue = Me!txtGroup
End Sub

This will make the group "sticky" - each new line will inherit the most recent
entry's value of the group; it can be overtyped and the new value will become
the new default.

 

[Mr.LuckyMe]

Thank you for your comments, but unfortunately the relationship is not one to
many, as one group can have many products and one product can be in many
groups.

Thanks

 

[John W. Vinson]

Thank you for your comments, but unfortunately the relationship is not one to
many, as one group can have many products and one product can be in many
groups.

Well, a many to many relationship is possible too: you need a Products table,
a Groups table, and a ProductGroup table. The same principle would apply.

 

[Mr.LuckyMe]

Thanks for your time and your answer.

Well, a many to many relationship is possible too: you need a Products table,
a Groups table, and a ProductGroup table. The same principle would apply.