Displaying a group of related records in a bound form

[dhstein]

This is a little complicated. I'll try to explain as best as I can - and
what I'm looking for are just some suggestions as to how I might do this -
rather than any specific code - although if it exists - I'd be happy to see
it.

The situation is we want to allow the user to adjust inventory quantities
for products. But a product is actually a family of products - so that if
he's going to adjust widgets - we want to display all the widgets on the form.
Example:

Widget - Large Size 10
Widget - Medium Size 15
Widget - Blue and Yellow 3

Each of these rows is a record in a table - and they all share the product
code of "widget"
But there might be as few as 2 items or as many as 20

I think I want a form bound to the table but I'm not sure how to display all
the records that are "widgets". I can display one record in the form, or I
can display the entire table, but I'm not sure how to get just these items.
Also since the number varies this is another complexity. I looked at binding
the fields to a query but I'm not sure if that is the path I need. Again,
I'm just looking for an approach that I might try to work on. Thanks for any
help with this.

 

[Jack Cannon]

The best way to accomplish this is to incorporate a ProductGroupID. Then it
is a simple matter of filtering on that identifier. Other methods such as
filtering on the first few characters of the name ("Widg*") are error prone
and do not allow for any variation in the name. If you do not already have a
ProductGroupID I would go ahead and implement one. It is worth the extra
effort and will save significant problems later.

Jack Cannon

 

[dhstein]

Jack,

I have "product group ID" in the sense that all related records share a
product code of "widget" (actually a number like 1234) . The problem for me
is displaying all of these related items on the form.

 

[Jack Cannon]

This should just be a simple matter of setting the filter to the
ProductGroupID.
If that is not working then you need to post information of how the form is
designed. Is it a single form displaying continuous records or is it form
that incorporates a subform?

Jack Cannon

 

[dhstein]

Jack,

That is my question -- How to design this form ? I know this is a
complicated question - that's why I tried to explain carefully in my post
what I'm trying to do. I guess I wasn't clear enough. Thanks.

 

[Jack Cannon]

It is really no different from designing any other form.
Set the Default View to "Continuous Forms" and Cycle "All Records"

Of course you must have some means of selecting the proper ProductGroupID
then;
The OpenForm command would look something like:
DoCmd.OpenForm "frmProductGroup", acNormal, , "[ProductGroupID]=" &
Me.ProductGroupID, acFormPropertySettings

Jack Cannon

 

[dhstein]

Jack,

It may be that what you're saying is exactly what I need to do and I'm
not getting it. Or it may be that I'm trying to do something different. I
don't want to show all products in a continuous form. I want a form that
shows the products in a product group. There will be a text box on the form
and when the user enters a product number in the text box he will get the
family of products that are in that product group. It may be 2 products or
it may be 20 products. But they will all show on the one screen. He can
change quantities for any or all of those products and then enter a different
value in the text box which will then show that group of products on one
page. This may be possible or it may not be possible and maybe what you're
saying is the way to do it. Sorry - I'm a little new to Access and trying to
figure this one out.

It is really no different from designing any other form.
Set the Default View to "Continuous Forms" and Cycle "All Records"

Of course you must have some means of selecting the proper ProductGroupID
then;
The OpenForm command would look something like:
DoCmd.OpenForm "frmProductGroup", acNormal, , "[ProductGroupID]=" &
Me.ProductGroupID, acFormPropertySettings

Jack Cannon

Jack,

That is my question -- How to design this form ? I know this is a
complicated question - that's why I tried to explain carefully in my post
what I'm trying to do. I guess I wasn't clear enough. Thanks.

 

[Jack Cannon]

What I had assumed was that a product group would be selected on form1 and
then that ProductGroupID would be used to open form2.

What I now understand is that you want to use only one form. On that form
you would manually enter a ProductNumber and the form would then display the
ProductGroup associated with that ProductNumber.

You can certainly do that. Set the form to "Continuous Forms" and Cycle
"All Records". It will open and display all records. Put a text box in the
form header. On the AfterUpdate event of the text box, use the ProductNumber
to lookup the ProductGroupID associated with the ProductNumber. Something
like:

