Linking forms

[Guest]

I have a table called frm_supplies which displays address and contact info
from the table tbl_suppliers. I also have a separate form called
frm_commodity which holds the supplier commodity groups from tbl_commodity,
what I would like is to click a button in the frm_suppliers and for it to
load up the frm_commodity with the suppliers info who was displayed on the
form when I clicked the button to be displayed. I have tried to do this a
number of different ways but no joy as of yet, could anyone point me in the
right direction?

 

[Brian]

I have a table called frm_supplies which displays address and contact info
from the table tbl_suppliers. I also have a separate form called
frm_commodity which holds the supplier commodity groups from tbl_commodity,
what I would like is to click a button in the frm_suppliers and for it to
load up the frm_commodity with the suppliers info who was displayed on the
form when I clicked the button to be displayed. I have tried to do this a
number of different ways but no joy as of yet, could anyone point me in the
right direction?

If I understand you correctly, what you need to do is to use the OpenForm
method with a WhereCondition e.g.

DoCmd.OpenForm "frm_commodity", WhereCondition := "supplier_id=" &
[supplier_id]

This assumes that the foreign key in tbl_commodities that identifies the
supplier is called supplier_id, and that it is a number. If it's text, do
it like this instead:

DoCmd.OpenForm "frm_commodity", WhereCondition := "supplier_id=""" &
[supplier_id] & """"

 

[Guest]

Hi

Something I should probably have mentioned is that the commodities and the
suppliers table are linked through a junction table. I take it there would be
a different bit of code for this?

Thanks

I have a table called frm_supplies which displays address and contact info
from the table tbl_suppliers. I also have a separate form called
frm_commodity which holds the supplier commodity groups from tbl_commodity,
what I would like is to click a button in the frm_suppliers and for it to
load up the frm_commodity with the suppliers info who was displayed on the
form when I clicked the button to be displayed. I have tried to do this a
number of different ways but no joy as of yet, could anyone point me in the
right direction?

If I understand you correctly, what you need to do is to use the OpenForm
method with a WhereCondition e.g.

DoCmd.OpenForm "frm_commodity", WhereCondition := "supplier_id=" &
[supplier_id]

This assumes that the foreign key in tbl_commodities that identifies the
supplier is called supplier_id, and that it is a number. If it's text, do
it like this instead:

DoCmd.OpenForm "frm_commodity", WhereCondition := "supplier_id=""" &
[supplier_id] & """"

 

[Brian]

Hi

Something I should probably have mentioned is that the commodities and the
suppliers table are linked through a junction table. I take it there would be
a different bit of code for this?

Thanks

Yes, that makes a difference! Your WhereCondition gets more complicated,
something like this:

WhereCondition := "commodity_id IN (SELECT commodity_id FROM
suppliers_commodities WHERE supplier_id = " & [supplier_id] & ")"