Use Criteria to open a different type of form when item is selecte

[TL Mike DH]

I am stuck on how to set it up to open a different form based on criteria.

Lets say I have:

ProductPK Product ProductLineFK
1 Dell Laser Mouse Input Device
2 Dell Keayboard Input Device
3 Dell 6400 Computer
4 Dell 440 Computer
5 Dell 5400 Printer

In the Product line table it would have an associated form

ProductLinePK ProductLine Form
1 Input Device frmInputDevice
2 Computer frmComputer
3 Printer frmPrinter

I have an index form to search for products when I select the product to
open a form I would like it to open the specific type form so that it shows
the correct type of information associated to that type of product.

 

[Marshall Barton]

I am stuck on how to set it up to open a different form based on criteria.

Lets say I have:

ProductPK Product ProductLineFK
1 Dell Laser Mouse Input Device
2 Dell Keayboard Input Device
3 Dell 6400 Computer
4 Dell 440 Computer
5 Dell 5400 Printer

In the Product line table it would have an associated form

ProductLinePK ProductLine Form
1 Input Device frmInputDevice
2 Computer frmComputer
3 Printer frmPrinter

I have an index form to search for products when I select the product to
open a form I would like it to open the specific type form so that it shows
the correct type of information associated to that type of product.

It's not clear what data is in the products form's record
source ir when/how you want to trigger opening the product
line form. If the Form field in the product line table is
included in the products form's record source (via a join),
then you can use:
DoCmd.OpenForm Me![Form]
I recommend this approach.

If it is not in the record source, then try using:
DoCmd.OpenForm DLookup("Form", "Product Line", _
"ProductLinePK = " & ProductLineFK)

OTOH, if you always want to display the product line form, I
would first recommend usin it as a subform on the products
form. This way you can use the LinkMaster/Child properties
to sync them.

 

[TL Mike DH]

It's not clear what data is in the products form's record
source ir when/how you want to trigger opening the product
line form. If the Form field in the product line table is
included in the products form's record source (via a join),
then you can use:
DoCmd.OpenForm Me![Form]
I recommend this approach.

If it is not in the record source, then try using:
DoCmd.OpenForm DLookup("Form", "Product Line", _
"ProductLinePK = " & ProductLineFK)

OTOH, if you always want to display the product line form, I
would first recommend usin it as a subform on the products
form. This way you can use the LinkMaster/Child properties
to sync them.

The form that I am using is an index/ search type form that I am able to
search for the product and then I dbl click on the product in the list box in
the index to bring up the specific form related to the product line for the
product selected.

The product and Product line has a relationship via the Primary key which is
an autonumber. I put the actual Product line name in the example so it could
be easly related to the product line table.

The current code I have for the dbl click action for the list box on the
index form is:

Dim rs As Object

DoCmd.OpenForm "frmPartNumber"

Set rs = Forms!frmPartNumber.Recordset.Clone
rs.FindFirst "PartNumberPK = " & Me.List30
Forms!frmPartNumber.Bookmark = rs.Bookmark



A sample of the db can be found here;
http://www.access-programmers.co.uk/forums/showthread.php?t=173507

 

[Marshall Barton]

It's not clear what data is in the products form's record
source ir when/how you want to trigger opening the product
line form. If the Form field in the product line table is
included in the products form's record source (via a join),
then you can use:
DoCmd.OpenForm Me![Form]
I recommend this approach.

If it is not in the record source, then try using:
DoCmd.OpenForm DLookup("Form", "Product Line", _
"ProductLinePK = " & ProductLineFK)

OTOH, if you always want to display the product line form, I
would first recommend usin it as a subform on the products
form. This way you can use the LinkMaster/Child properties
to sync them.

The form that I am using is an index/ search type form that I am able to
search for the product and then I dbl click on the product in the list box in
the index to bring up the specific form related to the product line for the
product selected.

The product and Product line has a relationship via the Primary key which is
an autonumber. I put the actual Product line name in the example so it could
be easly related to the product line table.

The current code I have for the dbl click action for the list box on the
index form is:

Dim rs As Object

DoCmd.OpenForm "frmPartNumber"

Set rs = Forms!frmPartNumber.Recordset.Clone
rs.FindFirst "PartNumberPK = " & Me.List30
Forms!frmPartNumber.Bookmark = rs.Bookmark

What is the list box's row source? It should include the
formname field from the product line table. Then your code
could be much the same as I said before. In this case the
form name can be retrieved using the list bo's Column
property (see VBA Help for details).

I hope the field in the product line tale is not really
named Form, that is a commonly used reserved word.