Select Case Problem 2

[rj51cxa]

I have a report which lists winners of a competition (txtUnit) in the order of their scores (position), with Position 4 being the highest score and Position 1 the lowest. A label (lblPlacing) shows either "Winner" or "Second Place" depending on the position. The problem is compounded by the requirement to show teams that competed for Honours Only (lblHO), who are not entitled to win the trophy.

The following code is in the On Print event of the Detail Section:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

Select Case Position
Case 4
    Select Case Honours_Only
    Case True
        lblPlacing.Caption = "Actual Winner"
        LblHO.Visible = True
    Case Else
        lblPlacing.Caption = "Winner"
        LblHO.Visible = False
    'Select Case TxtUnit
    'Case Nz(TxtUnit)
        'lblPlacing.Visible = False
        'LblHO.Visible = False
        'Case Else
        'lblPlacing.Visible = True
        'LblHO.Visible = True
    'End Select
    End Select
Case 3
    Select Case Honours_Only
    Case True
        lblPlacing.Caption = "Actual Second Place"
        LblHO.Visible = True
    Case Else
        lblPlacing.Caption = "Second Place"
        LblHO.Visible = False
    'Select Case TxtUnit
    'Case Nz(TxtUnit)
        'lblPlacing.Visible = False
        'LblHO.Visible = False
    'Case Else
        'lblPlacing.Visible = True
        'LblHO.Visible = True
        'lblPlacing.Caption = "Winner"
    'End Select
    End Select
Case 2
Select Case Honours_Only
    Case True
        lblPlacing.Caption = ""
        LblHO.Visible = True
    Case Else
        lblPlacing.Caption = "Second Place"
        LblHO.Visible = False
    'Select Case TxtUnit
    'Case Nz(TxtUnit)
        'lblPlacing.Visible = False
        'LblHO.Visible = False
    'Case Else
        'lblPlacing.Visible = True
        'LblHO.Visible = True
        'lblPlacing.Caption = "Second Place"
    'End Select
    End Select
End Select
End Sub

My problem is that, under certain conditions, only three scores are shown and, therefore, Position 4 becomes blank.
I therefore need Case 4 to be ignored and the parameters for Case 4 to be used in Case 3.

What I am trying to achieve with the Select Case TxtUnit is to say that, if TxtUnit is null, then nothing is printed, otherwise the Position and Honours Only labels are visible.

The first part of the code works well but, as you can see, I have had to rem out all reference to the Select Case because this effectively makes lblPlacing invisible for all cases. I think the problem is with "Case Nz(txtUnit)" for which I am not sure if my syntax is correct.

Any ideas would be most welcome
Best Regards
John

 

[dhookom]

I wouldn't put your business rules code into a report. This should be a function or functions in a standard module of business functions "modBusinessCalcs". It looks like your function(s) would require these arguments:
[tt][blue]
intPosition
booHonoursOnly
intNumOfScores
[/blue][/tt]
You would bind a text box in the report to your function.

I would also change LblHO to a "Can Shrink" text box with a control source like:

   =IIf(Honours_Only,"Your Caption",Null)

Duane
Hook'D on Access
MS Access MVP

 

[rj51cxa]

Hi Dhookom,

The code was placed in the Report on the advice of Genomon (Thread 703-1534163). He gave me the initial lead and I added the extra Select Case coding to take into account the Honours Only factor.

Are you suggesting that this is not the way to go? If so, I'm afraid your idea of creating a module is beyond my meagre programming skills!

Best Regards
John

 

[dhookom]

I appreciate what Genomon suggested. However, this code seems to implement a business rule and it might need to re-used on other reports or forms. If so, the correct place for the code is in a general module.

Creating a function is not difficult. You must determine what arguments are needed to calculate the desired output. For instance if you have fields for BoxHeight, BoxWidth, and BoxLength and want to display the box volume in your report, you should not add code to the On Print event of your report. Create a function in a module named "modCalcs" like:

Public Function CalcVolume(dblH As Double, dblW as Double, dblL as Double) As Double
    CalcVolume = dblH * dblW * dblL
