In need of a field population to activate a Subform

[VBChallenged]

Ok, I am working on an Access DB (2002) that needs to show a Subform when a field is populated with a number. In this case when VendorNumber is populated and it does not match the criteria of [Preconversion] and [Letter] and [VatCompliant] =true, then I want the subform to open up with the information. The link between the form and the VendorNumber is VENID.

I have tried to use a bunch of queries but it has not given me any joy. So I figure VB will have to be the way to go.

[lotus18]

Can you show us what have you done so far?

Rey Sean

[VBChallenged]

OK So far I have done the following:

IIf([VATCompliance_tbl]![PreConversion] Or [VATCompliance_tbl]![Letter] Or [VATCompliance_tbl]![VATCompliant] = YES), “”, check INVID and 1st letter sent.

Iif ( [VATCompliance_tbl]![PreConversion] and [VATCompliance_tbl]![Letter] and [VATCompliance_tbl]![VATCompliant] = False ),


Expr1: IIf(([VATCompliance_tbl]![PreConversion] And [VATCompliance_tbl]![Letter] And [VATCompliance_tbl]![VATCompliant]=False),[Forms]![VATCompliance_frm].[OnQuery],Null)

EXPR3: IIf([VATCompliance_tbl]![VATCompliant] And [VATCompliance_tbl]![PreConversion] And [VATCompliance_tbl]![Letter]="",[InvoiceTrackingW/Sum_qry]![INVID],[InvoiceTracking_tbl]![VendorNumber])

EXPR4: IIf([Test VATOutstanding Query]![PreConversion] Or [Test VATOutstanding Query]![Letter] Or [Test VATOutstanding Query]![VATCompliant]=True,"",[Test VATOutstanding Query]![INVID])

EXPR5: IIf([VATCompliance_tbl]![VATCompliant] Or [VATCompliance_tbl]![PreConversion] Or [VATCompliance_tbl]![Letter]=True,"",[InvoiceTrackingW/Sum_qry]![INVID])

INVID: (SELECT [INVID] FROM [InvoiceTrackingW/Sum_qry] WHERE [VATCompliance_tbl]![VATCompliant] And [VATCompliance_tbl]![PreConversion] And [VATCompliance_tbl]![Letter]="")

[VBChallenged]

I have had this going around in my head for days now. Trying to get the correct way to do this. I tried to cut and paste my DB in here but it would not. Not sure how to show you guys everything.
This is all new to me.

[VBChallenged]

Here goes the explanation, When the VendorNumber is populated and the statement [preconversion] and [letter] and [vatcompliant] = False I would like the subform to populate with only the information that is in my query VATOutstanding. For that to happen I would need the to use INVID to link the tables / querys VATOutstanding_qry, InvoiceTrackingW/Sum_qry. The problem is right now I do not have the VATOutstanding_qry working right now as the final table VATOutstanding_tbl doesn’t have anything in it.

I wanted to run a series of queries,
If [preconversion] and [letter] and [vatcompliant] = False then enter INVID

If INVID matches in VATOutstanding_tbl populate form

If VATOutstanding_tbl is populated then match with INVID and VendorNumber in InvoiceTracking_qry

If all of the above happens then populate the subform so we can check what other invoices are out there that are not VAT Compliant.

If we get an invoice that matches the criteria in the subform we can check that we matched it with a check box so that this line will not come up again when the vendornumber is populated next.

Does any of this make any sense?