Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Form and Subform

Copper Contributor

I have a main Form named frmProjQry.  Within this main form, I have 5 subforms.  One subform is named sfrmWO (based on tblWO) while a second is named sfrmALbr (based on tblALbr).  I want sfrmALbr to display records where tblWO.WONo = tblALbr.WOOrder AND tblALbr.Cost Element <> to 890001 or 890002.

 

The following SQL for sfrmALbr is pulling records where tblWO.WONo = tblALbr.WOOrder; however, it continues to also include records where tblALbr.Cost Element is = to 890001 or 890002.

 

SELECT tblALbr.[Cost Element], tblALbr.[Cost Element Name], tblALbr.[Posting Date], tblALbr.TotalQty, tblALbr.[Valin RepCurLbr]
FROM tblWO LEFT JOIN tblALbr ON tblWO.WONo = tblALbr.WOOrder
WHERE (((tblALbr.[Cost Element])<>890001 Or (tblALbr.[Cost Element])<>890002) AND (([tblWO].[WOOrder])=[Forms]![frmProjQry]![sfrmWO]![WONo]))
ORDER BY tblALbr.[Posting Date];

 

RJF61_0-1677698493181.png

Is there an error with my syntax or logic?

1 Reply

run your query to find out if it has error.

you may also use this as your Criteria:

...
...
WHERE (NOT ((tblALbr.[Cost Element]) IN (890001, 890002))) AND (([tblWO].[WOOrder])=[Forms]![frmProjQry]![sfrmWO].Form![WONo]))