Pass parameters

[BFlat]

I am new to acces and and running into a problem.

I have the following query on a form:

SELECT qry_Order.MRN, qry_Order.LastName, qry_Order.FirstName, [LastName] & " , " & [FirstName] AS Name, qry_Order.Memo, qry_Order.OrderID, qry_Order.From, qry_Order.To, qry_Order.Date, qry_Order.Payment, tbl_Shipment.ShipmentID, tbl_Shipment.FacilityDecision, tbl_Shipment.FacilityReason, tbl_Shipment.CustomerDecision, tbl_Shipment.CustomerReason, tbl_Shipment.Notes, tbl_Shipment.ShipDate, tbl_Shipment.ReceiptDate, tbl_Shipment.LastIP, tbl_Shipment.Item, tbl_Shipment.ReleasedTo, tbl_Shipment.Released, tbl_Return.ReadmitID
FROM (qry_Order INNER JOIN tbl_Shipment ON qry_Order.OrderID = tbl_Shipment.OrderID) LEFT JOIN tbl_Return ON tbl_Shipment.ShipmentID = tbl_Return.ShipmentID


WHERE ((([LastName] & " , " & [FirstName]) Like [forms]![flkp_VerifyShipment_MRN].[cboMRN] & "*"))
ORDER BY qry_Order.LastName

This form takes info from an order query that combines customer and order details to display the order.

From this form I have a shipment form that provides for entry of shipment details. This form is displayed by:
SELECT [LastName] & " , " & [FirstName] AS Name, tbl_Order.*, tbl_Shipment.*, [ReceiptDate]-[ShipDate] AS LOS
FROM (tbl_Info_Pt LEFT JOIN tbl_Order ON tbl_Info_Pt.MRN = tbl_Order.MRN) LEFT JOIN tbl_Shipment ON tbl_Order.OrderID = tbl_Shipment.OrderID

The problem that i am having is that when I add the shipment details a duplicate record is placed in the orders table (which I do not want to happen). I have tried several differnt methods of solving this problem but am coming up with nothing.

Please help.

[June7]

You need to use form/subform arrangements for data entry. Review: http://office.microsoft.com/en-us/ac...010098674.aspx

[BFlat]

I'm not following. What query would I use for the main form and then what query would I utilize for the subform/'data entry'?

[June7]

RecordSources for the forms can be just the tables or a query. If you want to provide the project for analysis, I will look at.

Possibly main form bound to customers, subform bound to orders, subsubform bound to order details would serve your purpose. Much depends on your work flow. Do you want to start an order and select customer from combobox? If customer is new then need to enter customer record 'on-the-fly'. Or do you want to open/create customer record then enter their order in the subform and order details in the subsubform?

[BFlat]

Here you go.

I had to clear out all the unnecessary queries. I've also cleared out all of the shipment records so you can start with fresh data.

[June7]

Okay, I see there isn't an OrderDetails. The relationships are Info_Cst to Order to Shippment (note the 2 p's in the table name).

The main form RecordSource should not have tblOrder and tblShippment, just be tblInfo_Cst.
Order form RecordSource should not have tblShippment and tblInfo_Cst, tblOrder.
Shippment form RecordSource should not have tblOrder and tblInfo_Cst, just tblShippment.

What is the relationship between Order and Shippment? Is it one-to-one? Will every order always have a shipment and only one shipment? Will every shipment have only one order? If both are true, can just have one table. If not, need another subform. Two options for subform.

1. Form/Subform/SubSubform: Order subform set to Single Form view. You have that already. Then have a subform for Shippment on the Orders subform.

2. have Shippment subform sit side-by-side with Order subform. Linking Shippment to Order will be trickier http://www.fmsinc.com/MicrosoftAcces...edSubforms.asp

I have never seen combobox used as you have on the Order form.

[BFlat]

Ok. I've set up everything accordingly. The question now is how do I pass everything to the proper locations? I have 5 fields that I am passing from the Customer Verification form (flkp_VerifyPt_Last). The MRN should be passed to the Main form and From, To, Date and Payment should be passed to the Order sub form.

[BFlat]

Sorry, I forgot to explain the relationships. Each customer can have several orders but each order can have only one shipement.

[June7]

You set everything up how, which approach? Provide revised db.

If properly set up there should be no reason to 'pass' data. That is what my suggested options are intended to accomplish. The structure you have with all 3 tables included in the RecordSource of all 3 forms along with code that opens Shipment form is all bad design and will continue to cause you problems. The new record generated in Orders is to be expected with this structure. Have you reviewed the links I provided?

So the relationship of orders to shipments is one-to-one and every order will always have a shipment? Again, a single table instead of two might be the easiest way to accommodate your situation.

The flkp_VerifyPt_last module is missing the Option Compare Database line in the header. Here is why it should always be in place http://support.microsoft.com/kb/98227

[BFlat]

Attached is the revised db. I utilzed setup 1 with the form/subform/subsubform.

The form I want to pass info from is the customer verification form. This form is used to verify if a customer exists while also displaying their order info. I would like the data from the selected record in this form to pass to the appropriate fields in the Main Shippment form and subforms so the user can see all the customer info and order info as they append the shippment data.

[June7]

Before I continue with you on your original path, consider the attached example.

[BFlat]

I really like your idea!! It's a lot simpler to see all the data together! The problem is I'd have to redo my entire db and unfortunatley I'm not budgeted for that as of yet as I have to present a working db. Possibly after the presentation I can obtain clearance to proceed with the changes you suggested.

[June7]

If you still want to open a form just to enter shipment info, there is no need for a form/subform/subsubform and also no need to 'pass' data. Check out this version. It does use a query that combines the 3 tables but the code will not duplicate record.

[BFlat]

Thanks for all your help.

I had to take a little break just to remove my self. I am running into one problem when I hit the append button it opens to a blank shipment form. I was tyring to fix it when I selected the append button again and to my surprise the shipment form opened again but this time populated. I continued to test this and its pretty standard. For some reason the form always opens blank and then to the populated form. Is there any reason why this is happening?

[June7]

It won't open populated for me at all, not even on repeated clicks of the Append button.

Set frmShipment DataEntry property to No.

I see you eliminated the search combobox and sticking with the navigation bar only? They can both be useful.