Requery separate form (2002 SP-2)

Topic

New Reply

I was fortunate enough to have Hans help me with a Me.Dirty issue yesterday (post 238190), and discovered several other applications for this code. It seems it is just good programming to consider this whenever using this kind of cmdbutton. A (similar?) issue I have discovered is a cmdbutton on a continuous form (“Refresh Display”). This is nothing more than a cmdbutton calling a requery machro. The form (frmLoadBoard) has this “Refresh” cmdbutton because if a new order is entered (frmOrderNew), it is not displayed in frmLoadBoard until it is requeried. This creates a rather precarious condition in that the operator has no way of knowing if there is a new order available for shipment unless he (randomly) clicks the “Refresh” button. I had thought that one might consider a timed event to trigger a requery, but things change so quickly in this environment that the new order update really needs to be instant. Might there be some way to have the frmOrderNew (on close event?) requery the frmLoadBoard? My hope would be to requery the frmLoadBoard when the frmOrderNew is completed (closed).

tblMaster > frmOrderNew
tblMaster > qfltLoadBoard > frmLoadBoard

Being relatively new to VBA, I become easily confused (come to think of it, that condition is not limited to VBA). As always, any assistance greatly appreciated.

Edit: I don’t know that it’s relevent, but the frmOrderNew is called from a cmdbutton on the frmLoadBoard as well as the switchboard.

Reply | Quote

Replies

Is this going to be used by a single operator, or by several?

If you have a single operator, then requerying frmLoadBoard in the OnClose event of frmOrderNew would be OK:

Private Sub Form_Close()
Forms!frmLoadBoard.Requery
End Sub

But in a multi-user environment, doing the above won’t refresh the form when one of the others adds an order. I would NOT recommend using a timed requery – it would be unnerving if the requery occurred while the operator was editing a record. But you could use a timer to display a notify the operator of new orders having been added, for instance by having a label display “37 new orders” or something like that. The user would still have to hit the Refresh button.

Reply | Quote

Thanks, once more, Hans for your reply. It is currently a single user environment, but (yikes) occationally has a peer-to-peer involvement. The plan is to move to move to a server in the near future (6 months or so). If I use the OnClose event for the time being, will that create issues in the multi-user environment later on? Should I just go with the “new orders” display right now, and how might I go about creating that timed event if so?

Reply | Quote

I would use the OnClose event anyway, now and in the future – you want frmLoadBoard to be updated when the operator creates a new order.

Until you move to a multi-user environment, there is no need to use the timer event, although it won’t hurt either – you might work on it as an exercise.

You will need to think about what you want to display: the total number of orders, or the number of orders that has not yet been shipped, or the number of orders that has been added in the last hour, or whatever. It must be something that you can calculate in a query or expression.

Put a label on frmLoadBoard; its caption can be anything (we’re going to change it in code). Let’s say we name it lblOrderInfo.
In the Properties for the form as a whole, activate the Events tab. Scroll down until you see On Timer and Timer Interval.
Set Timer Interval to the desired update interval in milliseconds. If you want the label to be updated every 8 seconds, enter 8000.
Click in the On Timer event, select Event Procedure in the dropdown list, then click the Builder button (the three dots).

You’ll be taken to the Visual Basic Editor, with the following code already created for you:

Private Sub Form_Timer()

End Sub

This procedure will be called every (Timer Interval) milliseconds as long as the form is open. We will set the caption of the label in this procedure. The exact contents depend on what you want, as described above. Just for illustration purposes, let’s assume that you have a table tblOrders with fields OrderID, OrderDate, … You want to display the number of orders that has been added today. The code could look like this:

Private Sub Form_Timer()
  lblOrderInfo.Caption = "The number of orders added today is " & _
    DCount("*", "tblOrders", "OrderDate = Date()")
End Sub

I stress again that this is just an illustration. *You* will have to think about, and decide, what is to be displayed.

Reply | Quote

I thought I might try the OnClose event for now, but encountered a problem. The code works just fine if the frmOrderNew is called from the cmdOrderNew, but I get a Run Time Error 2450 if frmOrderNew is called from the switchboard. If frmLoadBoard is already open it’s not a problem, but if it has not been opened (and you select frmOrderNew from the switchboard) you get the Run Time error. Is it that the OnClose event expects to find frmLoadBoard open?

Reply | Quote

Forms!frmLoadBoard.Requery will cause an error if that form is not open. There are two ways to remedy this:

• Insert an instruction On Error Resume Next before the requery statement. This prevents an error from being relayed to the user. It is efficient, but many programmers consider it to be a copout.
• Test if the form is open. Place the following utilitiy function in a general module:

  Function IsLoaded(ByVal strFormName As String) As Boolean
   ' Returns True if the specified form is open in Form view or Datasheet view.
    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) Then
      IsLoaded = Forms(strFormName).CurrentView
    End If
  End Function

  Then, in the OnClose event handler, use

  If IsLoaded("frmLoadBoard") Then
    Forms!frmLoadBoard.Requery
  End If

  [/list]See which one you like best.

