Sorting records in a form

[Jeff S via AccessMonster.com]

I have a form that captures a lot of information on actions involving
different products. This information is organized in a datasheet subform
where each line is an action involving one product.
I would like to have a way to capture the same information in a more
organized way. For example have tab control where each tab corresponds to a
different product but each tab has the same subform that captures the same
information.
Another solution would be to hide certain records (certain lines) of the
subform depending on which button I press on the main form.
I think the latter option my be more do-able but I don't know how to
implement it.

Any help?

 

[tina]

you can't "hide" certain records in the subform, but you don't need to. just
filter the subform's RecordSource for the specific product you want at any
given time. you could do that from a combobox control or listbox control on
the main form, by listing the products to choose from and using the
control's AfterUpdate event to filter the subform, as

Me!SubformControlName.Form.Filter = "ProductFieldName = " &
Me!ComboboxName
Me!SubformControlName.Form.FilterOn = True

you have to substitute the correct control and field names, of course.

or you could use a tab control with one tab for each product, though this is
not a dynamic solution because you would have to create a new tab in the
form every time you add a product to your Products table. but it could be
done, though the solutions that come to my mind immediately are rather
clunky.

hth

 

[John Vinson]

I have a form that captures a lot of information on actions involving
different products. This information is organized in a datasheet subform
where each line is an action involving one product.
I would like to have a way to capture the same information in a more
organized way. For example have tab control where each tab corresponds to a
different product but each tab has the same subform that captures the same
information.
Another solution would be to hide certain records (certain lines) of the
subform depending on which button I press on the main form.
I think the latter option my be more do-able but I don't know how to
implement it.

You might want to filter the subform to show only the records for a
selected product at a time. One way to do this would be to have a
control - a combo box or listbox perhaps - on the mainform allowing
the user to select which product they wish to see; set the Subform's
Master Link Field to include this control, and its Child Link Field to
include the ProductID.

This would have the advantage of not requiring any code.

John W. Vinson[MVP]

 

[Jeff S via AccessMonster.com]

Thanks all for the messages. I am not familiar with writing code and that
helps a lot.

Tina, your code works beautifully.

Thanks again

you can't "hide" certain records in the subform, but you don't need to. just
filter the subform's RecordSource for the specific product you want at any
given time. you could do that from a combobox control or listbox control on
the main form, by listing the products to choose from and using the
control's AfterUpdate event to filter the subform, as

Me!SubformControlName.Form.Filter = "ProductFieldName = " &
Me!ComboboxName
Me!SubformControlName.Form.FilterOn = True

you have to substitute the correct control and field names, of course.

or you could use a tab control with one tab for each product, though this is
not a dynamic solution because you would have to create a new tab in the
form every time you add a product to your Products table. but it could be
done, though the solutions that come to my mind immediately are rather
clunky.

hth

I have a form that captures a lot of information on actions involving
different products. This information is organized in a datasheet subform

[quoted text clipped - 9 lines]

Any help?

 

[Jeff S via AccessMonster.com]

Actually....
The code in itself works well but when I go to the next record the database
locks and I have to close access. Any idea why?

Jeff

you can't "hide" certain records in the subform, but you don't need to. just
filter the subform's RecordSource for the specific product you want at any
given time. you could do that from a combobox control or listbox control on
the main form, by listing the products to choose from and using the
control's AfterUpdate event to filter the subform, as

Me!SubformControlName.Form.Filter = "ProductFieldName = " &
Me!ComboboxName
Me!SubformControlName.Form.FilterOn = True

you have to substitute the correct control and field names, of course.

or you could use a tab control with one tab for each product, though this is
not a dynamic solution because you would have to create a new tab in the
form every time you add a product to your Products table. but it could be
done, though the solutions that come to my mind immediately are rather
clunky.

hth

I have a form that captures a lot of information on actions involving
different products. This information is organized in a datasheet subform

[quoted text clipped - 9 lines]

Any help?

 

[Jeff S via AccessMonster.com]

I don't understand how to implement this idea:
I put an unbound combo on the main form that allows to select the product.
Then how do I "set the Subform's Master Link Field to include this control,
and its Child Link Field to include the ProductID". I don't understand this
statement.

I have a form that captures a lot of information on actions involving
different products. This information is organized in a datasheet subform

[quoted text clipped - 7 lines]

I think the latter option my be more do-able but I don't know how to
implement it.

You might want to filter the subform to show only the records for a
selected product at a time. One way to do this would be to have a
control - a combo box or listbox perhaps - on the mainform allowing
the user to select which product they wish to see; set the Subform's
Master Link Field to include this control, and its Child Link Field to
include the ProductID.

This would have the advantage of not requiring any code.

John W. Vinson[MVP]

 

[Jeff S via AccessMonster.com]

I get there error "You can't carry out this action at the present time" when
I put this code in and then I need shut down the whole database because it
locks itself. Help!

you can't "hide" certain records in the subform, but you don't need to. just
filter the subform's RecordSource for the specific product you want at any
given time. you could do that from a combobox control or listbox control on
the main form, by listing the products to choose from and using the
control's AfterUpdate event to filter the subform, as

Me!SubformControlName.Form.Filter = "ProductFieldName = " &
Me!ComboboxName
Me!SubformControlName.Form.FilterOn = True

you have to substitute the correct control and field names, of course.

or you could use a tab control with one tab for each product, though this is
not a dynamic solution because you would have to create a new tab in the
form every time you add a product to your Products table. but it could be
done, though the solutions that come to my mind immediately are rather
clunky.

hth

I have a form that captures a lot of information on actions involving
different products. This information is organized in a datasheet subform

[quoted text clipped - 9 lines]

Any help?

 

[John Vinson]

I don't understand how to implement this idea:
I put an unbound combo on the main form that allows to select the product.
Then how do I "set the Subform's Master Link Field to include this control,
and its Child Link Field to include the ProductID". I don't understand this
statement.

What are the current Master Link Field and Child Link Field
properties? What is the Name property of the combo box? What field
within the subform's Recordsource would this combo box be intended to
filter?

John W. Vinson[MVP]

 

[Jeff S via AccessMonster.com]

Nevermind John. I managed to do some filtering based on my combo.

Thanks for your help