Ideas PLEASE

  • Thread starter Thread starter Lori
  • Start date Start date
L

Lori

I have a subform that I need to show certain data based on the current month.

The fields include Estimated and Actual $$ for each month. At present I have
all of the fields as Visible = No and the intent is to have All of the
Estimated Fields show up in Jan and Feb, but in Feb I need to see not only
the Est. field for Jan but the Actual Field as well. Right now I have the
code written as follows. I think my real problem is I don't know which
"event" to put it under. HELP

If Me.CurrentMo = 1 Then
Me.JanEst.Visible = True
Me.FebEst.Visible = True
Me.MarEst.Visible = True
Me.AprEst.Visible = True
Me.MayEst.Visible = True
Me.JunEst.Visible = True
Me.JulEst.Visible = True
Me.AulEst.Visible = True
Me.SepEst.Visible = True
Me.OctEst.Visible = True
Me.NovEst.Visible = True
Me.DecEst.Visible = True

ElseIf Me.CurrentMo = 2 Then
Me.JanEst.Visible = True
Me.JanAct.Visible = True
Me.FebEst.Visible = True
Me.MarEst.Visible = True
Me.AprEst.Visible = True
Me.MayEst.Visible = True
Me.JunEst.Visible = True
Me.JulEst.Visible = True
Me.AulEst.Visible = True
Me.SepEst.Visible = True
Me.OctEst.Visible = True
Me.NovEst.Visible = True
Me.DecEst.Visible = True

ElseIf Me.CurrentMo = 3 Then
Me.JanAct.Visible = True
Me.FebEst.Visible = True
Me.FebAct.Visible = True
Me.MarEst.Visible = True
Me.AprEst.Visible = True
Me.MayEst.Visible = True
Me.JunEst.Visible = True
Me.JulEst.Visible = True
Me.AulEst.Visible = True
Me.SepEst.Visible = True
Me.OctEst.Visible = True
Me.NovEst.Visible = True
Me.DecEst.Visible = True

ElseIf Me.CurrentMo = 4 Then
Me.JanAct.Visible = True
Me.FebAct.Visible = True
Me.MarEst.Visible = True
Me.MarAct.Visible = True
Me.AprEst.Visible = True
Me.MayEst.Visible = True
Me.JunEst.Visible = True
Me.JulEst.Visible = True
Me.AulEst.Visible = True
Me.SepEst.Visible = True
Me.OctEst.Visible = True
Me.NovEst.Visible = True
Me.DecEst.Visible = True

ElseIf Me.CurrentMo = 5 Then
Me.JanAct.Visible = True
Me.FebAct.Visible = True
Me.MarAct.Visible = True
Me.AprEst.Visible = True
Me.AprAct.Visible = True
Me.MayEst.Visible = True
Me.JunEst.Visible = True
Me.JulEst.Visible = True
Me.AulEst.Visible = True
Me.SepEst.Visible = True
Me.OctEst.Visible = True
Me.NovEst.Visible = True
Me.DecEst.Visible = True

ElseIf Me.CurrentMo = 6 Then
Me.JanAct.Visible = True
Me.FebAct.Visible = True
Me.MarAct.Visible = True
Me.AprAct.Visible = True
Me.MayEst.Visible = True
Me.MayAct.Visible = True
Me.JunEst.Visible = True
Me.JulEst.Visible = True
Me.AulEst.Visible = True
Me.SepEst.Visible = True
Me.OctEst.Visible = True
Me.NovEst.Visible = True
Me.DecEst.Visible = True

ElseIf Me.CurrentMo = 7 Then
Me.JanAct.Visible = True
Me.FebAct.Visible = True
Me.MarAct.Visible = True
Me.AprAct.Visible = True
Me.MayAct.Visible = True
Me.JunEst.Visible = True
Me.JunAct.Visible = True
Me.JulEst.Visible = True
Me.AulEst.Visible = True
Me.SepEst.Visible = True
Me.OctEst.Visible = True
Me.NovEst.Visible = True
Me.DecEst.Visible = True