End Function

Then in your report use a text box with a control source of:

  =CalcVolume(BoxHeight, BoxWidth, BoxLength)

The CalcVolume() function can then be used in any number of queries, forms, and reports. When your pointy-haired-boss changes the calculation for volume, you can change it in one place and it will be correct in every query, form, and report.

If I were creating your application, I would create a function like GetPlace() or whatever. What factors/arguments determin "Place"? These would be the inputs into the function. Then write code that uses the arguments/inputs to calculate the "Place" which gets returned from the function.

Public Function GetPlace(intPosition as Integer, booHonoursOnly as Boolean, intNumOfScores as Integer) As String
   'This function returns the place based on Position, Honours, and Number of Scores
    'Your code goes here

End Function

Then you can use this function as the control source of a text box in your report:

  =GetPlace([Position], [Honours_Only], NumScores)

IMO, this is the appropriate solution based on my experience.

Duane
Hook'D on Access
MS Access MVP

 

[rj51cxa]

Thanks Dhookom,

I understand what you are saying and you are correct - I have four reports which require the same code, so this will help a lot. I'll have a go and shout if I get stuck.

Best Regards
John

 

[dhookom]

John,
I believe you are beginning to understand ;-)

Duane
Hook'D on Access
MS Access MVP

 

[rj51cxa]

Duane,

Don't speak too soon! I understand what I need to do but am having problems achieving it.

I created a public function as you suggested but when I add a text box and put the details in the control source, I get a message saying that the Microsoft Jet Engine does not recognise "GetPlace". Just to be on the safe side, I am including the code that I have created:

Public Function GetPlace() As String

Option Compare Database

Dim Position As Integer
Dim HO As Boolean
Dim Placing As String

Select Case Position
Case 4
    Select Case Honours_Only
    Case True
        lblPlacing.Caption = "Actual Winner"
        LblHO.Visible = True
    Case Else
        lblPlacing.Caption = "Winner"
        LblHO.Visible = False
       End Select
Case 3
    Select Case Honours_Only
    Case True
        lblPlacing.Caption = "Actual Second Place"
        LblHO.Visible = True
    Case Else
        lblPlacing.Caption = "Second Place"
        LblHO.Visible = False
    End Select

Case 2
Select Case Honours_Only
    Case True
        lblPlacing.Caption = ""
        LblHO.Visible = True
    Case Else
        lblPlacing.Caption = "Second Place"
        LblHO.Visible = False
    End Select
End Select
End Function

Could you please take a look and see if I have made a simple error in the code and then explain why my text box won't work. Once I can get this bit right, I think I will be OK to modify the code to suit my needs.
Best Regards
John

 

[dhookom]

The function should be in a standard module and the module name can't be the function name. Get rid of the "Option Compare " inside the function.

The function must have arguments as noted:

Public Function GetPlace(intPosition as Integer, booHonoursOnly as Boolean, intNumOfScores as Integer) As String

Including the memory variables as arguments suggests they don't need to be "Dim'd" in the function.

The function should return a value to display in the text box. I would expect your code to include one or more lines like:

   GetPlace = "..."

This might get you further:

Public Function GetPlace(intPosition as Integer, _
      booHO as Boolean) As String

Select Case intPosition
Case 4
    Select Case booHO
    Case True
        GetPlace = "Actual Winner"
    Case Else
        GetPlace = "Winner"
    End Select
Case 3
    Select Case booHO
    Case True
        GetPlace = "Actual Second Place"
    Case Else
        GetPlace = "Second Place"
    End Select
Case 2
    Select Case booHO
    Case True
        GetPlace = ""
    Case Else
        GetPlace = "Second Place"
    End Select
End Select
End Function

You can then use a text box on your form or report with a control source like:

=GetPlace([Position],[Honours_Only])

Duane
Hook'D on Access
MS Access MVP

 

[rj51cxa]

Sorry, Duane

I'm still getting the same message when I try to preview the report. The text box is in the detail section of the report. Is there anything else I should do?

John

 

[Ray1127]

I have a question why would you use the above code over this code??

