Help with Un/hide

[Niniel]

Hello,

I want to put code in the On Current event of a continuous subform on my main form so that when I click on different records and thus change what's shown in another subform right next to it, the second subform is hidden under certain conditions. Those conditions are the presence, or not, of data [a date] in an unbound text box on the main form (DDate) (actually, the source for that data is on that second subform, I just have it copied into the text box because I thought it might be easier to reference it that way).

But my code is definitely wrong for that; I always have trouble to correctly
reference other subforms and controls on them.

Private Sub Form_Current()

If Forms.Parent.DDate = Null Then
Forms.Parent.sfrmSpeakerCOI1.Visible = False
Forms.Parent.NoDisclosureTxt.Visible = True
Else
Forms.Parent.sfrmSpeakerCOI1.Visible = True
Forms.Parent.NoDisclosureTxt.Visible = False
End If

End Sub

Could somebody please take a look and tell me what I need to change?

Thank you.

 

[RuralGuy]

See if this makes any difference.

Private Sub Form_Current()

If Len(Me.Parent.DDate & "") = 0 Then
   Me.Parent.sfrmSpeakerCOI1.Visible = False
   Me.Parent.NoDisclosureTxt.Visible = True
Else
   Me.Parent.sfrmSpeakerCOI1.Visible = True
   Me.Parent.NoDisclosureTxt.Visible = False
End If

End Sub

 

[Niniel]

Thank you!

I am getting some very strange behaviour now - when I start the form, I receive an error message - error 2455: You entered an expression that has an invalid reference to the property Form/Report.
After I click that error message away, the form isn't shown the way I want it but if then I change back and forth between records in the subform, it does what I want it to do.

 

[Niniel]

Ok, I fixed the incorrect display by putting the code into the On Current event of the main form as well.
Still getting that initial error message... Maybe I have an error elsewhere.

 

[RuralGuy]

The loading sequence for forms is to load the SubForm first. That's why you are getting the error. How about creating a public Boolean variable in the SubForm and check it in the current event before you change anything? The Boolean variable will initialize to False and change it to True in the Current event of the MainForm. Then you will know that the MainForm and all of the SubForms have been loaded. Just a thought.

 

[Niniel]

I'm afraid I do not quite follow.

But would checking the date field directly in a third subform make any difference? Something like below? Or would that produce the same error since the one subform is loaded before the other?

If Len(Forms!frmRSC!sfrmDisclosures1!DisclosureDate & "") = 0 Then

Update: No, that doesn't change a thing.

 

[Niniel]

That error doesn't cause any problems, so I'm wondering, is there a way to just suppress it is that the user won't have to see it?

 

[RuralGuy]

It is a much better practice to correct the source of the error than simply hide it. What part don't you understand? I'll help you fix it.

 

[Niniel]

Thank you.
Essentially, I don't understand the part about the boolean variable and how that can be used to prevent the error.

 

[RuralGuy]

In the SubForm's code module just below the Option Explicit and before any Sub's put:
Public TestOK As Boolean
Then change your OnCurrent event code to:

Private Sub Form_Current()

If TestOK Then
   If Len(Me.Parent.DDate & "") = 0 Then
      Me.Parent.sfrmSpeakerCOI1.Visible = False
      Me.Parent.NoDisclosureTxt.Visible = True
   Else
      Me.Parent.sfrmSpeakerCOI1.Visible = True
      Me.Parent.NoDisclosureTxt.Visible = False
   End If
End If

End Sub

Then put the following code in the Current event of your MainForm
Me.SubFormControl.FORM.TestOK = True
...using the name of your SubFormControl in which we just changed the Current event.

 

[Niniel]

Amazing.

Works like a charm.
Thank you very much!

 

[RuralGuy]

Where there is a will there is a way. Glad I could help.

 

[Niniel]

DDate gets its date information from a field that is based on a query. There is currently one date per speaker, but in the future, there will be more. I've been trying to use the max function to limit the dates in the query to the most recent ones, but that is creating problems with this code, I'm getting error messages when I try to create a new main record, and when I browse between records.
Any idea why?

 

[RuralGuy]

Is DDate on the MainForm or the SubForm? What are the errors?

 

[Niniel]

DDate is on the main form; I only mentioned it for continuity though; I'm actually directly referencing the date field on the subform. But since DDate just mirrors that, I don't think it makes any difference.
If Len(Forms!frmRSC!sfrmDisclosures1.Form!DisclosureDate & "") = 0 Then etc.

I'm getting errors "2424: The expression you entered has a field, control or property name that Microsoft Access can't find" and "2427: You entered an expression that has no value" when I try to create a new record, and 2424 when I browse between records.

 

[RuralGuy]

It *does* make a difference if the SubForm has no records. You may wish to check for records in the SubForm before referencing it.
Me!sfrmDisclosures1.Form.RecordCount > 0

 

[Niniel]

Now I'm getting "Error 2465: Application-defined or object-defined error", so I guess I put this in the wrong place.

If TestOK Then
If Me.Parent.sfrmDisclosures1.Form.RecordCount > 0 Then
If Len(Forms!frmRSC!sfrmDisclosures1.Form!DisclosureDate & "") = 0 Then

 

[RuralGuy]

Where do you have the code? Do you have two SubForms on your MainForm?

 

[Niniel]

Yes, I have a main form, and on it are several subforms, 3 of which are important for this:
sfrmRSC-Speakers, sfrmDisclosures and sfrmSpeakerCOI.
The first contains information about the speakers, and the other two related information. If there's no disclosure date, there's no COI information, and in that case I would like to hide that subform, and display a label instead.

The code is in sfrmRSC-Speakers "current" event, as as the main form's "current" event [starting with "if len...]
Would it help if I posted a screenshot of my relationships diagram?

 

[Niniel]

I found a workaround - instead of checking for null in sfrmDisclosure's DisclosureDate field, I am now checking sfrmSpeakerCOI's COIID field.
I don't even need to check for the presence of records anymore for this to work.
I noticed that when there's no speaker in sfrmRSC-Speakers, sfrmDisclosure disappears from the main form, which is why there's no field to check, which is why I got all those error messages.
I have absolutely no idea why it is doing that, and I'd really like to find out and fix it, so if you can offer any more insight, I'd be very grateful.
But if you are tired of this problem, or just don't know what's going on, either, then at least this is not show-stopping problem anymore, just an oddity that can be ignored.

So thank you for all your help so far!