Linking forms

[Charlie]

I have a form with customer details with a subform for contact details, with
a one to many relationship.

I need to a command button which will open an order form and automatically
link the customer and contact form where the command is situated.

The order form currently has a subform which shows the customer and contact
details for existing orders but shows nothing for the new orders.

Thanks in advance

Charlotte

 

[DevlinM]

Not quite sure what your goal is here. If you want to open the customer form
from the order form and have the customer form display the current customer
on the order form you would do this:

DoCmd.Openform FormName, , , WhereCondition
DoCmd.OpenForm "Customers", , , "CustomerID=" & Orders_FK_CustomerID

 

[Charlie]

Hi Devlin,

Not quite. I want to go from my form which contains the data from my
customer and customer contact to opening an order form but have it
automatically link the data so that the new order will have the correct
contact details without having to re-enter them.

Currently the primary keys from the 3 tables are linked with one to many
relationships with the order table holding all 3.

Any advice would be greatly appreciated.

Charlotte

 

[DevlinM]

OK, so you have a customers table/form. Related information is contacts and
orders.

You should have a form of just customers. You may wish to show a subform
with their contact information, or perhaps a seperate dialog to display that
information. From your customers form you would do just like I pointed out
previously, only in reverse.

In your customer's form add this code with your appropriate table.field names

Private Sub CmdSomeButton_Click()

DoCmd.OpenForm "Orders", , , "Orders_FK_CustomerID=" & Me.CustomerID

End Sub

If you need to see contact information from your orders' form, you would
need to do similar to open a contact information dialog, or have a contacts
subform on your orders form relating the Orders_FK_CustomerID as ParentID and
your ContactInfo_FK_CustomerID as your ChildID.

Does this make sense to you?

Here's what your table structure should look like:
tbl_Customers
att_CustID
att_CustName
att_Cust...

tbl_Orders
att_OrderID
att_Order_FK_CustID
att_Order_FK_ProductID
att_Order...

tbl_ContactInfo
att_CIID
att_CI_FK_CustID
att_CIPhone
att_CI_Addr
att_CI...

Your forms should be relatively the same

There are a number of ways to set up your orders form as a data entry form.
I suggest that you take a look at the Northwind sample DB. The easiest way
to do this is to create a main form which will be your dialog. There you
will want to set your recordsource = "Customers." Name this form
"CustomerOrders."

You will then create a subform and set the recordsource = "Orders." Name
this form "Orders." Place the "Orders" subform in the dialog you just
created. Name the subform Sf_Orders. Relate the Parent and Child
information as P: CustID C: Order_FK_CustID.

Now find the On Current Event in the properties dialog. Place an [Event
Procedure]. Open it and write the following code.

Private Sub Form_Current()
Me.Sf_Orders.Requery
End Sub

Now, looking back at your customers form, you will write an open form
command like this:

DoCmd.OpenForm "Orders", , , "CustomerID=" & Me.CustomerID

This will open the CustomerOrders form to the current customer. If your
customer has existing orders, they will appear in the Sf_Orders subform. If
not, you can now enter an order and it will use the current customerID to
relate the order record automatically.

I hope this dosesn't confuse you. Good luck