lngProductGroupID = Dlookup("[ProductGroupID]", "tblProducts",
"[ProductNumber]=" & Clng(Me.MyTextBox.Value)

Once you have the ProductGroupID use it to apply a filter to the form.
Something like:

Me.Filter = "[ProductGroupID]=" & lngProductGroupID
Me.FilterOn = True
Me.Requery

If you do not want any records to appear when the form opens initially then
on the Form_Open execute:

Me.Filter = "[ProductGroupID]=0"
Me.FilterOn = True

Jack Cannon

Jack,

It may be that what you're saying is exactly what I need to do and I'm
not getting it. Or it may be that I'm trying to do something different. I
don't want to show all products in a continuous form. I want a form that
shows the products in a product group. There will be a text box on the form
and when the user enters a product number in the text box he will get the
family of products that are in that product group. It may be 2 products or
it may be 20 products. But they will all show on the one screen. He can
change quantities for any or all of those products and then enter a different
value in the text box which will then show that group of products on one
page. This may be possible or it may not be possible and maybe what you're
saying is the way to do it. Sorry - I'm a little new to Access and trying to
figure this one out.

It is really no different from designing any other form.
Set the Default View to "Continuous Forms" and Cycle "All Records"

Of course you must have some means of selecting the proper ProductGroupID
then;
The OpenForm command would look something like:
DoCmd.OpenForm "frmProductGroup", acNormal, , "[ProductGroupID]=" &
Me.ProductGroupID, acFormPropertySettings

Jack Cannon

Jack,

That is my question -- How to design this form ? I know this is a
complicated question - that's why I tried to explain carefully in my post
what I'm trying to do. I guess I wasn't clear enough. Thanks.



:

This should just be a simple matter of setting the filter to the
ProductGroupID.
If that is not working then you need to post information of how the form is
designed. Is it a single form displaying continuous records or is it form
that incorporates a subform?

Jack Cannon

 

[dhstein]

Jack,

Sorry for not being clearer at first and thanks for your patience. This
sounds exactly like what I need. I'll give it a shot and let you know how it
goes. Thanks.

David

What I had assumed was that a product group would be selected on form1 and
then that ProductGroupID would be used to open form2.

What I now understand is that you want to use only one form. On that form
you would manually enter a ProductNumber and the form would then display the
ProductGroup associated with that ProductNumber.

You can certainly do that. Set the form to "Continuous Forms" and Cycle
"All Records". It will open and display all records. Put a text box in the
form header. On the AfterUpdate event of the text box, use the ProductNumber
to lookup the ProductGroupID associated with the ProductNumber. Something
like:

lngProductGroupID = Dlookup("[ProductGroupID]", "tblProducts",
"[ProductNumber]=" & Clng(Me.MyTextBox.Value)

Once you have the ProductGroupID use it to apply a filter to the form.
Something like:

Me.Filter = "[ProductGroupID]=" & lngProductGroupID
Me.FilterOn = True
Me.Requery

If you do not want any records to appear when the form opens initially then
on the Form_Open execute:

Me.Filter = "[ProductGroupID]=0"
Me.FilterOn = True

Jack Cannon

Jack,

It may be that what you're saying is exactly what I need to do and I'm
not getting it. Or it may be that I'm trying to do something different. I
don't want to show all products in a continuous form. I want a form that
shows the products in a product group. There will be a text box on the form
and when the user enters a product number in the text box he will get the
family of products that are in that product group. It may be 2 products or
it may be 20 products. But they will all show on the one screen. He can
change quantities for any or all of those products and then enter a different
value in the text box which will then show that group of products on one
page. This may be possible or it may not be possible and maybe what you're
saying is the way to do it. Sorry - I'm a little new to Access and trying to
figure this one out.

It is really no different from designing any other form.
Set the Default View to "Continuous Forms" and Cycle "All Records"

Of course you must have some means of selecting the proper ProductGroupID
then;
The OpenForm command would look something like:
DoCmd.OpenForm "frmProductGroup", acNormal, , "[ProductGroupID]=" &
Me.ProductGroupID, acFormPropertySettings

Jack Cannon


:

Jack,

That is my question -- How to design this form ? I know this is a
complicated question - that's why I tried to explain carefully in my post
what I'm trying to do. I guess I wasn't clear enough. Thanks.



:

This should just be a simple matter of setting the filter to the
ProductGroupID.
If that is not working then you need to post information of how the form is
designed. Is it a single form displaying continuous records or is it form
that incorporates a subform?

Jack Cannon