Row Source / Not In List

[RobertBC]

good day!:friends:

Straight forward:

scenario:
I had

Table (Field: OrderNo, OrderDate, CustomerID),
OrderDetails Table (Field: OrderNo, PONo, ProductID, UnitPrice, Qty, Amount),
Invoice Table (Field: InvNo, CustomerID, InvDate),
InvoiceDetails Table (Field: InvoiceNo, PONo)problem:

Iam using combo box to select PONo in invoice details base on the customer PONo (data will get into OrderDetails), i create a query to select all the PONo of a certain Customer, the problem here is every single PONo that the customer has is displayed irregarless if its already invoiced or not...

in short in InvoiceDetails Form how do i select all PONo by Customer That will Display only PONo that is not already invoiced.. meaning All PONo Excluding PONo That is already exist in Previous Invoice... Thanks!

[OBP]

In the query supplying the form enter
Is Null
in the criteria row for the InvoiceNo field.

[RobertBC]

heres my sample query i use in my combo box to select PONo of customer excluding the PONo that exist in previous Invoice..

SELECT DISTINCTROW [Order Details].PONo, Orders.OrderDate, [Order Details].PlateNo, [Order Details].VehicleType, [Order Details].ProductID, [Order Details].Quantity, [Order Details].UnitPrice, [Order Details].Price
FROM (Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID=[Order Details].OrderID
WHERE (((Orders.CustomerID)=forms!frmInvoice!InvCustomerID)
AND [Order Details].PONo <> ((SELECT InvoiceDetails.PONo FROM (frmInvoice INNER JOIN InvoiceDetails On InvoiceDetails.InvoiceID = frmInvoice.InvoiceID) where frmInvoice.CustomerID = forms!frmInvoice!InvCustomerID))


but ofcourse this is wrong because the return value for my subquery is multiple..

how do i do this in procedure..

[RobertBC]

i attach a sample dba here to more instruction for what ever i asked for..
thank you very much for your time guys

:friends:

[RobertBC]

pls Help

thanks!:friends: