Updating New Record form

[Guest]

Hi,

I have two tables set up: tblDeals and tblEquip, they have one field in
common called: DealCode.

I have a query qryEquip that links the two tables based on the DealCode
field and adds other tblDeals fields like Customer, Description, etc, to help
identify the deal but only the DealCode is stored in the Equip table.

Form frmEquip is based on query qryEquip and it is working fine.

Here’s what I’m trying to do: When I add a new Equip record I want to be
able to look into the tblDeals for the right deal and then pass the DealCode
to the frmEquip, then have the frmEquip pull the rest of the tblDeal info
from the qryEquip and populate the form so I can continue adding the rest of
the data (knowing that I have the right deal).

I’m familiar with using an unbound form with a combo box to capture the
DealCode and passing it to a form based on the tblDeals, but doing it on a
query that has two tables and for a new record is proving more complicated
than I thought and I haven’t been able to make it work. I may be making it
too complicated.

I will appreciate ANY suggestions that you can offer as I’m stuck. Thank you!

Trauton

 

[Carl Rapson]

Hi,

I have two tables set up: tblDeals and tblEquip, they have one field in
common called: DealCode.

I have a query qryEquip that links the two tables based on the DealCode
field and adds other tblDeals fields like Customer, Description, etc, to
help
identify the deal but only the DealCode is stored in the Equip table.

Form frmEquip is based on query qryEquip and it is working fine.

Here's what I'm trying to do: When I add a new Equip record I want to be
able to look into the tblDeals for the right deal and then pass the
DealCode
to the frmEquip, then have the frmEquip pull the rest of the tblDeal info
from the qryEquip and populate the form so I can continue adding the rest
of
the data (knowing that I have the right deal).

I'm familiar with using an unbound form with a combo box to capture the
DealCode and passing it to a form based on the tblDeals, but doing it on a
query that has two tables and for a new record is proving more complicated
than I thought and I haven't been able to make it work. I may be making
it
too complicated.

I will appreciate ANY suggestions that you can offer as I'm stuck. Thank
you!

Trauton

Unless other information (besides the DealCode) from tblDeals is being
stored in tblEquip (which shouldn't be the case because it violates
normalization rules), you don't need for your query to pull fields from
tblDeals. All you need to store in tblEquip is the DealCode, since you can
pull related information from tblDeals at any time with that value. You can
add unbound fields to your form and populate them from tblDeals in the
AfterUpdate event of the combo box that you use to select the DealCode.

Carl Rapson

 

[Guest]

Hi Carl,

Thank you for your reply. I will follow your suggestion. Yes, the only
field from the tblDeals that I am storing in the tblEquip is the DealCode.

I appreciate your help,

Trauton