Search Form Code Relating to a Second Table

[DeanRowe]

Hi,

I have two tables, "NewPurchaseTable" and "TheirPurchaseTable", I am viewing them side by side in subforms on a main form called "InvoiceForm". Each subform is called "OurPurchaseTableSubForm" and "TheirPurchaseTableSubform" respectively, and they are both being viewed in continuous forms view.

Each subform is also a search form so each set of data can be filtered individually, I have managed to crack the codes for all the filters except one that I am tearing my hair out over.

Here is an example of code that I use for a check box filter called "MatchedFilter" which filters out recordsets that have got an entry in the "Invoice ID" field, in the "OurPurchaseTableSubform":

If Me.MatchedFilter = True Then
    strWhere = strWhere & "([Invoice ID]) Is Null AND "
    ElseIf Me.MatchedFilter = False Then
    strWhere = strWhere & "([Invoice ID]) Is Not Null AND "
    End If

This code works perfectly, and I am looking to perform something similar but much more complicated for the "TheirPurchaseTableSubform".

I want to filter out records where the "PurchaseID" from "TheirPurchaseTableSubform" is entered in the "Invoice ID" field of the "NewPurchaseTable". So, the code would look similar to this:

If Me.MatchedFilter = True Then
    strWhere = strWhere & "(NewPurchaseTable.[Invoice ID]=[PurchaseID) Is Null AND "
    ElseIf Me.MatchedFilter = False Then
    strWhere = strWhere & "(NewPurchaseTable.[Invoice ID]=[PurchaseID) Is Not Null AND "
    End If

However it always comes out with errors no matter how I tweak it.

Does anyone know how this can be changed to filter records where the purchaseID is entered in the [Invoice ID] field of the "NewPurchaseTable"?

I don't know whether to go down this route, or to try to incorporate a dlookup function - I did go down the query route for a while to try and learn the code and I managed to write a SQL code that FOUND the matching records, but couldn't manage to change it to find the non-matching records:

SELECT PurchaseTable.*
FROM PurchaseTable INNER JOIN NewPurchaseTable ON PurchaseTable.PurchaseID=NewPurchaseTable.[Invoice ID];

I'm suffering alot on this problem and wanted to get this finished so I can enjoy my christmas break!!! any help would be welcomed greatly!

Thank you for reading this post, and sorry it turned into a war & peace.

Dean

 

[DeanRowe]

only took 8 hours in the end! had to base the second subform on a query that included the "Invoice ID" field in it, so it was then displayed on the subform and filtered out that way.