subform filter problem - i think

  • Thread starter Thread starter Megan Flaherty via AccessMonster.com
  • Start date Start date
M

Megan Flaherty via AccessMonster.com

hi.
really tired. sorry if this is a stupid question.

i had an expression on a form
If (Eval("DLookUp(""[TierID]"",""[PRICES]"",""[TierID] =Form.[TierID]
"") Is Not Null")) Then ...
worked fine when the parameter was part of the main form.

but then, I had to make some changess and the TierID is now on a subform.
I can get the value: MsgBox "Selected Tier = " & Me.Form1![TierID]

So i tried
If (Eval("DLookUp(""[TierID]"",""[PRICES]"",""[TierID] =Me.Form1!
[TierID] "") Is Not Null")) Then ...

and

If (Eval("DLookUp(""[TierID]"",""[PRICES]"",""[TierID] =[Forms]![Form1]
![TierID] "") Is Not Null")) Then ...

and

a zillion other things.

can anyone do this one quickly?
 
Megan said:
i had an expression on a form
If (Eval("DLookUp(""[TierID]"",""[PRICES]"",""[TierID] =Form.[TierID]
"") Is Not Null")) Then ...
worked fine when the parameter was part of the main form.

but then, I had to make some changess and the TierID is now on a subform.
I can get the value: MsgBox "Selected Tier = " & Me.Form1![TierID]

So i tried
If (Eval("DLookUp(""[TierID]"",""[PRICES]"",""[TierID] =Me.Form1!
[TierID] "") Is Not Null")) Then ...
Click to expand...


This is very confusing, but I give it a try.

First, you say you have it on a form, but it's invalid as a
text box expression, so I'm wondering what "on a form" means
here? You're using VAB syntax, so I'll guess it's in an
event procedure somewhere.

Second, I can not figure out why you're using the Eval
function here. Is it only because you thought that Is Not
Null was the only way to check the result?

Third, the DLookup appears to look up the TierID value that
matches the TierID on the form. If so, why look up a value
that you already have on the form?? Maybe it's just
checking to see if the Prices table has an entry for the
form's TierID. Since this is the only scenario that makes
sense to me, I'll try to rewrite it this way:

If Not IsNull(DLookUp("[TierID]","[PRICES]","[TierID] =" &
Me.subformcontrolname.FORM.[TierID])) Then ...

Or, alternatively:

If DCount("*","[PRICES]","[TierID] =" &
Me.subformcontrolname.FORM.[TierID]) > 0 Then ...
 
Sorry, said I was tired - struggling my way thru this program. Learning a
lot as I go but mostly the hard way.

I originally had a form based on a customer table that included the tierID
as a field. At the time the customer would have 1 tierID regardless of the
year of the prices.

So my eval(dlookup).... was to determine first if the tier existed on the
price table and if it did, was it for the year I was trying to add prices
to.

I had a little subform that included data from another table with the year
and effective date with the link the customerID.

Then there was a change to the specs that said to allow the customer to
have a different tier for each year - if they wanted to.

So I took the tierID field off of the customer table and added to the table
with the year and effective date.

In the msgbox I am able to display the value from the subform for the
tierID.

Rambling so - in summary --
I am trying to get the value of the tierID from the subform, see if it
exists on the prices table for the selected year. If not, open the
enterprices form passing the tierID as the criteria.
 
MeganF said:
Sorry, said I was tired - struggling my way thru this program. Learning a
lot as I go but mostly the hard way.

I originally had a form based on a customer table that included the tierID
as a field. At the time the customer would have 1 tierID regardless of the
year of the prices.

So my eval(dlookup).... was to determine first if the tier existed on the
price table and if it did, was it for the year I was trying to add prices
to.

I had a little subform that included data from another table with the year
and effective date with the link the customerID.

Then there was a change to the specs that said to allow the customer to
have a different tier for each year - if they wanted to.

So I took the tierID field off of the customer table and added to the table
with the year and effective date.

In the msgbox I am able to display the value from the subform for the
tierID.

Rambling so - in summary --
I am trying to get the value of the tierID from the subform, see if it
exists on the prices table for the selected year. If not, open the
enterprices form passing the tierID as the criteria.
Click to expand...


I'm still somewhat confused here. Please use your table's
(and their field's) real name instead of just referring to
them as "another table" and "the table". I thought these
were refering to the Price table, but now I don't know what
they are.

If I've interpreted things correctly, I think the test for
the existence in "the table" of a price tier for the
customer in the main form's Current record would be:

If DCount("*","thetable","TierID =" & _
Me.subformcontrolname.FORM.[TierID] & _
" And CustomerID = " & Me.txtCustomerID) > 0 Then ...

But maybe you need to include a check for the year as
well???
 
I'm sorry to be so confusing.
I got a little confused on the subformcontrolname but I got it to work.

Here's what I used:

If Not IsNull(DLookup("[Year]", "[PRICES]", "[TierID]= " & "'" &
Me.Form1.Form.[TierID] & "' AND [Year] =" & "'" & Me.Form1.Form.[Year] &
"'")) Then

Thank you very much.
You got me out of a huge jam and rolling again.
 
Back
Top