Sleepless Form Combo Select

[MadeCurler]

I have a form with a combo box that has six values being pulled from a table "Catches 1 - 22 Salmon", "Catches 1 22 Sea Trout" plus another 4 values.

I have six corresponding reports called "Cathces122Salmon", "Catches122SeaTrout" plus another 4 reports.

What I want (obviously) is to open the report "Catches122Salmon" when I select "Catches 1 - 22 Salmon" from the combo

When I use the code below on a command button, a report opens but it is not the one referred to in the If..Else statement. For example, If I select "Catches 1-22 Grisle" from the drop down it opens "Catches122SeaTrout"

Private Sub Command5_Click()

Dim strForm As String

If Me.Combo6 = 1 Then
strForm = "Catches122Grisle"

ElseIf Me.Combo6 = 2 Then
strForm = "Catches122Salmon"

ElseIf Me.Combo6 = 3 Then
strForm = "Catches122SeaTrout"

ElseIf Me.Combo6 = 4 Then
strForm = "Catches2332Grisle"

ElseIf Me.Combo6 = 5 Then
strForm = "Catches2332Salmon"

Else
strForm = "Catches2332SeaTrout"
End If


'Debug.Print strForm

DoCmd.OpenReport strForm, acViewPreview

End Sub

 

[nanscombe]

Try using a Select Case statement instead.

Private Sub Command5_Click()

Dim strForm As String

  SELECT Case nz(me.Combo6,0) ' This is in case nothing is selected
  Case 1
    strForm = "Catches122Grisle"

  Case 2
    strForm = "Catches122Salmon"

  Case 3
    strForm = "Catches122SeaTrout"

  Case 4 
    strForm = "Catches2332Grisle"

  Case 5
    strForm = "Catches2332Salmon"

  Case Else
    strForm = "Catches2332SeaTrout"

  End Select


'Debug.Print strForm

DoCmd.OpenReport strForm, acViewPreview

This does assume that your ComboBox has two columns, the first (hidden) holding the value you are testing for and the second the text.

 

[MadeCurler]

Thank you for that but I still have problems. When I add Debug.Print Combo6 at the beginning of the code and Debug.Print strForm at the end of the code I get weird results. For example if I select the first combo item I get Combo6 = 3 and strForm = CathchesSeaTrout in the immediate window. I would expect combo6 to =1 and strForm =Catches122Grisle


Another example if I select the second item I get combo6 = 1 and strForm=Catches122Grisle in the immediate window. I would expect combo6= 2 and strForm = Catches122Salmon.

I'm new to all this and it's now driving me mad!! I'd appreciate any help you can give.

 

[nanscombe]

You could try adding this line just before the Select Case statement, stepping through each option and seeing if you it produces what you would expect.

Debug.print me.Combo6.Column(0), me.Combo6.Column(1)
SELECT Case nz(me.Combo6,0)

It will tell you what is in columns 0 & 1 of the ComboBox and should give you a list which looks like:

1 Catches 1 - 22 Salmon
2 Catches 1 22 Sea Trout
3 Your
4 Other
5 Values
6 Here

If it looks correct try replacing the Select Case statement with..

SELECT Case nz(me.Combo6.Column(0),0)

If it looks like:

Catches 1 - 22 Salmon Null
Catches 1 22 Sea Trout Null
Your Null
Other Null
Values Null
Here Null

Your case statements need to check for the text instead.

Case "Catches 1 - 22 Salmon"
    strForm = "Catches122Salmon"

Of course, if you have numbers and titles, the numbering might just be up the creek and you may just have to adjust the case statements.

 

[MadeCurler]

Thank you very much....I'll try these options and let you know

 

[RainLover]

@ MadeCurler

The method you are using is sometimes called "Hard Coding" You currently have six choices, but what happens when that becomes 7 or 8 or etc.

It means that you as programmer must go behind the scenes and recode. With a good program it should be able to grow with the circumstances.

Instead of using If or Case statements simply bring the value of the Combo into your code. So the Filter becomes the Value of the combo.

 

[nanscombe]

It's calling up different reports based on the item chosen.

If anything the report name would be better being put in to a table along with the text.

Table tblReports:
reportName, categoryName
Catches122Salmon, Catches 1 - 22 Salmon
Catches122SeaTrout, Catches 1 22 Sea Trout
...

Then the ComboBox could be based on those two columns. First column reportName (hidden - width 0). Second column categoryName (visible)

The code could simply be replaced with.

Private Sub Command5_Click()
  DoCmd.OpenReport me.Combo6.Column(0), acViewPreview
End Sub

Then, if more entries are required you would just add more entries to tblReports.

 

[RainLover]

Nigel

That is much better.

 

[nanscombe]

But not the help that was originally requested.

 

[RainLover]

But not the help that was originally requested.

