Subform refresh hangs

[drjohn61]

Hi All:

I have a form that has a data source set to a simple query off of a table. The form has a subform that has a child/parent relationship (date filed). The subform has conditional formatting set for all of the fields. Moving through the records with a simple DoCmd.GoToRecord , , acNext (or previous) works fine. The subform updates correctly and the conditional formatting works fine.

I also have an unbound textbox that uses a datepicker to populate it. The after update event is set to either filter the form or find the next record with the date entered (I have tried both--obviously not at the same time). The form is either filtered correctly or finds the correct record, but it seems to hang and not apply the conditional formatting until I click on the form tab or a control on the form.

It is baffling to me why it works flawlessly when moving to the next/previous record, but not when trying to navigate to a specific record. DOes anyone have a solution for this? I am using Access 2007.

Thanks,
DrJohn61

 

[John Big Booty]

As part of your filter or Find next procedure put try doing a Me.Repaint this should trigger the Conditional Formatting.

 

[drjohn61]

Thanks!

I will try it again when I get back tomorrow (it is at work). I tried refresh, requery, repaint... nothing worked. I also tried various setfocus scenarios. The other weird thing is that certain fields on the main form don't update either (until I click on something) and the status bar says it is "calculating" (what I don't know). As soon as I click on something, everything updates and works fine. Honestly, I did not do a lot with repaint, so I will see if that makes a difference. I am also going to try a subform without conditional formatting to see if that makes a diff--although, I still don't get why it workd when just cycling through the records...

 

[John Big Booty]

Given that you are working with a Form/Sub Form set you will need to get the reference correct, dependant on which form you are firing your code from. This link should help you get the correct reference.

 

[drjohn61]

Nice resource thanks!

I will keep that one handy. I am not sure how that helps here though. I don't ever leave the main form. I guess maybe I did not get all of the info down. Sorry!

The main form is linked (datasource) to a query off of table one. The subform is linked to a query off of table two. The parent/child relationship is off of a datefield. So when the date is set on the main form, the subform is "filtered" to the same date (I know it is not really filtered).

When I cycle through the dates on the main form (just going to the next/previous record), the subform works great; conditional formatting and all.

When I try to navigate to a specific record (date) on the main form (or filter it on the date), the subform sort of hangs (the calculating issue). Not all controls update on the main form and, while the subform is "filtered", the conditional formatting does not fire--Until I click on something. Then all is great.

I thought maybe opening a different form before the update and closing after, changing focus to the form, subform, various controls, or refreshing, requerying (repainting--only tried this once) would help. The only thing that fixes it (that I can find) is to physically click on the main form.

 

[John Big Booty]

If you are driving things from the Main Form, and you want to Repaint the Sub Form, that's where you will need the correct reference, to the Sub Form.

 

[drjohn61]

OH..... duh!

Thanks!

I will give it a shot tomorrow.

 

[drjohn61]

OK, an update on this.

I tried all sorts of variations and combinations of repaint, setfocus and refresh. nothing worked. There is no problem if the form does not have conditional formatting, so it has to be an issue with that--still not sure why only if filtering or finding from the main form. It also works fine if the form is opened filtered...

I guess I am going to try deleting the conditional programming on the subform before doing the findfirst, going to the desired record, reprogramming the conditions, and then repaint the subform. I have not done it in VBA before, but it does not look hard--IF it works... I am going to try just one field first.

...unless someone has a better idea...

Thanks,
John

 

[vbaInet]

The after update event is set to either filter the form or find the next record with the date entered (I have tried both--obviously not at the same time).

How are you doing this? Let's see your code.

 

[drjohn61]

My exact code is at work, but this is pretty much it (the find record version, which is my preferred method):

Sub txtScheduleDate_AfterUpdate ()
Dim rs As DAO.Recordset

If txtScheduleDate & "" <> "" Then
Set rs = Me.RecordsetClone
rs.FindFirst "[CalDate] = #" & Me.txtScheduleDate & "#"
If rs.NoMatch Then
MsgBox "Date not found."
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing

'I have tried different repaint, setfocus, and refresh combos here

End If
End Sub

I have also tried it without the recordsetclone, which works delivers the same result.

 

[vbaInet]

So the search works? By the way, instead of txtScheduleDate & "" <> "" use the IsDate() function.

I'm guessing your field names are different from the control names? That is, you are using some sort of prefix or suffix to differentiate them?

When it hangs does the Status Bar below (to the left) read Calculating...?

Can you give me an example of one your Conditional Formatting statements.

 

[drjohn61]

Thanks, I will use the IsDate()!

Yes, the field and control names are different. and, yes, it shows that it is calculating when it hangs--until I click on something; then it is fine.

I don't have the exact conditional formatting here, but it is an expression and something like:

[Status] like "In*" OR [Status] like "W@H*"

([Status] being the control)

I have three conditions for each field, but tried it with a very simple expression on only one field and it still hangs. It does not hang without the conditions.

 

[vbaInet]

Alright, it's causing a Circular Reference causing it to be in an endless loop. Plus your expression is incorrect.

What should this actual mean -->
[Status] like "In*" OR [Status] like "W@H*"
?

Did you use Field Value Is or Expression Is in your CF?

 

[drjohn61]

I used expression is.

[status] is a control that has a text value in it. It actually holds a person's attendance status for the day (In, Out, etc.) If the person is out, it is one color, on travel a different and so on.

The condition works fine if I just load the form or move through the records using DoCmd.GoToRecord , "", acNext (or acPrevious). It only hangs if I use the findfirst (or filter the form).

 

[vbaInet]

Sorry, when I saw IN I was thinking of the IN() operator in SQL.

Right, instead of using the field name use the control name, i.e. txtStatus in place of Status. Re-written:

Left([txtStatus], 2) = "In" OR [txtStatus] LIKE "W@H*"

 

[drjohn61]

Thanks. While we were discussing this, I actually pulled out my work laptop. I tried this, but it still hangs.

I think I know of some other workarounds that will complicate the whole form, but this is FRUSTRATING. It should work!

 

[vbaInet]

Perhaps you're trying to conditionally format a calculated control too. Read this:

http://allenbrowne.com/bug-05.html

Upload a test db if you can.

 

[drjohn61]

no, it is not a calculated control. It is pulled straight from the table.

I will see if I can pull out the forms and a sanitized table to upload (sensitive info and a HUGE DB ). It will likely be tomorrow.

 

[vbaInet]

I believe you mentioned having CF on more than one field, so if any of then are, they will be affected.

Let us know when you strip off the irrelevant bits.

 

[drjohn61]

OK, I cleaned up a test version. There are two tables in this DB. The Calendar Extended actually exists in the real one. The ScheduleDate one actually is a query generated from several other tables and queries. I made a table for simplicity sake. It did not change the outcome; I checked.

Use the "PDD Schedule" form. The arrow buttons scroll through the dates fine. In this version, you see the Status on the subform turn yellow on Saturday or Sunday, since these are non-workdays (NWD).

If you click in the box between the arrows, pick a date and click enter (something I also would like to change--maybe use the "On Change" event after I get this working?), you will see the problem...

Thanks!