On Load, form not visible

[Bruce]

I'm using an OnLoad event to trigger my VB code that acts on the form data. The problem is the form is not visible as the code executes and it needs to be. Here's the code:

If (WorkOrderType = "Renewal") Then


If MsgBox("I found this WorkOrder is still OPEN, Shall I close it for you?", vbQuestion + vbYesNo, "WorkOrder Association Query") = vbYes Then

Me.CloseDate = Date - 1

ElseIf (WorkOrderType = "Deactivation") Then
ElseIf MsgBox("I found this OPEN WorkOrder, Shall I close it for you?", vbQuestion + vbYesNo, "WorkOrder Association Query") = vbYes Then

Me.CloseDate = Date

Else
End If
End If

I tried to add the code to other events (OnGotFocus, OnDirty, etc..but it didn't work in those fields. I also tried a Me.Form visible = True but errored out to illegal use. Any ideas?

[RuralGuy]

If you comment out all of that code, is the form then visible?

[Bruce]

Yes, the form is visible if I remove the code.

[RuralGuy]

Indented, this is what your code looks like:

   If (WorkOrderType = "Renewal") Then
      If MsgBox("I found this WorkOrder is still OPEN, Shall I close it for you?", vbQuestion + vbYesNo, "WorkOrder Association Query") = vbYes Then
         Me.CloseDate = Date - 1
      ElseIf (WorkOrderType = "Deactivation") Then
      ElseIf MsgBox("I found this OPEN WorkOrder, Shall I close it for you?", vbQuestion + vbYesNo, "WorkOrder Association Query") = vbYes Then
         Me.CloseDate = Date
      Else
      End If
   End If

I doubt that is what you want, is it?

[Bruce]

Thanks, that produces the same result. The form is still not visible but after answering No to both MsgBox questions, the form shows up. I should also mention, If I remove the MSgBox Yes/No questions, the code performs the functions properly but I never get to see the form and confirm the changes. Also, with the form not visible, the If criteria are not being scrutinized. i.e. the user is prompted for both questions. So, first problem...form not visible, second problem "If" criteria not being properly scrutinized. I assume if we fix problem #1 then #2 may go away.

You've been a great help to me RG. Thanks!

[RuralGuy]

MsgBoxes are Dialog forms and stop all other events until they are answered. In other words the form stops loading until you answer the question. Not what you want. What is WorkOrderType and how many different values can it contain? Is it a field in the RecordSource of the form? Is it also the name of a control on your form?

[Bruce]

Thanks! WorkOrderType is a form/field that contains data returned from a querry. It will contain 2 possible values, Activation or Renewal (my original post indicated Deactivation which was incorrect). It's control source links it to the table from where the querry pulled it.

Hope this helps for a few, I found another problem I need to fix. Thanks for making me think about the flow. Regardless, I still need the form to show before the questions are asked.....Ha...imagine that?

[RuralGuy]

I'll let you struggle with the code for a bit but try using the Current Event instead and see if that works for you.

[Bruce]

Found a work around. I did try the OnCurrent and about every other event I could select (lots of em) but none of them worked. SOoooo. I added a Command button <Close This WorkOrder?>

Private Sub Command22_Click()
If Forms!ADD_WorkOrders!WorkOrderType.Text = "Renewal" Then
Me.CloseDate = Date - 1
Me.Refresh

Else
Me.CloseDate = Date
Me.Refresh

End If
End Sub

Thanks RG!

[RuralGuy]

The .Text property is only available when the control has the focus and you don't need the Refresh. Set a public flag in the OnLoad event and in the OnCurrent Event only execute your code if the flag is set and then reset the flag. No button or manual intervention required.

[RuralGuy]

Your code will look something like:

Private Sub Form_Current()
   If Loading Then
      If Me.WorkOrderType = "Renewal" Then
         Me.CloseDate = Date - 1
      Else
         Me.CloseDate = Date
      End If
      Loading = False
   End If
End Sub

I assume this only applies when the form is first loaded.

[pbaldy]

Just to throw out an alternative on the "invisible form" issue; one workaround is to use the timer event of the form. Set the timer to a fraction of a second, and in your code set the timer interval to zero, which will stop it from running again. The code won't run until the form is visible.

[RuralGuy]

Another good idea heard from.

[Bruce]

All excellent ideas for a unusual problem. I consulted with the users and they still prefer the button instead of the MsgBox confirmation and code we have all been so busy brainstorming. I set up the button to give the user more visual control and also set a validation rule to make sure they close out the workorder before exiting the form. e.g. the Form Close button checks to make sure the Workorder status = closed or a MSgBox (Okonly) forces them to close by clicking the "Close WorkOrder" button.

Several ways to skin a cat.....Meoowwwwwch!

Thanks!
Bruce

[pbaldy]

LOL at "Meoowwwwwch!"