Access crashing when using bookmark,requery, and conditional formatting

[JGrots]

I have a subform ([TrainingQry3 subform]) that on focus inputs the value into an invisible text box. Then I have this text box linked using master/child to another subform ([TrainingTopics subform]) to filter it. Inside this subform I have a Yes/No box that indicates if I want this topic to not apply to this particular employee. If I check the box Yes, indicating that I don't want this employee to be trained on this topic then I have a conditional format that will gray on the row. I wanted to also have the subform requery so that all the grayed out rows are on the bottom. Also, I have a vba code that requeries the subform and brings me back to the saw record using the bookmark function.

The problem I'm having is that when I click Yes and the subform requeries and the row becomes grayed out, Access crashes.

Does anyone know why this is?

Here is the code with the Bookmark function:

Private Sub NATopic_AfterUpdate()
strNABookmark = Forms![Employees Form].[TrainingQry3 Subform].Form.Bookmark
If Me.Dirty Then Me.Dirty = False
Forms![Employees Form].[TrainingQry3 Subform].Form.Requery


Forms![Employees Form].[TrainingQry3 Subform].Form.Bookmark = strNABookmark
Forms![Employees Form].[TrainingQry3 Subform].Form.Category.SetFocus
Forms![Employees Form].[TrainingQry3 Subform2].Form.Requery

End Sub

[June7]

Have you step debugged?

Clicking Yes generates error "Microsoft Access can't find the field '|1' referred to in your expression. Debugger goes to line

Forms![Employees Form].[TrainingQry3 Subform2].Form.Requery

I comment the line and code works fine.

The NATopic AfterUpdate event runs before NATopic Click event. Try BeforeUpdate event instead of Click.

Variable strNABookmark does not get set.

[JGrots]

Hi June7,

Thank you for responding.

The reason why that error came up was because I took out the form that the code was reading. I have added the form back in. I realized that the database I attached isn't a true representation of what I want. So I have made some changes and attached an updated version. Training Database (3.17.14) - Test.zip

I tried making the click event a beforeupdate event but I kept getting errors. Can you elaborate more on how and why I should make the click event a beforeupdate event and how I make sure that the msgbox prompt still comes up?

What do you mean by "Variable strNABookmark does not get set" ?

Thanks for your help!

[June7]

Never mind about the BeforeUpdate suggestion.

I meant when I step debugged and hovered cursor over strNABookmark, it had no value, it was empty. Now I can't find NATopic control on any form in this version.

Not getting errors anymore. Clicking the checkbox works.

[JGrots]

The NATopic control has a caption name of "Not Applicable" and is in the TrainingTopics subform on the tab "Training Records"

[June7]

Okay, found it. That's the checkbox. It is working for me.

strNABookmark still shows empty

I commented all the code in NATopic_AfterUpdate and get the same results.

[JGrots]

I can't even click on a field in the TrainingTopics Subform without this happening.

[June7]

Very odd. Can't replicate the error and I am running that mdb in 2010.

[JGrots]

I just deleted all the conditional formatting and the error goes away and everything works great. I don't understand why the conditional formatting is causing me problems.

[June7]

Even odder. Does the subform fail with conditional formatting if opened independently from the navigation pane?

[JGrots]

I works fine when its independent from the navigation pane. I think it has to do with the requering of the queries and then going back to the bookmark location. It seems like Access can't handle it.

[June7]

Except that it is no longer erroring when I run the code.

Not sure you really need to requery the form. The records are not filtered out of the RecordSource, just the field value is changed. As I said, get same behavior with or without that code.

Did you try the conditional formatting without the requery code?

[JGrots]

I commented out the NATopic_AfterUpdate and the DateTrained_AfterUpdate codes and it still crashes with the conditional formatting.

[June7]

I would normally suggest corruption is the villain except that I don't get this issue. Since I can't replicate, can't offer solution.

[JGrots]

Thanks for the help anyways June7!