Reply | Quote

Whew! (I think I need to change my shirt!). What an experience. One easily discovers his typing skills when entering code. I managed to get everything done though, and it all works really great (I also completed the IsLoaded coding)

Private Sub Form_Timer()
lblOrderInfo.Caption = “Recent new orders added ” & DCount(“*”,”tblMaster”,”OrderStatus=’NEW'”)
End Sub

(BTW, my code is all on one line. I tried using “_” (no quotes) to get a carriage return so the code doesn’t go off screen, but no luck)
You may also note that I added a “NEW” status for the frmOrderNew comboBox because an order could have an “OPEN” status without being new. The display in the header, I fear, will become “passive” over time though. If this were a txtBox I could add some Conditional Formatting to make it invisible if Null, with bold red font if not Null. I don’t think that can be done with a lable though. My point being that the operator would notice the change and click the refresh button in the multi-user enviroment, whereas that is not currently required with the OnClose event.

This has been a GREAT learning experience. Thanks so much for your help and “training”; I shall not forget my debt to the forum if I ever actually get to the point of knowing something.

Reply | Quote

Hello Bryan,

Congratulations on getting it to work. Two remarks:

1. You are correct, a label doesn’t have conditional formatting, but you can set the formatting in the Form_Timer procedure: FontName, FontSize, FontBold, ForeColor, BackColor, BorderStyle, etc. You could change it each time, or only if it has changed, or if the number of new orders is large… it’s up to you. In fact, you can do more this way than with conditional formatting.

2. To “break” a line of code, you must type a space after a word (this is essential), then an underscore _ and finally, press the Enter key. Pressing Tab to indent the new line is optional.

For example

MsgBox _
  "Hello World"

is valid, but

MsgBox_
  "Wake up and smell the coffee"

is not valid, because there is no space between MsgBox and _.

Reply | Quote

You’ve already done so much for me (where do I send the check?)…

1. I’ve done some pretty bizzare things to get around the 3 variables in Conditional Formatting (text with” ‘ “,” . “, and “,”). I always wondered if there was a better way. What I want to do is have an IIF statement where, if count is 0, then BackColor is white and ForeColor is red. In that way I could have the default properties set to grey/grey (color of header background) so you wouildn’t even see it unless the value was more than 0. If it were more than 0, the text would diplay in red with a white background. I do have ‘Beginning Access 97 VBA’, but haven’t found a reference to this type of issue. Might you direct me (at your leisure of course).

2. I’m afraid I fall into that “rookie” condition where I think computers can think. Miss a “space” and everything goes to hell. Thanks for the correction.

Thanks professor,
Your Humble Student

Reply | Quote

Something like this? Set the label up for white background and red text (I’ll explain after the code)

Private Sub Form_Timer()
  Dim lngCount As Long
  lngCount = DCount("*","tblMaster","OrderStatus='NEW'")
  If lngCount > 0 Then
    lblOrderInfo.Caption = "Recent new orders added " & lngCount
    lblOrderInfo.BackStyle = 1 ' Normal
  Else
    lblOrderInfo.Caption = ""
    lblOrderInfo.BackStyle = 0 ' Transparent
  End If
End Sub

How this works: the label is formatted with a white background and red fore (=font) color. If the count of new orders is 0, we set the caption of the label to an empty string, so no text is visible, and the background style to transparent, so that the section color will show through. If you wish, you can also set the BorderStyle to 1 (a normal line) or 0 (transparent) according to the count of new orders. I will leave the exact coding to you as an exercise . As they say, it’s straightforward and left to the reader, right?

Reply | Quote

Wow. This is fantastic Hans! (I removed the OnClose event from frmOrderNew to view the results). Absolutely impressive (dare I say?) “programming”. It is unavoidable for the operator to miss the need to refresh the display to get the new order(s) – you’d have to be blind. A nice aside to the result is that the display remains until the status is changed to something other than “NEW”. But, once the status is changed, the display reflects the change until all orders have been properly handled – nearly impossible to overlook the need to address the new order(s). Once ALL new orders have been properly addressed, the display vanishes. This application is actually taking on a “professional” feel.

I actually didn’t need to amend your code, it worked just fine as posted; so there wasn’t much of an “exercise” involved (LOL). However, I do have the comboOrderStatus formatted with Conditional Formatting and there are 12 different status’ with only three different ways to display them. In looking at your code I will attempt (a little knowledge can be dangerous) to create twelve different ways of formatting them. So, for my own reward (and in honor of your generous contributions) I will pursue this issue further in trying to figure out the code, and even where to put it.

Most Gratefully,
Grasshopper

Reply | Quote