depends which event you are calling the code from - try the report open event. Also I assume your function is in a general module?Nothing is occuring with the reports.
depends which event you are calling the code from - try the report open event. Also I assume your function is in a general module?Nothing is occuring with the reports.
on a general point and not to digress too far, functions can be called from anywhere including sql queries and as an event, Subs cannot. Functions can return a value or not as the case may be. I will often return a boolean which indicates the function has performed as expected e.g.
and myfunction might be something likeCode:if myfunction(1,2) then 'myfunction has worked OK 'carry on processing else 'make an orderly exit end if
I also would not include Call and the brackets - they relate to ancient times for backwards compatibility and call is required for subs with more than one parameter and brackets are included. Just makes for more confusionCode:function myfunction(i as integer, j as integer) as boolean on error goto errctrl 'do something here myfunction=true exit function errctrl: msgbox err.number & " " & err.description myfunction=false end function
@PSSMargaret - to explain 'as an event' in form parameters, events tab, where you see [Event Procedure] you could simply type
=subBoxes([Form],[categoryID]+1))
in the form load or current event - not tested and not sure of your logic as to how categoryID is actually determined. But another simple example
in a general module create a function
Code:Function fName(frm as form) msgbox "you have opened " & frm.name End Function
and in any form open event where you would see [Event Procedure] put
=fName([Form])
then open the form and the function will run
Why would you do this? one reason is to make the form module smaller or not even required - which means the form will load faster. Another is when developing, you don't need to open the module to see the code. It is not a panacea for everything - for example you cannot use it where the event has parameters such as mousemove or double click events. But has it's uses
only by excluding them for reports - perhaps something likeIs there a way to get the locked field codes to work with this?
if typename(obj) like "Form_*" then .WithinToll.Locked = True 'otherwise do nothing
or disable the errors
not sure which error you are getting bus assuming it is 12345
Code:function..... on error goto errctrlCode:with obj 'your code here end with exit function errctrl: select case err Case 12345 'locked property does not exist resume next Case Else msgbox err.number & " " & err.description end select end function
Thank Ajax for your suggestions above. I will read through and give them a try. I was very busy and had to move on so what I did was create two modules, one with the field shading code and the other with the fielding locking code. Called the field shading and field locking functions for the forms and called the field shading function only for the reports. There is still only one place to make changes for either shading or locking and both seem to be working well. I have to test further. Posting the code for other that come across this thread and will investigate Ajax's suggestions further. Thanks again.
Code:Function FieldShading (meObj As Object, arg As Long) With meObj Select Case arg Case 1 .ProblemState.BackColor = RGB(217, 217, 217) .LocalPriority.BackColor = RGB(217, 217, 217) .HwyIntLocalMatch.BackColor = RGB(217, 217, 217) .HwyIntLocalMatchPoints.ForeColor = RGB(217, 217, 217) .HwyIntLocalMatchPoints.BackColor = RGB(217, 217, 217) .CriticalOpp.BackColor = RGB(217, 217, 217) .CriticalOppPoints.ForeColor = RGB(217, 217, 217) .CriticalOppPoints.BackColor = RGB(217, 217, 217) .ProjectReadiness.BackColor = RGB(217, 217, 217) .ProjectReadinessPoints.ForeColor = RGB(217, 217, 217) .ProjectReadinessPoints.BackColor = RGB(217, 217, 217) .WithinToll.BackColor = RGB(217, 217, 217) .FederalAid.BackColor = RGB(217, 217, 217) Case 2 .ProblemState.BackColor = RGB(255, 255, 255) .LocalPriority.BackColor = RGB(255, 255, 255) .HwyIntLocalMatch.BackColor = RGB(255, 255, 255) .HwyIntLocalMatchPoints.ForeColor = RGB(0, 0, 0) .HwyIntLocalMatchPoints.BackColor = RGB(255, 255, 255) .CriticalOpp.BackColor = RGB(255, 255, 255) .CriticalOppPoints.ForeColor = RGB(0, 0, 0) .CriticalOppPoints.BackColor = RGB(255, 255, 255) .ProjectReadiness.BackColor = RGB(255, 255, 255) .ProjectReadinessPoints.ForeColor = RGB(0, 0, 0) .ProjectReadinessPoints.BackColor = RGB(255, 255, 255) .WithinToll.BackColor = RGB(255, 255, 255) .FederalAid.BackColor = RGB(255, 255, 255) Case 3 .ProblemState.BackColor = RGB(217, 217, 217) .LocalPriority.BackColor = RGB(255, 255, 255) .HwyIntLocalMatch.BackColor = RGB(217, 217, 217) .HwyIntLocalMatchPoints.ForeColor = RGB(217, 217, 217) .HwyIntLocalMatchPoints.BackColor = RGB(217, 217, 217) .CriticalOpp.BackColor = RGB(255, 255, 255) .CriticalOppPoints.ForeColor = RGB(0, 0, 0) .CriticalOppPoints.BackColor = RGB(255, 255, 255) .ProjectReadiness.BackColor = RGB(255, 255, 255) .ProjectReadinessPoints.ForeColor = RGB(0, 0, 0) .ProjectReadinessPoints.BackColor = RGB(255, 255, 255) .WithinToll.BackColor = RGB(217, 217, 217) .FederalAid.BackColor = RGB(217, 217, 217) End Select End With End Function
Code:Function FieldLocks (meObj As Object, arg As Long) With meObj Select Case arg Case 1 .ProblemState.Locked = True .LocalPriority.Locked = True .HwyIntLocalMatch.Locked = True .CriticalOpp.Locked = True .ProjectReadiness.Locked = True .WithinToll.Locked = True .FederalAid.Locked = True Case 2 .ProblemState.Locked = False .LocalPriority.Locked = False .HwyIntLocalMatch.Locked = False .CriticalOpp.Locked = False .ProjectReadiness.Locked = False .WithinToll.Locked = False .FederalAid.Locked = False Case 3 .ProblemState.Locked = True .LocalPriority.Locked = False .HwyIntLocalMatch.Locked = True .CriticalOpp.Locked = False .ProjectReadiness.Locked = False .WithinToll.Locked = True .FederalAid.Locked = True End Select End With End Function
Code:Public Sub Form_Current() If Forms!frmprojectdetailsdataentry!CategoryID <> 1 Or Forms!frmprojectdetailsdataentry!CategoryID <> 2 Or IsNull(Forms!frmprojectdetailsdataentry!CategoryID) Then Call FieldShading(Me, 1) Call FieldLocks(Me, 1) End If If Forms!frmprojectdetailsdataentry!CategoryID = 1 Then Call FieldShading(Me, 2) Call FieldLocks(Me, 2) End If If Forms!frmprojectdetailsdataentry!CategoryID = 2 Then Call FieldShading(Me, 3) Call FieldLocks(Me, 3) End If End Sub
Code:Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) If Me.CategoryID <> 1 Or Me.CategoryID <> 2 Or IsNull(Me.CategoryID) Then Call FieldShading(Me, 1) End If If Me.CategoryID = 1 Then Call FieldShading(Me, 2) End If If Me.CategoryID = 2 Then Call FieldShading(Me, 3) End If End Sub
Hmm, a third parameter seems easier and tidier to me, but to each their own.?
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
When you get tired of copying and pasting Forms!frmprojectdetailsdataentry.CategoryID over and over (or Heaven forbid, you are actually typing that) consider one of the following approaches
orCode:With Forms!frmprojectdetailsdataentry If .CategoryID <> 1 Or .CategoryID <> 2 Or IsNull(.CategoryID) Then
Code:Dim ctl As Control Set ctl = Forms!frmprojectdetailsdataentry.CategoryID If IsNull(ctl) Or ctl <>1 Or ctl<>2 Then
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
I come to this board as a last resort and I almost made through without receiving condescending and belittling comments from another developer.
We get Micron, you’re way more experienced and smarter than the rest.
Being a developer to me is a work in progress and I learn all the time. I will review all the comments and hopefully learn from them but sometimes when you have a deadline, you have to move on and revisit later.
Learning on these boards would be so much nicer without the belittling comments. If you don’t have anything positive to lend, keep it to yourself.
If that is addressed to me, it was not meant to be, just my opinion, and the way I would have approached it.?I come to this board as a last resort and I almost made through without receiving condescending and belittling comments from another developer.
Learning on these boards would be so much nicer without the belittling comments. If you don’t have anything positive to lend, keep it to yourself.
As i said 'each to their own', there is generally more than one way to achieve the goal?.
I'm sorry if you took it that way.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
Welshgasman, My response was directed to Micron. Appreciate your suggestions.
So that's the thanks I get for trying to make your life easier? No problem - except that you have a paper thin skin and take what was meant to be helpful as condescending. Sorry if my style doesn't meet your standards. I'll just add you to my ignore list so that I never bother you again.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.