Hide or Show a label based on Criteria on other form

[crisis2007]

I have a form that is called SF_Event (form1). I have a linked form called F_EventDetails (form2). F_EventDetails has a subform in it called SF_Stat. I was wondering if there was a way that I can put a label on my Main form that is visible only if a control in the SF_Stat subform has a value in it (is not null). What I was thinking was:

If IsNull([Forms![F_EventDetails]![SF_Stat].Form![Car] Then
Me![Label1].Visible = False

Therefore, if the control named "Car" has a value in the subfom of form 2, then the label I have on my form 1 would say something like "More Info".

I tried this and it does not work. I believe I received a syntax error message. I placed the code on the OnOpen event but perhaps that was wrong. I am trying to learn how to use code but continue to struggle along with trial and error.

 

[GingerR]

Instead of messing with determining if a form has data in it, how about checking the underlying table?

Make a text box, and in the Control Source put

=If(Dlookup("Car","CarTable","EventID = " & Me.EventID) <> "","CAR IS THERE","CAR IS NOT THERE")

So dlookup is a way to reference a table that you are not even in. You have told us your table structure, but I assume that somehow the CAR is related to this form1. So the statement above looks into the table where the car is, where it's related to the Event, and if there's a CAR it says one thing (or ""/nothing if you want) and if there's not a car, it says something else.

You could also write a query that has the three tables related, then put the query in the dlookup statement instead. If you still have trouble, please detail your table structure.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[crisis2007]

Thanks! I will try it tomorrow and see where it goes. I really like the idea of referencing tables rather than forms. It makes more sense. I'll let you know how I do when I can get back to it.

 

[TheAceMan1]

How are ya crisis2007 . . .

As a first shot at this, in the [blue]On Load[/blue] event of [blue]SF_Stat[/blue], copy/paste the following:

[blue]   Dim frm As Form
   
   Set frm = Forms!SF_Event
   
   If Nz(Me![Car], "") <> "" Then
      frm![Label1].Visible = True
   Else
      frm![Label1].Visible = False
   End If
   
   Set frm = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[missinglinq]

You also need to remember that the form you're referencing, I believe, has to be open at the time!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[GingerR]

If you still need help, please describe better when each of these forms is opened and how they are related. In your first post you put "I have a linked form" but I don't know what a "linked form" is.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[crisis2007]

The form it needs to refer to is not open. I tried the textbox control solution and it did not work either. I know I need to be more specific. The SF_Event Form contains a couple of controls, including a memo field. When there is data in the memo field, I click a command button and it opens up another form "F_EventDetails" that is synchronized or linked to the SF_Event form through code I located. On this form it contains a subform "SF_Stat" where I have more controls. The subform SF_Stat is related to the "F_EventDetails" form through "EventKey" field.

My tables are set up as follows:
T_Stat T_Event

StatKey EventKey
EventKey ActivityKey
Car EventType
etc... etc....

I have the "linked" form only for formatting reasons to allow continuous forms while still having a subform visible (access 97). That subform is in the "linked" form. I hope that makes some sort of sense.

 

[crisis2007]

I did try this in a textbox:

=DLookUp("[Car]","T_Stat","[EventKey] = Form![EventKey]")

and it worked, returning the correct value. So I am certain that the solution that GingerR gave will work. I am just not getting the syntax right. I do want it to return a statement of a value being there (or not there), not the value itself.

 

[GingerR]

=Dlookup("Car","T_Stat","EventKey = " & Me.EventKey)

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[crisis2007]

I tried the statement but it comes back #Name? in the textbox.

 

[PHV]

#Name? in the textbox
You can't use the Me keyword in a ControlSource.
Have a look here:

http://support.microsoft.com/?kbid=209099

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GingerR]

Oh, yeah, wasn't paying attention.

=Dlookup("Car","T_Stat","EventKey = " & [EventKey])

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[crisis2007]

Thanks for continuing to help! I am still having problems. I did try :

=DLookUp("[Car]","T_Stat","[EventKey] = Form![EventKey]") and it does successfully bring up a value. But I try to manipulate it in the "If" statement and I still get #Name? I still want it to display "Car is there" or Car is not there." Not the actual value.

 

[randy700]

Do you mean ...

If(Not IsNull(DLookUp("[Car]","T_Stat","[EventKey] = Form![EventKey]"))) Then
    [i]textbox[/i] = "Car is there"
Else
    [i]textbox[/i] = "Car is not there"
End If

Randy

 

[crisis2007]

Yes, but can it just be placed in the textbox or would it have to be placed in an event of the form?

 

[randy700]

To use it as the control source of your textbox, try...
=iif(Not IsNull(DLookUp("[Car]","T_Stat","[EventKey] = Form![EventKey]")), "Car is there", "Car is not there")



Randy

 

[crisis2007]

Thanks for your help randy700! It did the trick. Thank you to everyone who helped me with this. I truly appreciate it!

 

[TheAceMan1]

How are ya crisis2007 . . .

If you truly appreciate being helped, why not give [blue]stars[/blue] (pinky's!) to [blue]those you fell are worthy![/blue] . . .

Be sure too visit the link at the bottom of my post. Its more important than you think . . .

See Ya! . . . . . .

Be sure to see thread181-473997