ElseIf Me.CurrentMo = 8 Then
Me.JanAct.Visible = True
Me.FebAct.Visible = True
Me.MarAct.Visible = True
Me.AprAct.Visible = True
Me.MayAct.Visible = True
Me.JunAct.Visible = True
Me.JulEst.Visible = True
Me.JulAct.Visible = True
Me.AulEst.Visible = True
Me.SepEst.Visible = True
Me.OctEst.Visible = True
Me.NovEst.Visible = True
Me.DecEst.Visible = True

ElseIf Me.CurrentMo = 9 Then
Me.JanAct.Visible = True
Me.FebAct.Visible = True
Me.MarAct.Visible = True
Me.AprAct.Visible = True
Me.MayAct.Visible = True
Me.JunAct.Visible = True
Me.JulAct.Visible = True
Me.AulEst.Visible = True
Me.AugAct.Visible = True
Me.SepEst.Visible = True
Me.OctEst.Visible = True
Me.NovEst.Visible = True
Me.DecEst.Visible = True
ElseIf Me.CurrentMo = 10 Then
Me.JanAct.Visible = True
Me.FebAct.Visible = True
Me.MarAct.Visible = True
Me.AprAct.Visible = True
Me.MayAct.Visible = True
Me.JunAct.Visible = True
Me.JulAct.Visible = True
Me.AugAct.Visible = True
Me.SepEst.Visible = True
Me.SepAct.Visible = True
Me.OctEst.Visible = True
Me.NovEst.Visible = True
Me.DecEst.Visible = True

ElseIf Me.CurrentMo = 11 Then
Me.JanAct.Visible = True
Me.FebAct.Visible = True
Me.MarAct.Visible = True
Me.AprAct.Visible = True
Me.MayAct.Visible = True
Me.JunAct.Visible = True
Me.JulAct.Visible = True
Me.AugAct.Visible = True
Me.SepAct.Visible = True
Me.OctEst.Visible = True
Me.OctAct.Visible = True
Me.NovEst.Visible = True
Me.DecEst.Visible = True
ElseIf Me.CurrentMo = 12 Then
Me.JanAct.Visible = True
Me.FebAct.Visible = True
Me.MarAct.Visible = True
Me.AprAct.Visible = True
Me.MayAct.Visible = True
Me.JunAct.Visible = True
Me.JulAct.Visible = True
Me.AugAct.Visible = True
Me.SepAct.Visible = True
Me.OctAct.Visible = True
Me.NovEst.Visible = True
Me.NovAct.Visible = True
Me.DecEst.Visible = True
End If
End Sub
 
At what point do you want this data to show up? If you want it to show up
when the form is opened, then I would use either the On Load or On Open
event. If you want it to show up when the individual form is selected, then
I would use the On Current event.
 
I've tried on open but I think the problem is that there is a unbound filter
on the form that switches from project to project. I tried creating a
separate form all together for the table and used the on open event it
doesn't seem to do any good. Its giving me everything even though most of it
should be hidden.
 
I'm assuming Me.CurrentMo is a form on your field. How is it updated?

Also, have you thought of throwing some code into your on Open or On Load
event to simply set all the fields' visible property to false? It may be
carrying over the visible settings from the previous time it was opened.
 
All of the fields are set to false. The CurrentMo is a field on my form that
pulls the month from a # field that the user enters on the submenu for the
current project I'm working on. I've also tried it with formatting the date
where the current date is formatted as simply the month...

