Design Form field based on Query Field "WHERE" is conditional value

[Antone via AccessMonster.com]

I am designing a Form called checks_tabular to enter invoices to pay (FOR
CHECK RUNS).

In Form, I have several combo boxes with drop down menu. Claim Number, Payee
ID....etc

I can isolate all vendors associated with a particular claim. But, I need to
have a selection to show "ALL" vendors (not only those associated with a
particular claim). So, I set a flag YES/NO, where "YES" is to see all
vendors (in case, there is a new vendor associated with claim....we can look
at our entire vendor list to see if this vendor is set up).

The Payee Query joins two tables Payees and Claim Transactions (where Payee
ID joins the two).
In Payee Query, I set claim number to "WHERE" and criteria Forms]!
[checks_tabular]![CLAIM_NUMBER]. OK, this works to isolate all payees
associated with one particular claim.

But, I tried IIF statement based on YES=1, NO=2 flag (to see the entire
vendorlist. I don't know if IIF works with WHERE. But, I set formula as
IIF(FLAG=2,Forms]![checks_tabular]![CLAIM_NUMBER], " "). I think WHERE might
want to isolate one claim number, not leave criteria blank to concern all
vendors associated with all claims.

I can't get IIF statement to distinguish between

1) The Payee (Vendors) associated with a specific claim
2) The Entire Payee List (3,000+ vendors).

Can anyone help me with this one? I don't think it likes the IIF statement
with with WHERE. Else, I don't think I can use wildcard in criteria for
query to concern all claims with WHERE. This one is a little tricky.

Thank you in advance,

Antone
RHI Consulting Team

 

[Piet Linden]

Antoine,

build your filter for your form in code, and pass it in when you open
the form...

DoCmd.OpenForm "MyForm"... one of the arguments is a filter - just use
a valid WHERE clause. Just build an appropriate filter for each of
your options, and then pass the one that matches what you're filtering
for.

 

[Antone via AccessMonster.com]

Piet,

Thank you for your assistance. I am still a beginner at Access. It's a
combo box that I want to filter based on flag 1=yes and 2=no

If flag, 1 = yes, then I want to see entire vendor list
If flag, 2 = no, I want to see vendor list isolated for a particular claim.
(query currently does this correctly).

Should I put this code or similar code in Combo Box in Event before action?


Can you elaborate a little more on your suggestions below?

Thank you so much for your assistance!

Tony

 

[Antone via AccessMonster.com]

Dear Piet,

It's does not like my filter. This combobox "Key_Yes_No" is a flag where
YES = 1 and NO = 2. If this flag = 2 then I want to filter that claim
number from combobox 1. I think I might need to use LIKE.

Can I filter that field to query (which I rather do)? Or do I need to filter
that field in table?

I don't know if I need that extra coding in middle....I was follow example on
Forum for similar filters.

Thanks again for you assistance! Tony
_________________________________________________________


Private Sub Key_Yes_No_AfterUpdate()

DoCmd.OpenForm "checks_tabular"

Dim strSQL As String
strSQL = "Select" & Me!CLAIM_NUMBER
strSQL = strSQL & " from payee"
Me!PayeeCombo.RowSourceType = "Table/Query"
Me!PayeeCombo.RowSource = strSQL

IFF(KEY_Yes_No = 2, Select CLAIM_NUMBER from tblclaim_checks_combined where
CLAIM_NUMBER = Forms!checks_tabular
!CLAIM_NUMBER, CLAIM_NUMBER = "IS NOT NULL")

Forms!checks_tabular.PayeeCombo.Requery
End Sub

Piet,

Thank you for your assistance. I am still a beginner at Access. It's a
combo box that I want to filter based on flag 1=yes and 2=no

If flag, 1 = yes, then I want to see entire vendor list
If flag, 2 = no, I want to see vendor list isolated for a particular claim.
(query currently does this correctly).

Should I put this code or similar code in Combo Box in Event before action?

Can you elaborate a little more on your suggestions below?

Thank you so much for your assistance!

Tony

Antoine,

[quoted text clipped - 5 lines]

your options, and then pass the one that matches what you're filtering
for.