Hide/Unhide subform based on continuous subform

[Niniel]

Hello,

I have a parent table that is linked in a 1->many relationship to an Answers
table via RecordID. The Answers table in turn is linked to a Questions table
via QuestionID. The Answers table has the following fields - AnswerID (PK),
RecordID (FK), QuestionID (FK), Answer [yes/no field], Notes [txt].
On a form based on the parent table, I have a subform based on the Answers
child table, so that for one record in the parent table, several records from
that child table are shown in a continuous subform. They show the text field
[the question] and the yes/no field.
What I am trying to figure out is if/how I can create a system so that when
one specific checkbox is checked, an action is performed [I want to unhide
another subform on the main form that has nothing to do with the Answers
table, but is based on another child table].

I was thinking something along these lines might work:

if [Forms]![frmParentForm]![RecordID] and
tblAnswers.QuestionID(99) = true then
subform2.visible = true
else subform2.visible = false [is an else statement always required?]
end if

This is not really an attempt at coding, more an illustration, but the point
is that I believe I would have to reference the questionID and the recordID
in order to correctly identify the checkbox.

I would greatly appreciate if somebody could help me with the coding here.

Thank you.

 

[TonyT]

Hi Niniel,

You can use the after_Update event of the check box to determine if the
values you want are met or not, but you cannot just reference the table
values as you have(tblAnswers.QuestionID), either it needs to be a field on
one of the open forms or subforms, or you need to perform a DLookup to find
it's value. for example if you just wanted to hide subform2 if checkbox
chkMyField was true then use;

If Me.chkMyField = True Then
Forms!frmParentForm.Subform2.Visible = False
Else Forms!frmParentForm.Subform2.Visible = True
End If

The Else or ElseIf part of an If statement is optional, but in this instance
is useful to un-hide the subform.

Give it a go and post back with the code you are trying if it doesn't work
as you hoped!

TonyT..

 

[Niniel]

Hello Tony,

Ah, ok, it looks like this may work.
Here's the Dlookup command I came up with:

= dlookup ("[Answer]", "qryExhibitsAbstracts" ,
"[ActivityID]=Forms!frmActivityApplicationForm![ActivityID] and
[QuestionID]=99")

The result of this should be either "0" or "-1" [false/true].

The next step would be to incorporate that into an If statement.

Would that go like this?

If dlookup ("[Answer]", "qryExhibitsAbstracts" ,
"[ActivityID]=Forms!frmActivityApplicationForm![ActivityID] and
[QuestionID]=99") = True Then
Forms!frmActivityApplicationForm.Subform2.Visible = True
Else Forms!frmActivityApplicationForm.Subform2.Visible = False
End If

 

[TonyT]

Would that go like this?

If dlookup ("[Answer]", "qryExhibitsAbstracts" ,
"[ActivityID]=Forms!frmActivityApplicationForm![ActivityID] and
[QuestionID]=99") = True Then
Forms!frmActivityApplicationForm.Subform2.Visible = True
Else Forms!frmActivityApplicationForm.Subform2.Visible = False
End If

a couple of syntactic changes;

If dlookup ("[Answer]", "qryExhibitsAbstracts" ,
"[ActivityID] = " & Forms!frmActivityApplicationForm![ActivityID] & " AND
[QuestionID] = 99") = True Then '= True is optional with boolean If's
Forms!frmActivityApplicationForm.Subform2.Visible = True
Else Forms!frmActivityApplicationForm.Subform2.Visible = False
End If

apart from that it looks good to me,

TonyT..

 

[Niniel]

Hello Tony,

Hurray!
It's working now thanks to your corrections. I am using the Dlookup function
for browsing through records, and different code attached to the checkbox for
entering data/editing. Works great, except I just noticed that there's a
problem with the checkbox code - when question 99 is already checked, and
another checkbox on that continuous subform is then checked, the target
subform is made invisible.
If it's not one thing that's wrong, it's another.

Thank you very much for your assistance!

___

Private Sub Answer_AfterUpdate()
If Me.Answer = -1 And Me.QuestionID = 99 Then
Forms!frmBrowseApplications!sfrmBrowseSubmissions.Visible = True
Else: Forms!frmBrowseApplications!sfrmBrowseSubmissions.Visible = False
End If
End Sub

Private Sub Form_current()
If DLookup("[Answer]", "tblActivityAnswers", "[ActivityID] = " &
Forms!frmBrowseApplications![ActivityID] & " AND [QuestionID] = 99") = True
Then
Forms!frmBrowseApplications!sfrmBrowseSubmissions.Visible = True
End If
End Sub

Would that go like this?

If dlookup ("[Answer]", "qryExhibitsAbstracts" ,
"[ActivityID]=Forms!frmActivityApplicationForm![ActivityID] and
[QuestionID]=99") = True Then
Forms!frmActivityApplicationForm.Subform2.Visible = True
Else Forms!frmActivityApplicationForm.Subform2.Visible = False
End If

a couple of syntactic changes;

If dlookup ("[Answer]", "qryExhibitsAbstracts" ,
"[ActivityID] = " & Forms!frmActivityApplicationForm![ActivityID] & " AND
[QuestionID] = 99") = True Then '= True is optional with boolean If's
Forms!frmActivityApplicationForm.Subform2.Visible = True
Else Forms!frmActivityApplicationForm.Subform2.Visible = False
End If

apart from that it looks good to me,

TonyT..

 

[Niniel]

Came up with some minor modifications, and now all is well.
Thanks again for all your help!
___

Private Sub Answer_AfterUpdate()

If Me.Answer = -1 And Me.QuestionID = 99 Then
Forms!frmBrowseApplications!sfrmBrowseSubmissions.Visible = True
End If

If Me.Answer = 0 And Me.QuestionID = 99 Then
Forms!frmBrowseApplications!sfrmBrowseSubmissions.Visible = False
End If

End Sub
_

Private Sub Form_current()

If DLookup("[Answer]", "tblActivityAnswers", "[ActivityID] = " &
Forms!frmBrowseApplications![ActivityID] & " AND [QuestionID] = 99") = True
Then
Forms!frmBrowseApplications!sfrmBrowseSubmissions.Visible = True
Else: Forms!frmBrowseApplications!sfrmBrowseSubmissions.Visible = False
End If

End Sub