I've also tried added coding to include both True and false for the fields I
want but nothing seems to work...HELP!!! :(

If Me.CurrentMo = 1 Then
Me.JanEst.Visible = True
Me.FebEst.Visible = True
Me.MarEst.Visible = True
Me.AprEst.Visible = True
Me.MayEst.Visible = True
Me.JunEst.Visible = True
Me.JulEst.Visible = True
Me.AugEst.Visible = True
Me.SepEst.Visible = True
Me.OctEst.Visible = True
Me.NovEst.Visible = True
Me.DecEst.Visible = True
Me.JanAct.Visible = False
Me.FebAct.Visible = False
Me.MarAct.Visible = False
Me.AprAct.Visible = False
Me.MayAct.Visible = False
Me.JunAct.Visible = False
Me.JulAct.Visible = False
Me.AugAct.Visible = False
Me.SepAct.Visible = False
Me.OctAct.Visible = False
Me.NovAct.Visible = False
Me.DecAct.Visible = False
 
If the month is entered by the user, then try putting it in the After Update
event of the field the user is entering it in. Then set everything back to
visible = false in the form's On Close event so everything starts out fresh
the next time the form opens.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


Lori said:
All of the fields are set to false. The CurrentMo is a field on my form that
pulls the month from a # field that the user enters on the submenu for the
current project I'm working on. I've also tried it with formatting the date
where the current date is formatted as simply the month...

I've also tried added coding to include both True and false for the fields I
want but nothing seems to work...HELP!!! :(

If Me.CurrentMo = 1 Then
Me.JanEst.Visible = True
Me.FebEst.Visible = True
Me.MarEst.Visible = True
Me.AprEst.Visible = True
Me.MayEst.Visible = True
Me.JunEst.Visible = True
Me.JulEst.Visible = True
Me.AugEst.Visible = True
Me.SepEst.Visible = True
Me.OctEst.Visible = True
Me.NovEst.Visible = True
Me.DecEst.Visible = True
Me.JanAct.Visible = False
Me.FebAct.Visible = False
Me.MarAct.Visible = False
Me.AprAct.Visible = False
Me.MayAct.Visible = False
Me.JunAct.Visible = False
Me.JulAct.Visible = False
Me.AugAct.Visible = False
Me.SepAct.Visible = False
Me.OctAct.Visible = False
Me.NovAct.Visible = False
Me.DecAct.Visible = False
Click to expand...
 
Lori said:
I have a subform that I need to show certain data based on the
current month.

The fields include Estimated and Actual $$ for each month. At present
I have all of the fields as Visible = No and the intent is to have
All of the Estimated Fields show up in Jan and Feb, but in Feb I need
to see not only the Est. field for Jan but the Actual Field as well.
Right now I have the code written as follows. I think my real problem
is I don't know which "event" to put it under. HELP
Click to expand...

This would probably go in a couple places.
IN the afterupdate event for the field where the month is set and in on open
if moving to a record where the month was already set.
Other problems include deciding which year is involved.

Note that if these fields are all in one table you have broken one of the
fundamental rules of a relational database. There *may* be valid reasons for
this but you will always be faced with additional coding that a proper
relationship will not require.
 
I would echo what Mike Painter said in that if you have a
table with fields like;

JanEst
JanAct
FebEst
FebAct
MarEst
MarAct
etc.
etc.

then you've got a table design problem. Over the lifespan of
your application you are going to spend a lot of extra time
and effort fighting against that design problem.

Having said that, there may be some solutions to your current
problem, but I'm unclear on a few things;

1) Do you want the estimated values to show for ALL months
ALL the time? (i.e. the estimates are projected out for the
entire year)

2) Are the actual values entered sometime in the following
month and prior to that those fields are Null?

3) Are you using a single form? continuous form?

--
_________

Sean Bailey


Lori said:
All of the fields are set to false. The CurrentMo is a field on my form that
pulls the month from a # field that the user enters on the submenu for the
current project I'm working on. I've also tried it with formatting the date
where the current date is formatted as simply the month...

I've also tried added coding to include both True and false for the fields I
want but nothing seems to work...HELP!!! :(

If Me.CurrentMo = 1 Then
Me.JanEst.Visible = True
Me.FebEst.Visible = True
Me.MarEst.Visible = True
Me.AprEst.Visible = True
Me.MayEst.Visible = True
Me.JunEst.Visible = True
Me.JulEst.Visible = True
Me.AugEst.Visible = True
Me.SepEst.Visible = True
Me.OctEst.Visible = True
Me.NovEst.Visible = True
Me.DecEst.Visible = True
Me.JanAct.Visible = False
Me.FebAct.Visible = False
Me.MarAct.Visible = False
Me.AprAct.Visible = False
Me.MayAct.Visible = False
Me.JunAct.Visible = False
Me.JulAct.Visible = False
Me.AugAct.Visible = False
Me.SepAct.Visible = False
Me.OctAct.Visible = False
Me.NovAct.Visible = False
Me.DecAct.Visible = False
Click to expand...
 
Back
Top