Public Function GetPlace(intPosition as Integer, _
booHO as Boolean) As String
Select Case intPosition
Case 4
If booHO Then GetPlace = "Actual Winner" Else GetPlace = "winner"
Case 3
If booHO Then GetPlace = "Actual Second Place" Else GetPlace = "Second Place"
Case 2
If booHO Then GetPlace = "Actual Third Place" Else GetPlace = "Third Place"
End Select
End Function

 

[rj51cxa]

Ray,

Thanks for the thought, the code is certainly much more simple. My problem was that I was getting an error message

The Microsoft Jet Engine does not recognise "GetPlace".

I could not try out the code because the text box didn't work
Best Regards
John

 

[dhookom]

John,
Did you create the function in a standard module? You should be able to open the debug window by pressing [Ctrl]+G. This will open a window in VBA that allows you to type in something like:

 ? GetPlace(4,True)

You can test various arguments for debugging purposes.


Duane
Hook'D on Access
MS Access MVP

 

[rj51cxa]

Duane

I'm out tonight, so I'll gie it a go in the morning. Just didn't want you to think I had dropped the problem.

Best Regards
John

 

[rj51cxa]

Hi Duane

What I had in my Database window, Modules object was a Module with the name "GetPlacing" which contained your code. I opened the module and then the immediate window, typed in the text as you suggested and received the following error message:

Compile error
Expected variable or procedure, not module

I then tried Ray1127's code and created a new module in the Database window with the name "Placing". I opened the immediate window, typed in the text as before and received the same error message.
Am I doing something fundamentally wrong? I wouldn't be surprised!
John

 

[dhookom]

Please open your module and attempt to compile the code from the debug window. Then read faq705-7148. Then copy all of the code back to this thread with any error messages.

Change the name of your module from "GetPlacing" to "modBusinessCalcs"

Duane
Hook'D on Access
MS Access MVP

 

[rj51cxa]

Duane

By now you must be thinking that I'm a real dumbo and you would be right! I've tried to step into the code using F8 but each time I get a fault beep and nothing happens. I'm afraid I need your guidance again.
I'd like to get to the bottom of this, but I'm concerned about pushing things too far. If you feel you would sooner leave it, I will quite understand.
Sorry about this
John

 

[Ray1127]

John, when you created the new module did you delete the function from the old module or did you rename it? It could be erroring because the same function exists in 2 different modules.

 

[rj51cxa]

Ray,

I renamed the original module which had Duane's code in it and created a new one with your code. I only have three modules , two of which are dealing with this code and both are different.

John

 

[Ray1127]

Yes but if you didn't change the code in the original module you will have 2 public Functions with the same name and Access will throw an error as it doesn't know which one to call.

 

[rj51cxa]

Ray,

In order to avoid confusion, I will attach the two modules so that you can see if there is any problem with them:

"ModBusinessCode"

Public Function GetPlace(IntPosition As Integer, _
      BooHO As Boolean) As String

Select Case IntPosition
Case 4
    Select Case BooHO
    Case True
        GetPlace = "Actual Winner"
    Case Else
        GetPlace = "Winner"
    End Select
        
Case 3
    Select Case BooHO
    Case True
        GetPlace = "Actual Second Place"
    Case Else
        GetPlace = "Second Place"
    End Select
Case 2
    Select Case BooHO
    Case True
        GetPlace = ""
    Case Else
        GetPlace = "Second Place"
    End Select
End Select
End Function

"ModPlacing"

Public Function GetPlace(IntPosition As Integer, _
      BooHO As Boolean) As String
Select Case IntPosition
Case 4
    If BooHO Then GetPlace = "Actual Winner" Else GetPlace = "winner"
Case 3
    If BooHO Then GetPlace = "Actual Second Place" Else GetPlace = "Second Place"
Case 2
    If BooHO Then GetPlace = "Actual Third Place" Else GetPlace = "Third Place"
End Select
End Function

I've tried to do as Duane suggested and run them using F8 but neither will respond. I'm afraid I am now somewhat confused. Any help would be much appreciated.
John