True,
but if you help people to do the wrong thing you are not helping at all. In fact you are adding to their problem. Sometimes you have to look harder and find what the real problem is.

 

[boblarson]

True,
but if you help people to do the wrong thing you are not helping at all. In fact you are adding to their problem. Sometimes you have to look harder and find what the real problem is.

EXACTLY! We should not blindly give the answer, but try to help those who are going down a path which doesn't follow best practices. Sometimes though you have to let users discover that should they be too resistant (could be that they have no ability to change what is), then we do provide the answer to the immediate situation.

 

[nanscombe]

I see moving from a nested "If / Else If" to "Select Case" as a positive step.

For all we know this may be a small tactical solution which may not grow much beyond what it currently is. Introducing a new table, just to run half a dozen reports, may not be a justified reason to add an extra layer of complication.

The original request was for a little help with a piece of code rather than a full cycle of systems analysis.

 

[MadeCurler]

Guys.....you are all correct in what you say. I absolutely agree that this Forum should not be a place where inexperienced Access users (like me) come and pick up "unearned" code snippets. There does come a point however, where you end up near in tears of frustration after you've spent hours searching for clues on what the solution might be, think you may have found it, tried it, and it doesn't work. I've spent ages trying to learn VBA - from "For Dummies" books to the web (there's a lot of good stiff out there), but it isn't easy (at 66)

As far as this specific problem is concerned, I understand "Select Case" and "If...Else" and in fact I have drew the data for the combo from a table but my fields were ID and CategoryName (not as you suggest reportName, categoryName).

I'd like to thank you all again for taking the time to respond to this. I'll not give up and keep on trying - difficult though it is for me.

 

[nanscombe]

Guys.....you are all correct in what you say. I absolutely agree that this Forum should not be a place where inexperienced Access users (like me) come and pick up "unearned" code snippets.

Like most other Forums on the internet this is a public place. You have just as much right to ask for help here as someone with many years experience.


[professional mode - on]

Are you using a table specifically for the Categories?

Or are you deriving a list of distinct Category names from a table of fishing data?



Any chance of uploading a copy of your database (minus data if necessary) so we can see what you are trying to do rather than indulging in a game of '20 questions'?

 

[MadeCurler]

I set the data source of the combo as a table called "TblPoolSelect" in which I set the Primary Key to PoolSelectId (Autonumber) and the only other field I listed in that table was "PoolSelectRange" in which I entered 6 values...ranging from "Catches 1-22 Salmon" to "Catches 23 - 32 Grisle".....I then created 6 queries using other properly "related" tables in the Db where one of the criteria was "PoolSelectRange" and subsequently reports based on each of the 6 queries. You suggested in your earlier reply that three fields should have been created (including PK)......namely PoolsSelectID, reportName, categoryName...and I now understand why (I think).

My main problem with this was that I was too ambitious. I tried to present the data in a report driven , button "on click" interface without the need to use the Access menu system (like File>Print Query).....I found a tutorial on the web at.....

http://www.datapigtechnologies.com/flashfiles/searchform.html

....that suits me fine and works (although it's not as fancy as I'd like as far as producing customised reports is concerned)...

I really appreciate your help and understanding on this, but please don't put yourself to any more bother.

Kind Regards

MadCurler

 

[nanscombe]

Ok. One last piece of advice.

As I said earlier, all you need to do is find out what me.Combo6 is returning, be it the value of PoolSelectId or PoolSelectRange and amend your Select Case statement to match.

What I would do in your situation is start out simply with just the Case Else statement.

Select Case nz(me.Combo6,0)
Case Else
  Debug.print nz(me.Combo6,0)
End Select

I would then pick an option, and click the report button, and that will tell you what you need to use.

If you click it and it says 1 then create a statement for Case 1.

Select Case nz(me.Combo6,0)
Case 1
  strForm = "Catches122Salmon"
Case Else
  Debug.print nz(me.Combo6,0)
End Select

If it says Catches 1 - 22 Salmon then create a statement for Case "Catches 1 - 22 Salmon".

Select Case nz(me.Combo6,0)
Case "Catches 1 - 22 Salmon"
  strForm = "Catches122Salmon"
Case Else
  Debug.print nz(me.Combo6,0)
End Select

Approaching it in this way allows you to gradually fill in the blanks until they are all done.

 

[MadeCurler]

Thank you...I'll try that. In my learning, I've used the Immediate Window but haven't really studied how to use it to it's full potential for debugging purposes. This thread problem has prompted me to now go and find out more about "Debugging" and the use of Immediate Window and based on your advice I'm sure I'll crack it!! Thanks very much for patience in this. Regards

 

[RainLover]

The attached is a bit involved, however it does demonstrate complex searching.

Perhaps you can pick up a few thing from it.

 

[RainLover]

The attached is a bit involved, however it does demonstrate complex searching.

Perhaps you can pick up a few thing from it.