Update form issue

[Niniel]

Hello,

I finally figured something out that's been bugging me for a while, only to
realize that it's not working quite right yet.
The code below is in my first subform's AfterUpdate event, and I also copied
it into the main form's Current event. All it seems to do though is hide the
second subform once I move away from the record where originally I made it
visible. Going back to my original record won't bring the subform back,
either, even though the checkbox is checked.

How do I fix this?

Thank you.


Here's my code:

Private Sub Answer_AfterUpdate()

If Answer = -1 And QuestionID = 99 Then
Forms!frmBrowseApplications!sfrmBrowseSubmissions.Visible = True
Else: Forms!frmBrowseApplications!sfrmBrowseSubmissions.Visible = False

End If

End Sub

 

[Daniel]

Hi,

The AfterUpdate event (of the form) occurs whenever a record is
changed. I think you probably want the "Current" event in the subform
instead, which will occur whenever you switch which record is active.

If you move to a record whose criteria is different from Answer = -1
And QuestionID = 99 then it will make the 2nd subform invisible again.

The code should probably not be in the current event of the main form -
this may be part of your problem.

HTH,
Danny

 

[Niniel]

Sorry, Danny, I wasn't quite clear - the code is of course in the checkbox's
AfterUpdate event, not the subform's.
I did experiment with putting the code in the subform's Current event, but
that didn't seem to make any difference.
The reason why I copied it to the main form's Current event is that I am
trying to make sure that the visibility of the subform is updated depending
on the the state of question 99 per main form record. In other words, the
the Answer for question 99 changes with the main table's records, and I want
to reflect that on the form by toggling visibility of my subform.

I hope this makes [more] sense.

 

[Daniel]

Hi Niniel,

That makes things more clear.

If I understand correctly, you have a textbox and a checkbox - one on
the main form, and one on the subform. Both are associated with a
field (the "Control Source" property's value is a field name).
Whenever the record is changed/updated - in either the main or the
subform, you want to toggle whether a second subform is visible if
appropriate.

To do this, you should use the current event of both the main form and
of the subform. That way, whenever the active record is
changed/updated, then it will determine whether the second subform
should be displayed.

I suspect that Access cannot find "QuestionID" and "Answer" because
they're on different sheets.

Assuming the subform's name is SF1 and the main form is MF1 then:

Try in the main form's current event:
If me.[SF1]!Answer = -1 And QuestionID = 99 Then
me.sfrmBrowseSubmissions.Visible = True
Else
me.frmBrowseSubmissions.Visible = False
End If

And in the subform's current event:
If me.answer = -1 And forms![MF1]!QuestionID = 99 Then
Forms!frmBrowseApplications!sfrmBrowseSubmissions.Visible = True
Else
Forms!frmBrowseApplications!sfrmBrowseSubmissions.Visible = False
End If

Let me know if that solves your problem or not.

Danny

Sorry, Danny, I wasn't quite clear - the code is of course in the checkbox's
AfterUpdate event, not the subform's.
I did experiment with putting the code in the subform's Current event, but
that didn't seem to make any difference.
The reason why I copied it to the main form's Current event is that I am
trying to make sure that the visibility of the subform is updated depending
on the the state of question 99 per main form record. In other words, the
the Answer for question 99 changes with the main table's records, and I want
to reflect that on the form by toggling visibility of my subform.

I hope this makes [more] sense.

Hi,

The AfterUpdate event (of the form) occurs whenever a record is
changed. I think you probably want the "Current" event in the subform
instead, which will occur whenever you switch which record is active.

If you move to a record whose criteria is different from Answer = -1
And QuestionID = 99 then it will make the 2nd subform invisible again.

The code should probably not be in the current event of the main form -
this may be part of your problem.

HTH,
Danny

 

[Niniel]

Hello Danni,

My setup is sightly different - my subforms are continuous and have the
checkbox and text field - which just displays information [explains the check
box]
Both draw their information from different tables that are linked in a
one-to-many relationship. So for every one record in the main table, there
will be full set of questions in the child table/s. As I scroll through the
main table/form's records, the information in the subforms is updated
accordingly.
Which works for the checkboxes, but not yet for the in/visible subform.

Unfortunately, there's still a bug in your idea - I'm getting an "Error
2455 - You entered an expression that has an invalid reference to the
property Form/Report" for the code in the main form.

 

[Daniel]

I think I understand now.

Because both the checkbox and text field are on the subform, then you
will need to change the statements I wrote a little bit, so that each
value is 'referenced' properly :

Try in the main form's current event:
If me.[SF1]!Answer = -1 And me.[SF1]!QuestionID = 99 Then
me.sfrmBrowseSubmissions.Visible = True
Else
me.frmBrowseSubmissions.Visible = False
End If

And in the subform's current event:
If me.Answer = -1 And me.QuestionID = 99 Then
Forms!frmBrowseApplications!sfrmBrowseSubmissions.Visible = True
Else
Forms!frmBrowseApplications!sfrmBrowseSubmissions.Visible = False
End If

Does this work?

Danny

Hello Danni,

My setup is sightly different - my subforms are continuous and have the
checkbox and text field - which just displays information [explains the check
box]
Both draw their information from different tables that are linked in a
one-to-many relationship. So for every one record in the main table, there
will be full set of questions in the child table/s. As I scroll through the
main table/form's records, the information in the subforms is updated
accordingly.
Which works for the checkboxes, but not yet for the in/visible subform.

Unfortunately, there's still a bug in your idea - I'm getting an "Error
2455 - You entered an expression that has an invalid reference to the
property Form/Report" for the code in the main form.

Hi Niniel,

That makes things more clear.

If I understand correctly, you have a textbox and a checkbox - one on
the main form, and one on the subform. Both are associated with a
field (the "Control Source" property's value is a field name).
Whenever the record is changed/updated - in either the main or the
subform, you want to toggle whether a second subform is visible if
appropriate.

To do this, you should use the current event of both the main form and
of the subform. That way, whenever the active record is
changed/updated, then it will determine whether the second subform
should be displayed.

I suspect that Access cannot find "QuestionID" and "Answer" because
they're on different sheets.

Assuming the subform's name is SF1 and the main form is MF1 then:

Try in the main form's current event:
If me.[SF1]!Answer = -1 And QuestionID = 99 Then
me.sfrmBrowseSubmissions.Visible = True
Else
me.frmBrowseSubmissions.Visible = False
End If

And in the subform's current event:
If me.answer = -1 And forms![MF1]!QuestionID = 99 Then
Forms!frmBrowseApplications!sfrmBrowseSubmissions.Visible = True
Else
Forms!frmBrowseApplications!sfrmBrowseSubmissions.Visible = False
End If

Let me know if that solves your problem or not.

Danny

 

[Niniel]

Hello Danni,

No, that did not work, I am still getting that error for the code in the
main form.

However, I found a different solution for the Current code, and I only need
it in the subform, not in the main form.
I couldn't get either method to work in both places, but together they seem
to be doing the job.
The only problem *now* is that I just noticed that with the checkbox for
question 99 checked, checking another checkbox on that same continuous
subform will hide my target subform.

Do you have a suggestion as to how I could prevent that?


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

 

[Niniel]

Problem solved.
Had to make some minor modifications, but 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