By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,203 Members | 1,383 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,203 IT Pros & Developers. It's quick & easy.

In need of a field population to activate a Subform

P: 4
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.
May 25 '08 #1
Share this Question
Share on Google+
4 Replies


lotus18
100+
P: 866
Can you show us what have you done so far?

Rey Sean
May 25 '08 #2

P: 4
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]="")
May 25 '08 #3

P: 4
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.
May 25 '08 #4

P: 4
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?
May 25 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.