passing parameter in Form

[Beatrice]

Hi All,

I'm changing my DB view, in order to facilitate the data access.

My problem is the following:
I have different page that allow me to view different data, organized in different way, concerning different products

In the form A I want to click on "Product X Button"
that open the Form B in which I choose the kind of data I need (maybe product info, product volume and so on) so clicking on right button the relative form C (or D or E, depends on button ) opens with data concerning the product X ( chosen in Form A).

How can I open the the Form C (D, E,...) concerning ProductX, without creating dedicated form for each product??

Thanks in advance,
Bea

[Stewart Ross]

Hi Bea. When you want to view a specific record in a form you open the form using a Where condition as a filter to restrict the form to one matching record.

When the command button you mention is clicked you would have some selection code in your on click event that chooses the right form name, then open the form using a filter like the skeleton below, substituting for the names of controls etc you really use:

DoCmd.OpenForm FormName, acNormal,, "[ProductCodeField] = '" & Me.ComboOrListboxForSelection & "'"

I have assumed that the product code would be the matching field for filtering, and that it is a string. If it is a number you would not need the single quotes on either side of the combo reference.

-Stewart

[Beatrice]

Hi Stewart,

the problem is that in FOrm B I have only btton opening form, not related to Query, so I cannot use "Me.ComboOrListboxForSelection " command.

My idea was, but I don't know if it is possible, to pass the parameter in VBA:

Private Sub Command1_Click(Parameter|Filter)

Do you think is it possible???

Thanks,
Bea

Hi Bea. When you want to view a specific record in a form you open the form using a Where condition as a filter to restrict the form to one matching record.

When the command button you mention is clicked you would have some selection code in your on click event that chooses the right form name, then open the form using a filter like the skeleton below, substituting for the names of controls etc you really use:

DoCmd.OpenForm FormName, acNormal,, "[ProductCodeField] = '" & Me.ComboOrListboxForSelection & "'"

I have assumed that the product code would be the matching field for filtering, and that it is a string. If it is a number you would not need the single quotes on either side of the combo reference.

-Stewart

[Stewart Ross]

Hi Bea. It would be easier to pass the value as an argument to the form using the OpenArgs property of the form. You can pass the form a string value in the DoCmd.OpenForm sequence, and retrieve it within the form itself (using the On Form Load event to do so).

You can also refer directly to a control value in another form. If Form A (say) is still open you can always refer to a combo on it from another form, using the full control reference. There are several alternative ways to do so, all of which allow you to access values in another form. The examples below would set a local variable in your VBA code to the value of such a control:

SomeLocalVariable = forms!formname!controlname
SomeLocalVariable = forms("formname").controls("controlname")
SomeLocalVariable = forms("formname")!controlname

Whilst I offer these as ways forward you may wish to try, I can't help but feel that the multi-form approach you are trying is not quite right if you are having to bend things this way. You could use a different approach by adding a Tab control in your master form, which provides a set of tabbed pages that would allow you to show a product view filtered as necessary all from within the one main form.

I attach an example screenshot showing a part of one of my own tabbed pagesets.

-Stewart

[Beatrice]

Hi Stewart,

the big problem on this DB it due that it's the first!and I've learnt Access (And VBA) working on it.... so more advanced Form/Query must cohabit with basic ones (that are the first I created... and on which the DB is based).

I've never tried the Tab option... I'll study it and introduce it in my DB.

I'll keep you informed about my improvements! :-)

Bea