Replies

Thanks! Short, clear, and to the point. I suppose the next question would be a philosophical one; like why would MS offer the option of converting macros to modules if it accomplished nothing more than revealing the code; but, I’m not going to go there, not ever. I think re-writing the code would be a worthwhile exercise for me, and perhaps even an improvement in the efficiency of the application. As per the feedback, I see that macros may be minimally effective, yet far from efficient. Armed with that awareness, I’m off to practice code. Hope I don’t do too much damage.

Charlotte,

I’m thinking perhaps I didn’t structure my question correctly.

Right click Macro, save as module. What, if anything, does that accomplish?

Pat,

Thanks for your reply. I do understand the benefit of error handling in code. I just don’t get what converting macros to modules accomplishes.

O.K.

So, why convert to modules? The module looks no different to me than the macro (except that I can see the code). Is Access running the module, or the macro after conversion?

Mark,

Yeah. Got it! (see rest of string).

Thanks for the code!!! (and replies)

Hans,

Oh yeah. I like that, I like that a lot! Public Const; who’d have ever thought?
That deserves a

Hans,

Beginning to feel like a nanny yet?

Thanks for the reference (I was looking up “vb color”). I think I follow the logic of using ColorConstants. What I was interested in doing was finding out the numeric equivalent for the vb name of those colors you see in the “Fill/Back Color” button on the toolbar. I assumed (uh-oh) that these were SystemColorConstants, but while there may be vbGreen, there is no vbPaleGreen (Light, Dark, etc). What I have done is select a color from the toolbar and then looked to see what the numeric value is. I see the point as far as reading the code goes, so I added a description.

Public Function WhenGotFocus(ctl As Control)
ctl.BackColor = 13434828 ‘PaleGreen’
End Function

Thanks again!

(BTW, how do you post code in the format it was written? Indents, font, etc)

Because vbGreen is the most ungodly looking thing. Is there a list of vb colors somewhere? Can’t find anything in help that tells you what colors are available (ie vbLightGreen?). Thanks again for your assist.

Gosh, this is humiliating.

Bit of a typo issue! All better now. BTW, can one use a color “code” (ie 255 instead of vbRed)? I would like to enter:

ctl.BackColor =52377

Does the number require special handling (brackets, etc)? Thanks for all your patience!!

I suppose what I’m missing here (aside from the brain cell count) is that “ctl” would be the name of the control? Is that the name of each and every particular text-box, msg-box, combo-box; or, is there a generic name for all text-boxes, combo-boxes, etc?

Charlotte,

1.”You aren’t returning the form’s backcolor, you’re making the rectange with a different backcolor visible by setting the control’s backstyle to transparent” What “rectangle” would that be?

2. I copied/pasted the code.

Important: I’m not the sharpest tack in the box.

Mark,

Is that precisely the code to be entered? I still get a runtime error. I like Hans’ simple solution, but in my case I don’t want to return the Form BackColor. Might you re-post the complete (edited) code?

Hans,

Funny how one one revision seems to lead to another, and another,and… I am very pleased with the results of this most resent journey, but most grateful for the lessons learned along the way. While VBA results are certainly the immediate target, the learning process has the greatest long-term benefit. I became most frustrated when I tried to get Access to “think” the way I do, but really started getting somewhere when I attempted to think the way Access operates. Long way to go, but at least I’m on the way.

(Forms!frmLoadBoardTenDay versus Me.Refresh…Duh! Like I said, long way to go)

This is a bit off the original subject, but (after a nap) I began to see the “refresh” issue a bit differently and turned a mountain into a mole hill. I went back to the Timer Event for frmLoadBoardTenDay and simply ammended the code. Now the new “conditional” color display refresh’s every 60 seconds, regardless if a new order has been entered.

Private Sub Form_Timer()
Dim lngCount As Long
lngCount = DCount(“*”, “tblMaster”, “OrderStatus=’NEW'”)
If lngCount > 0 Then
Searching.Caption = “New order(s) found. Click ‘refresh’ to display: ” & lngCount
Searching.BackStyle = 1 ‘ Normal
Else
Searching.Caption = ” ”
Searching.BackStyle = 0 ‘ Transparent
End If
Forms!frmLoadBoardTenDay.Requery
End Sub

A WORD OF CAUTION: This is a single user application. I have been advised against Timer event ReQueries in multi-user environments.

Thanks again to Hans and Steve for all the code, examples, and most importantly, the learning experience. I am humbled by your generousity and hope I can (some day) return the favor to other members.

I was able to figure out a way around hitting cmdRefresh every time an edit is made on frmOrderDetail. I used the On Close event in frmOrderDetail to run (don’ be laughing now) a machro that closes and reopens frmLoadBoardTenDay. OK, so it’s a little bit funky with the screen flash and all, but I just couldn’t come up with anything else. Requery seems to be more for controls (or current focus forms). If you have some more “magic” in your hat for an issue like this, by all means give me a shout (that’s an open invitation to all BTW).