Replies

Whoa! Hold everything Hans. It works!! I just have to requery the frmLoadBoardTenDay every time I change the status in frmOrderDetail.

Hope you don’t go blind reading this!

Geeeesh!! Talk about “above and beyond the call of duty” – you deserve the Medal of Honor for sure. Thanks so much for all that work!!

I think we’re very close at this point, but the OLE is displaying only one color (Green) regardless of status. In step #18 “Assign a ColorIndex to each status”; tlkpOrderStatus has three columns: Status (Assigned, Billed, etc.), StatusKey (1 – 15), and ColorIndex (Red, Yellow, etc). I’m not sure what you meant by assign a color index, but I just randomly clicked on a color for each status.

Close…very close.

I think I may have hurt myself. I don’t think the brain cells are functioning anymore. Couldn’t sleep, so I’m back at it. I can’t seem to make the connection between the demo (thanks Steve, re:Hans) and my application. I’m not sure I can write this out, but I’m gonna do my best.

tblMaster > qryMaster > qfltLoadBoardTenDay > frmLoadBoardTenDay > txtOrderStatus (from tblMaster Lookup re: tlkpOrderStatus)

frmLoadBoard is really for display purposes only (to get the “big picture” of all orders per qfltLoadBoardTenDay) dblclk any order and you get frmOrderDetail to make any edits or change in status (ComboOrderStatus). Because there are no edits done in frmLoadBoardTenDay, and even if the data comes from a ComboBox, it is displayed as a TxtBox (it also makes for a cleaner display in continuous forms).

I edited tblMaster to include the fields ColorKey (number) and ColorIndex (number). I don’t really understand why (how bad is that?), but those were the fields in tblData from the sample db (tblData also has Data1 and Data2), but seeing as I needed the ComboOrderStatus field from tblMaster I did not add the additional fields from the sample db.

I then created tblColors with the fields Value (number) and Color (OLE). Again, I did not include the field “Description” from the sample db because the value I need the txtOrderStatus to display is the value of ComboOrderStatus. I then went to qfltLoadBoardTenDay and added tblColors with a link between ColorIndex (qryMaster) and Value (tblColors). Clicking RUN returns no records. I haven’t even gotten to the form yet.

Hans,

Lost, dazed, and confused. I have printed out all the threads and downloaded the sample db. Trying (desperately) to understand, but keep getting distracted by work. Just wanted you to know that I am trying to work this out, but it will probably take all of this evening to come up with something deserving of a reply. Nose to the grindstone!

Thank you Hans – I am researching your references.

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

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

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.

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?

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?

Thanks Hans, it works perfectly!

The reason for the seperate form is that the memo is text intensive. Delivery exceptions track each and every phone call (and the jest of the conversation) associated with contacts with customers, drivers, and staff over a period of what may amount to several days. This often results in numerous lines of text and displaying this info in its entirety (or at least a major portion of it) requires the whole screen. I do, however, follow your logic in doing it on the same form.

Thanks Again!!

Duh…thanks. I didn’t say I was a Newbie for nothing!

Thanks, once again, Hans!!

1. The internal Compact and Repair failed as previously attempted.
2. Jet Comp (thanks) DID compact, but did not repair (same error with internal Compact and Repair after re-opening original db)
3. Created new db without MSys objects diplayed and imported objects from old db. Internal (Access) Compact and Repair now working!!

Thanks Again!

BTW (and for anyone watching this post) on the frmOrderEntry I left the text box containing the FSC as editable, but removed the tab stop. This allows for changing the FSC (for whatever exception that may arise) but requires a deliberate effort on the part of the operator to do so.