Passing parameters to a subform (1 Viewer)

[wgma]

I have 3 forms that I use in my Access 2007 database. the first form is for data entry, the second form is an invoice which allows edits, and the third form displays the charges and only has one column and may have multiple records.

The third form is a sub form and is used on both of the first two forms. the first form (Bill Of Lading) has a button that opens the second form (invoice).

The Bill Of Lading seems to work correctly but only more testing will let me know for sure. When the invoice is opened the subform still displays the values for the first record. For the second record there are no charges so the sub form should be blank.

I am not sure how to pass the values to get the correct records passed back.

Bill Of Lading's record source is the main table. I provide the record ID on the form in a hidden textbox.

Invoice form's record source is a query.

SELECT lkupshipper.company+Chr(13)+Chr(10)+lkupshipper.address AS Shipper, tlbShip.ShipName, tblBillOfLading.ExportRef, tblBillOfLading.BillOfLadingNbr, tblShipment.NbrOfPkgs, tblShipment.Description, tblShipment.GrossWeight, tblBillOfLading.FreightCharges, tblBillOfLading.ID
FROM ((tlbShip RIGHT JOIN (lkupshipper RIGHT JOIN tblBillOfLading ON lkupshipper.id=tblBillOfLading.ShipperID) ON tlbShip.ID=tblBillOfLading.ExportCarrierID) INNER JOIN tblShipment ON tblBillOfLading.ID=tblShipment.BillOfLadingID)
GROUP BY lkupshipper.company+Chr(13)+Chr(10)+lkupshipper.address, tlbShip.ShipName, tblBillOfLading.ExportRef, tblBillOfLading.BillOfLadingNbr, tblShipment.NbrOfPkgs, tblShipment.Description, tblShipment.GrossWeight, tblBillOfLading.FreightCharges, tblBillOfLading.ID, lkupshipper.company

the code to open the invoice form is

Private Sub cmdOpenInvoice_Click()
DoCmd.OpenForm "frmFreightInvoice", acNormal, , "[qryFreightInvoice.ID]=" & Me!ID
End Sub




The subform's record source is a query as well.

SELECT Sum(tblPrepaid.Prepaid) AS Prepaid
FROM tblPrepaid
WHERE tblPrepaid.BLID=forms!frmBillOfLading!txtID;


Since the subform is on both of the other forms I am having a hard time getting the correct ID passed to that query. maybe there is a better way to do this?

Thanks.

 

[neileg]

If you take the WHERE clause out of your query, the parent/child links between the main form and the subform will control the subform display. Make sure the subform wizard is activated when you add the subform to the main form and Access will do this for you.

 

[wgma]

I was going to create another subform but there is no Form Wizard on the toolbar.

 

[wgma]

OK, I was able to use the wizard to create a subform for the invoice form. However, I need to be able to change the relationship as to which ID it is referencing. Where do I do that?

 

[wgma]

Got it all figured out. Thanks for your help.